Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Proud to be a Super User!
Solved! Go to Solution.
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.
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
.
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.
Proud to be a Super User!
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.
@Anonymous
by the way, upon checking Power BI does this Splitter.SplitTextByDelimiter("/#(lf)",
Proud to be a Super User!
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?
Proud to be a Super User!
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |