Sharepoint multi row text

I'm having difficulty presenting text fields from Sharepoint. In Sharepoint there are multiple rows delineated by a return and a bullet symbol, however, when I pull into Alteryx the separators are gone and the data from rows just runs from one to the next making it much harder for report users to read.

Capture 1 (attachment) is how the data looks in Sharepoint

Capture 2 is how the data from Sharepoint looks when imported to PowerBI

Capture 3 is how it looks when imported into Alteryx (the red brackets that I drew in are where the carriage return should be to make it more readable with each on a separate line within the same row)

Capture1.PNG

Capture2.PNG

Capture3.PNG

Sharepoint

Accepted answers

rafalolbert

Hi @plathrop8,

Please have a look at the updated workflow, changes include:

- in the 'Summarize' tool concatenation is using HTML's line break indicator: '<be />'

- this unfortunately is not honoured by the 'Table' tool

- 'Formula' tool is meant to fix it using: Replace([Table],"<br />","<br />")

- below is the output from the 'Render' tool - please notice it has 'Table (Bad Field Type)' message, however this is still working as expected

Thanks,

Rafal Olbert

#Excuse me, do you speak Alteryx?

workflow_new.yxmd

All comments

rafalolbert

Hi @plathrop8,

Lets try something like this:

1) Use RegEx tool to replace any HTML tags with empty string

2) Cleanup any remaining bits, in my example i noticed an additional numeric entity representation of a space, so i did: Replace([multiline],' ','')

3) 'Text To Columns' with 'Split To Rows' option

4) Filter for empty records (1st record)

5) Reconstruct the bullets as per your character using 'Formula' tool

6) Summarize with concat and line break '\n'

The result looks like this:

Thanks,

Rafal

#Excuse me, do you speak Alteryx?

workflow.yxmd

workflow.yxzp

data.csv

plathrop8

rafalolbert

In your example, the data is coming through the way I want to see it from the cell viewer like you show in one of your attachments, however, when you hook up a reporting table the data just gets smashed together again (not on separate lines within the cells). I attached your workflow adjusted to add the reporting table as v1.

Capture4.PNG

workflow_v1.yxmd

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335