Split by Variable Columns in Power Query

Рет қаралды 28,332

Күн бұрын

Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
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 -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co.in/blog/
Corporate Training 👨‍🏫 - www.goodly.co.in/training/
Need my help on a Project 💻- www.goodly.co.in/consulting/
Split by Delimiter Blog - www.goodly.co.in/split-by-var...
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - goodly.wordpress@gmail.com
===== 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
- Source: goo.gl/fh3rEJ
Official After The Fall KZfaq Channel Below
kzfaq.info/love/GQE...
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)
Full license here: creativecommons.org/licenses

Пікірлер: 147

@GoodlyChandeep 8 ай бұрын

Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/

@GeorgeKlucsarits Жыл бұрын

Came across your video trying to solve this exact problem today. I needed to separate a list in a cell to separate columns prior to unpivoting. Your explanation was terrific and probably the most straightforward solution I've come across. Thanks!

@wayneedmondson1065 2 жыл бұрын

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

@GoodlyChandeep 2 жыл бұрын

Glad you like it 😊

@jaymehta3320 2 жыл бұрын

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.

@pabeader1941 2 жыл бұрын

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.

@leebecker8255 2 жыл бұрын

Brilliant!! This is exactly what I was looking for about a year ago, but I couldn't quite work out how to do it. I can't wait to implement this. I really like the way you explained all the step. THANK YOU!!! I have just subscribed :)

@GoodlyChandeep 2 жыл бұрын

Fantastic!

@teaking1981 2 жыл бұрын

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)

@Clau.08 3 ай бұрын

mind blown. great tutorial and great explanation!!!!!

@adityadeshmukh3150 2 жыл бұрын

This technique blows my mind. I think M query can makes our work faster.. Thanks for sharing this knowledge. 👍

@shirleymoreman6725 7 ай бұрын

Fabulous! Thanks for the clear explanation.

@qiancasper 2 жыл бұрын

Thanks for demonstrating the procedure of solving the problem really amazing

@nonoobott8602 2 жыл бұрын

Great solution as always. I've learned so much on your channel. Thanks for sharing

@RonDavidowicz 2 жыл бұрын

Great video, I'm sure these techniques can be used for many other problems.

@GoodlyChandeep 2 жыл бұрын

Glad it was helful 😊

@JoseMariaGomezMartinez 2 жыл бұрын

Glorious! Mastering as always Chandeep, many thanks for sharing!

@GoodlyChandeep 2 жыл бұрын

Glad you like it Jose! 😊

@yumyumhotsauce1 2 жыл бұрын

wow, this just came just at the right time. I have a use case where I have to do a report on dynamic product category/products. Just subscribed. thanks!

@Chris_in_fremantle 2 жыл бұрын

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

@GoodlyChandeep 2 жыл бұрын

Glad you like it 😊

@LuciusSullaFelix Жыл бұрын

Excellent video...great explanation....subscribed.

@cristian.angyal 2 жыл бұрын

Great video! Thanks for sharing!

@raimundojs9547 Жыл бұрын

Fantastic! Beautiful! Thanks for sharing.

@GoodlyChandeep Жыл бұрын

glad you like this

@CJamesEnglish 10 ай бұрын

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.

@suki9860 2 жыл бұрын

Always cutting-edge, Chandeep. Thanks!

@GoodlyChandeep 2 жыл бұрын

Glad you like it 😊

@eduardokeane 7 ай бұрын

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

@AndrewWright26 Жыл бұрын

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

@GoodlyChandeep Жыл бұрын

Glad you like it

@shoaibrehman9988 2 жыл бұрын

Excellent way to deal with this types of queries. Thanks

@GoodlyChandeep 2 жыл бұрын

Glad you like it 😊

@pmsocho 5 ай бұрын

Awesome! 👍

@martyc5674 2 жыл бұрын

Excellent Chandeep 👍👍

@franciscom.paredesarias2356 2 жыл бұрын

Extraordinary result, thank you very much for sharing this type of solutions, personally it has helped me a lot. Greetings from Chile

@GoodlyChandeep 2 жыл бұрын

Glad it was helpful 💚

@davidmvg216146 2 жыл бұрын

