Split by Variable Columns in Power Query

The default Split by feature in Power Query produces a fixed number of columns. In this videos I'll share an interesting M technique to split by Variable Columns in Power Query. Enjoy!

===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -

✔️ Power Query Course-

✔️ Master Excel Step by Step-

✔️ Business Intelligence Dashboards-

===== LINKS 🔗 =====

===== CONTACT 🌐 =====

===== CHAPTERS =====
0:00 Intro
0:37 Split by Columns - The Problem
2:23 The Logic
4:22 The Solution
12:09 Testing the Solution
13:02 My Courses

===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers!

- - - - -
Music By: "After The Fall"
Track Name: "Tears Of Gaia"
Published by: Chill Out Records
Official After The Fall YouTube Channel Below
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)

[teaking]

Really enjoyed the video, and found the use of the functions a great learning experience.

For this example I think the list of names can be replaced with the number of columns, so you could have used the max number straight in the argument, although the video was more informative using the other way

= let
_max = List.Max(Table.AddColumn(Source, "Custom", each List.Count(Text.PositionOfAny([Hobbies], {", "}, Occurrence.All)))[Custom])+1
in
Table.SplitColumn(Source, "Hobbies", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), _max)

[wayneedmondson]

Awesome Chandeep! Very clever.. as always. Thanks for walking through the steps.. well explained and easy to follow. Thumbs up!!

[jaymehta]

Really amazing. Dynamic columns, even outside split, remains a very challenging area - like pivoting and unpivoting a dynamic list of columns. This video definitely helps.

[CJamesEnglish]

Great presentation - excellent walkthrough of the critical thinking and problem-solving technique. I wonder if there's a way to do it in DAX, as well...
Also... I, too, enjoy Hulking. Excellent choice, Rehet.

[ajinkyashinde]

Amazing....The only guy on YouTube who can teach power query transformation like bread & butter... Kudus Chandeep for this awesome playlist. ❤❤

[pabeader]

Great idea. I love the way you conserve steps and combine so much into each one. I do see an issue that will come up with steps beyond these dynamic columns. But I suppose that can be handled as the cases arise.

[jimmyni]

The BEST TEATCHER EVER about Power Query (M) language. Loving all videos about it. Thank you a lot for your great and amazing commitment to help world community. I wish all the best to you and your family!

[qiancasper]

Thanks for demonstrating the procedure of solving the problem really amazing

[Chris_in_fremantle]

As always, very clever. Mind blown regarding not having to to refer to the immediately proceeding applied step. Well done.

[AndrewWright]

Thank you so much. You explain the concept so well.
I'm going to apply this to a problem I have. Thanks again!

[Softwaretrain]

Thanks, Chandeep, I liked the part which you use Text.PositionOf for AllOccurance but for split by variable columns why do you make it really complicated?!!!
Just remove the list in the last argument of Table.SplitColumn function. (then it will be dynamic for new columns)😉

[VS.RLeVant]

You are a wizard with this. Thanks for sharing!!!

[eduardokeane]

Excelente, me ayudó el tutorial para aplicarlo en un reporte, saludos desde Perú.

[theresaverburg]

Is it possible to have unique values only in the columns?
In your example, the "sleeping" value would have its own column.

[pawewrona]

I did this solution some time ago more for a "sport" activity. However, the easier way is to use to Text.Split which will convert the text into list, and then expand the list.

[AlesKus]

You are... just unbelivable 🙂 Thank you!

[MrAbrandao]

I found a bug in the split function while working with a large dataset. I was manually applying a sequence of commands, with the split function in the middle. I was splitting a column using the delimiter ";;;". The columns had a varying number of delimiter occurrences, but I expected them to consistently generate 4 or 5 columns each.

Only later did I realize that the split function was only generating 2 columns. I had to re-run the split operation, and only then did it generate the correct number of columns. Now, whenever I apply the split command, I wait for it to show the detected number of columns before applying it again.

[iankr]

Great video, thanks! Your son sounds cool 😀

[IlaPatel]

Just the tip I was looking for, Thank you! :)

[patrickbrom]

This is great, can also be used with loading PDF, and so on, thank you!