Latest Enterprise DNA Initiatives

Splitting Excel multiline cells in multiple rows

Hi @sam.mckay ,
I’ve some Excel files where users put multiple lines in a single cell and they did on multiple columns.
If I split each column by delimiters into rows I got multiple rows for the same combination.
Trying to merge in one single column, split by rows, and then split into columns didn’t work either.

I’m afraid this cannot be obtained only with the UI generated code.

Thanks for your help and support.

Roberto

Multiline Cell Split.xlsx (23.0 KB)
split multirows cells.pbix (16.6 KB)

Hi @Roberto,

See if this works for you.
Copy and paste this M code into a new blank query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnUOVggOcfRzcQxyUQj2cVTQcPIJ01TSUTKMKYrJMwIRxiDCBChkZFJhDhRDIi0qTBU0LK0MDBR0FQzNgbSmUqxOtJJ3gGe8m6erj0t8sGtQmKeza3ywZwiQCA0I8A8KwWeFJbKBFiADY/LIFwM7Buq7kPgQT9cgfHbH5BFvbiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Tier = _t, Priority = _t, #"M S Schedule" = _t]),
    GetValues = Table.AddColumn(Source, "Temp", each List.Zip( { List.Transform(Text.Split([Priority], "#(lf)"), Text.Clean), List.Transform(Text.Split([M S Schedule], "#(lf)"), Text.Clean) }))[[Tier], [Temp]],
    ExpandLists = Table.ExpandListColumn(GetValues, "Temp"),
    ExtractValues = Table.TransformColumns(ExpandLists, {"Temp", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    SplitColumn = Table.SplitColumn(ExtractValues, "Temp", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Priority", "M S Schedule"})
in
    SplitColumn

.
This is the key part

image

Splitting the text on line feeds, that returns a list, these values needed some cleaning so added the List.Transform in combination with Text.Clean.

Because there’s an equal number of values in both columns, I’m bringing them together based on their position with List.Zip so I’m left with a single list of lists.

And finally using projection to only keep the [Tier] and [Temp] column.

Here’s your sample file.
split multirows cells.pbix (20.7 KB)

I hope this is helpful.

4 Likes

Hi @Melissa, that’s so beautiful! Thanks a lot for the solution and your kind explanation
This is the second time I see using List.Zip as a magic wand.

2 Likes

@Melissa ,

Agree with @Roberto - this is magic. I immediately recognized this as a list.zip solution from your previous list.zip video, but I couldn’t figure out how to handle the delimiter splitting here. If you’re looking for a good video topic (hint, hint…), I think an explanation of how to use the lf/cr special characters as you did here would be fantastic - never seen anyone else cover that material…

Great stuff!

  • Brian
2 Likes

I was keeping an eye out for the solution to this one……the magic is still strong with @Melissa :grinning:

1 Like

Thanks @Roberto, @BrianJ and @DavieJoe
Just added it to my “to do” list :wink:

3 Likes

I’m now in the Power Query M Masterclass Part One hoping the magic starts getting strong with me as well. Thank @Melissa

2 Likes

Hi @Roberto,
Well you’re off to a good start :wink: LOL
Enjoy the course!

3 Likes

Hi @Roberto,

The video for your scenario was just published to the eDNA YT Channel, you can find it here

2 Likes

Thanks @Melissa! I love how you mix UI and coding. The walkthrough looks so easy. I’ll do it from scratch on my own. I’ve discovered some tokens use cr and lf as separators, others just lf. I had opened the CSV with Excel and I missed that. In your video you show how to make it step by step. Really great stuff.

2 Likes

Hi @Melissa,
I’m now working with the real customer data and I found that when in a file (I’ve hundreds) the columns have no values the parsing fails since the list.zip seems to skip null values. How can I consider all columns even when they have no values?

Thanks for your help

Roberto


image

Hi @Roberto,

Please supply a mock-up sample and PBIX.
Thanks

Hi @Melissa,
please find attached sample and pbix.
Thanks

Roberto
source.csv (366 Bytes)
split.pbix (22.5 KB)

Hi @Roberto,

Thanks for providing a sample :+1:
I expect this small highlighted modification to the code will resolve that issue.

I hope this is helpful

1 Like

Hi @Melissa,
it made the trick. All production data files are now nicely parsed, Thank you!

I still have got a question: after some years of practice, I’ve learned how to debug DAX, both writing ad hoc measures and using tools like DAX Studio.
Is there any advice you can provide for debugging M? In the How The Power Query Editor Is Organized Part 1 debugging is not mentioned. Will we see it in Part 2?

Thanks again

Roberto

2 Likes

Hi @Roberto,

Glad to hear this works well for you!

As for debugging M code, I think the process in the accompanying video illustrates how you can check and review intermediate results when building a solution. I’ve tried to show many of the errors you’re likely to encounter throughout the entire course but specifically the chapter on “How to successfully implement a piece of M code

I hope this is helpful

2 Likes

Hi @Melissa,
just completed Power Query M Masterclass Part One. Very informative indeed.
It’s a must for all those who want to go seriously on Power BI.
Like many, I’ve been spoiled by the UI and I’ve missed using the power of M.
Eager to see part 2 and hopefully beyond!

Thanks again

Roberto

3 Likes

@Roberto ,

Thanks so much for the feedback. I couldn’t agree more - I keep returning to that course and rewatching chapters as my M skills improve., and gain substantial new insights from it on each viewing.

  • Brian

P.S. More exceptional @Melissa PQ/M content coming in the not too distant future…stay tuned.

2 Likes