This great content, cheer for your good work! 👍🎉

@GoodlyChandeep 2 жыл бұрын

Glad you like! 💚

@VS.R3LeVant 2 жыл бұрын

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

@GoodlyChandeep 2 жыл бұрын

Glad you like it 😊

@joebateman9668 Жыл бұрын

Very, very cool and handy tip!!

@GoodlyChandeep Жыл бұрын

Glad you liked it

@jimmy3862 2 жыл бұрын

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!

@GoodlyChandeep 2 жыл бұрын

Glad you are loving the videos 😊

@ajinkyashinde1778 Жыл бұрын

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

@GoodlyChandeep Жыл бұрын

Thanks a ton 😉

@Jujubeterraba 2 жыл бұрын

Thank you, this video has saved my day!

@GoodlyChandeep 2 жыл бұрын

Glad it was helpful 💚

@ErickNishimoto Жыл бұрын

Thx from Brazil! 💚💛

@GoodlyChandeep Жыл бұрын

Welcome!

@IlaPatel811 Жыл бұрын

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

@GoodlyChandeep Жыл бұрын

Glad I could help!

@patrickbrom2702 2 жыл бұрын

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

@jawadahmadehssan6251 2 жыл бұрын

Thanks Chandeep for sharing this brilliant content.

@GoodlyChandeep 2 жыл бұрын

Glad you liked it!

@stlaurent26 Жыл бұрын

Thank you!

@AlesKus Жыл бұрын

You are... just unbelivable 🙂 Thank you!

@GoodlyChandeep Жыл бұрын

Thanks Ales!

@egbertdejong6377 2 жыл бұрын

Great content!

@yewchoob85 Жыл бұрын

Very well put together video

@GoodlyChandeep Жыл бұрын

Glad you enjoyed!

@fajnefajne4339 2 жыл бұрын

Excellent. Thank you very much!

@GoodlyChandeep 2 жыл бұрын

Glad you like it 😊

@OzduSoleilDATA 11 ай бұрын

Very Goodly,my good sir! 🔥👊🏽🔥

@GoodlyChandeep 11 ай бұрын

Thanks Oz. Glad to see you here!

@kennethstephani692 2 жыл бұрын

Great Video!!

@GoodlyChandeep 2 жыл бұрын

Thank you 💚

@bithepower9081 2 жыл бұрын

As always amazing stuff Chandeep

@GoodlyChandeep 2 жыл бұрын

Thank you 😊

@Tuhin380 4 ай бұрын

Just amezing❤❤

@KuldeepSingh-nq1vi 2 жыл бұрын

Wow 😲 Chandeep Amazing!, what a technique you taught. Please solve my problem also which I commented in another's video. How can I pass dynamic list into table.combine function.

@Softwaretrain 2 жыл бұрын

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)😉

@GoodlyChandeep 2 жыл бұрын

Didn't know that.. thanks

@martyc5674 2 жыл бұрын

Wow-- That works really well.. makes me wonder if lots of hardcoded lists in M are Optional Arguments

@martyc5674 2 жыл бұрын

This only works if the first row happens to have the max delimiters required, I have tried, and lets say if the first record has 2 delimiters, then you get 3 columns and no more.

@Softwaretrain 2 жыл бұрын

@@martyc5674 Of course, I reached to this point and the solution is making Custom column as =List.Count(List.Split([Hobbies],",")) And before split, we must sort z to a this custom column. Still easier.

@nonoobott8602 3 ай бұрын

Amazing ❤

@user-lm5wb8vi1x 7 ай бұрын

Finished watching

@rrrraaaacccc80 5 ай бұрын

Great 💯👍

@qasimawan3569 10 ай бұрын

This is amazing, exactly what I needed. Can I ask, how do you come up with this logic/idea? Does it come naturally or..

@pawewrona9749 2 жыл бұрын

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.

@iankr Жыл бұрын

Great video, thanks! Your son sounds cool 😀

@GoodlyChandeep Жыл бұрын

Thanks Man!

@irenegurne8953 Жыл бұрын

Love it! but is there also a way to covert it each in a rows rather than columns?

@MarioSaldanhaLobo Жыл бұрын

