Split a cell into multiple columns

[danextian]

Hi All,

I need your help again.

1/11 communication
1/10 communication
1/9 communication
1/6 communication
1/5 communication
1/4 I emailed the following:
items a
items b

Above is the content of a single cell. I want to split them into multiple rows per line break  - that's char(10) in excel -  and get the earliest date. I know i cant split them into multiple columns but I just can't find the function in Power BI Desktop to split them into rows. 

The splitting step is where I am stuck with. After I done with that step, I can just extract the text string until right before the first blank space, convert the strings to date, replace text (items a & items b lines) with null and remove them, sort the remaining rows in ascending order and remove the duplicates.

[Phil_Seamark]

Hi @danextian

You could try the great now "split into rows" feature that was introduced in a recent version of Power BI Desktop

So use the Query Editor and do the following.  Highlight your column and split on special character #(lf) into rows

.

[danextian]

Hi @Phil_Seamark,

Thanks for the reply.

I tried your advise to break a cell by line feed into rows. Unfortunately, it didn't work. Doing so does nothing.

I tried to split the cells into column and they did.  The feature is there but i don't think it is working.

[Anonymous · Accepted solution]

@danextian@Phil_Seamark

Indeed, the feature doesn't work in this case.

The code of the recorded code line is:

= Table.ExpandListColumn(Table.TransformColumns(Source, 
{{"Spalte ""1""", Splitter.SplitTextByDelimiter("#(#)(lf)", QuoteStyle.Csv), 
let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), 
"Spalte ""1""")

By simply adjusint the part

Splitter.SplitTextByDelimiter("#(#)(lf)",

to

Splitter.SplitTextByDelimiter("#(lf)",

it works as desired.

[danextian]

@Anonymous

by the way, upon checking Power BI does this Splitter.SplitTextByDelimiter("/#(lf)",

[Anonymous]

@danextian

Didn't know that one - thanks

[danextian]

Hi @Anonymous, thanks for this. It works. 

By the way, is there a way in Power BI to split into a column into multiple columns using multiple delimeters - not just a space or comma but either a space or comma?