Thanks for the explanation. I've shorten it a bit. This avoids me having to clean up added columns in the end. The 'n' column, that contains the number of occurrences for ';' exists only inside the MaxAnswers step. I could also add it and then refer to a previous step, true... MaxAnswers = List.Max(Table.AddColumn(Source, "n", each List.Count(Text.PositionOf([Answers], ";", Occurrence.All)) + 1)[n]), HeadersAnswers = List.Transform({1..MaxAnswers}, each "Answer"&Text.From(_)),

@thebhaskarjoshi 2 жыл бұрын

Great video

@GoodlyChandeep 2 жыл бұрын

💚

@JenMayB Жыл бұрын

Thank you so much! I am having a problem in that I need to split a column up to 15 characters for example. The column has characters up to 15 and then a space and then another set of characters but within those first 15 characters there can also be spaces, So I can’t split by the delimiter of a space. And I can’t split by 15 characters because sometimes it is less than 15 characters. I need to make the split dynamic but I am very lost on what to tell it to do.

@mahathmasadineni2884 2 жыл бұрын

It is awesome chandeep sir, Can you please help me how can we add multiple custom columns in single step at power query

@samuraize24 Жыл бұрын

Golden!

@GoodlyChandeep Жыл бұрын

Thanks!

@thamilanban 2 жыл бұрын

Thank you

@blitzerdisco Жыл бұрын

thanks for great tutorial! what if there would be more columns like Hobbies_1, Hobbies_2 and few items in each of them?

@antoniodelpozzo2652 2 жыл бұрын

Great!

@GoodlyChandeep 2 жыл бұрын

Thanks 😊

@mattmatt245 2 жыл бұрын

Hi Chandeep, Did you do a video on how to remove duplicates when column values can be swapped like (1,2) and (2,1) would be a duplicate. Thanks

@singhalmonica 2 жыл бұрын

Great lesson once again.... Sir pls help to understand how get total of if we have numbers in place of Hobbies. Pls help sir...

@sukhomoyb 2 жыл бұрын

Wow just wow ...

@GoodlyChandeep 2 жыл бұрын

Thanks 😊

@Tat-cx1lr Жыл бұрын

Bravo

@teigenxayden 4 ай бұрын

The red color code is hard code,to modify them is a good job.

@josealvesferreira1683 2 жыл бұрын

Very good

@GoodlyChandeep 2 жыл бұрын

Thanks!

@emilmubarakshin49 4 ай бұрын

Excellent video once again. Does Rehet means Happiness?

@GoodlyChandeep 4 ай бұрын

it means calm

@sahiladya8473 2 жыл бұрын

that's amazing

@GoodlyChandeep 2 жыл бұрын

Thanks 😊

@NarendraSunku 2 жыл бұрын

You are genius

@GoodlyChandeep 2 жыл бұрын

Thank you! 😊

@tomekleddo8537 Жыл бұрын

Cheers!

@GoodlyChandeep Жыл бұрын

Cheers! Thanks!

@theresaverburg 5 ай бұрын

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

@AnbarasuAnnamalai Жыл бұрын

It is nice

@GoodlyChandeep Жыл бұрын

Thanks!

@MrAbrandao 3 ай бұрын

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.

@leebecker8255 2 жыл бұрын

Now I want to trim (or do some other function) to all of the columns.. Can you think of a good way to do that?

@gennarocimmino Жыл бұрын

Very interesting tutorial. But you didn't foresee that in the future you might list a little friend of your friends and that he will have other hobbies, for example football and Crying, just two hobbies, but football would deserve a new separate column and the current formula does not provide for this case .... :) :) Greetings.

@rrrraaaacccc80 3 ай бұрын

💯👍

@augustriandyrizkymaris2894 Жыл бұрын

my report have an uneven space length.. is there a way to split them?

@mchllwoods 2 жыл бұрын

This tutorial is great but it's not meeting my needs exactly. My issue is I have a list of names and I want to split them into columns by First Name, Middle Name or Initial, Last Name, and Suffix name. Not all have a suffix, middle int/name or period. when I split them last names go into the middle name column and I don't know how to bring it to the right column. When I split the delimiter one step at a time by the first name it's in the right column but when I split the second time I run into problems. How do I fix this?

@GoodlyChandeep 2 жыл бұрын

Can you please send me some dummy data and describe the problem clearly - goodly.wordpress@gmail.com

@HarshilPatel497 Жыл бұрын

This solved my main issue to learn m code how to modify but couldn't we split the hobies by delimiter by row and then we get a unique list and refer that list this would solve issue if you have duplicate entries...

@GoodlyChandeep Жыл бұрын

Yes there could be many ways of solving such problems

@HarshilPatel497 Жыл бұрын

Yes, And you know it all... You explain things very well..and i think far better than anyone else when it comes to power query.....

@adilarsalan6020 2 жыл бұрын

Hello where to find your fundamental course.

@GoodlyChandeep 2 жыл бұрын

goodly.podia.com/power-bi-beginner-course

@BajrangSingh-js2qq 7 ай бұрын

Hi I have some need in data

@danielbarber7329 Жыл бұрын

Awesome trick however is there a way to sort the information. Let's say you asked 100 people and they chose a hobby but it appears any of them columns in the order it was split. Maybe you want a column based on the hobby type.

@sebfromgermany3819 Жыл бұрын

Other functions, e.g. Text.Combine uses a list to reference the columns to be combined. So far I was unable to define a list similar to this example to get this function working. Was anybody more successful with Text.Combine? Seb

@GoodlyChandeep Жыл бұрын

Sorry what is the issue that you're facing. Text.Combine works on a list (and not a column references)

@sebfromgermany3819 Жыл бұрын

I have a table with three columns Col1 … Col3. I merge the columns into a new column via add column / merge column with gives this M code: = Table.AddColumn(Source, "ColCombine", each Text.Combine({[Col1], [Col2], [Col3]}, "|"), type text) I wanted to do something similar to your video that I have a formula like = Table.AddColumn(Source, "ColCombine", each Text.Combine(ListColHeading, "|"), type text) So far I did not manage to create a List that the content of [Col1], [Col2], [Col3] is referenced - did I explain okay?

@sebfromgermany3819 Жыл бұрын

A possible solution is using Table.CombineColumns instead of merging the columns into a new column. The steps to perform are - Creating the list “ColList” from the tables header - Transforming every column into type text using “ColList“ in combination with Table.TransformColumnTypes - Applying Table.CombineColumns in combination with “ColList“ Code could look like this: Source … IntoText = Table.TransformColumnTypes(Source, List.Transform(ColList, each {_, type text})), CombineCols = Table.CombineColumns(IntoText,ColList,Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"ColsCombined") I got the inspiration for this from the video 😉 kzfaq.info/get/bejne/Z8yea8mJvt2afWg.html

@viveksharma4193 2 жыл бұрын

Apart from your video, I have a question, is there any way to fetch data from Facebook by using a power query?

@GoodlyChandeep 2 жыл бұрын

www.goodly.co.in/facebook-dashboard-in-excel/

@janayarangel4238 Жыл бұрын

SAVING LIIIIIIVESSSSSSS

@GoodlyChandeep Жыл бұрын

Thank You!

@arunkumarkesavan6077 Жыл бұрын

Super duber

@GoodlyChandeep Жыл бұрын

Thanks!

@h.m.nafidrahman2145 2 жыл бұрын

Chandeep, how do i dynamically split up to the second last value in PBI for each record? For e.g. I have a table with EE hierarchy for each user- For John, hierarchy is User1.User2.User3.User4 If John is User4, I want to split his hierarchy till User3. For Jane, hierarchy is User1.User2.User3.User4.User5 If Jane is User5, I want to split her hierarchy till User4 With the current setup, the columns are split into 5 columns by default, as there are 5 users in the table. So even though John is User4, the columns are split into 5 columns, where the 4th column consists of John's name. This is how the columns are split: User 1 | User 2 | User 3 | User 4 | User 5 M1 | M2 | M3 | John | NULL M1 | M2 | M3 | M4 | Jane I want the columns to stop at User 3 for John, so column USER 4 should be NULL as well Any way to split the columns based on each record, rather than looking at the maximum number of splits in the table?