Unstacking Rows into Columns - Using M Code in Power Query
Вставка
- Опубліковано 16 кві 2023
- Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
In this video, we will discuss a slick & efficient way to unstack rows in separate columns using M Code in Power Query
===== 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/
Download File ⬇️ - goodly.co.in/unstacking-rows-...
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - goodly.wordpress@gmail.com
===== 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! - Наука та технологія
Where do you learn this stuff? As usual mind blown.
I’ve seen this pattern in Matt Allington’s work but Chandeep has so much more to share. The bro rocks!
All thanks to the shitty data that work with. 😀
Good one Chandeep. After List.Split, I used Table.FromRows, as in:
let
Source = Excel.CurrentWorkbook(){[Name="Table18"]}[Content],
ListBucket = List.Split(Source[Data],4),
MakeTable = Table.FromRows(ListBucket,{"Name","City","Age","Phone"})
in
MakeTable
Just another approach. Thumbs up!!
Yes, this is better.
💛Thank! I was scratching my head why isn't he using a Table.FromRows function, there has to be one!
I tried using Table.FromRows but at the time of recording the video it returned some stupid error so I switched to creating a record and then a table. But I agree.. that is shorter approach.
Thanks a lot!
Thanks so much, borther! It is really helpful.
man, you make sooooo usable tutorials. thank you!
Happy to help Gui!
Brilliant as usual! I just had this type of data problem come my way so I’m glad I could use the M code version to solve it, thanks Chandeep!
Peace and love to you. Thanks and good luck in your life.
Dude Thank You!!!!! 😀😃😀 looking for this solution for a while thanks !!!!
Very cool - both methods, but, of course, I was really impressed with the M code method. Great explanation and a great demo of how M code can easily process lists and tables. M Code, though not initially all that intuitive, is amazing in its elegance and what it can do. Well done, Microsoft and thanks for the great presentation, Goodly. You make me very glad that I am a subscriber.
Always comes up with super fantastic approaches
Thanks Shriraj!
Beautifully Professional !
Thank you Raed!
You are really champ of Power Query❤❤❤
Thanks Kuldeep!
Thank you!👍
Again amazing video sir… thank you 😊
Glad you like it Abhijeet!
You are just awesome 🎉
Fantastic!
Great, Thank you.
Would love to see you solve more of these problems.
Here I am thinging of the ones in Chapther 13 of the book Master you data.
Excellent, Plz post maximum videos on Power Query Vidoe..
Excellent 💯👍
Great Video Chandeep
Thanks Pavan!
Many thanks Chandeep.
You can do it in two steps only
List.Split & Table.FromRows
Regards
Mohammed from Algeria
Omg I literally have a project I can use thus solution for right now!!!!
Cool!
I tried doing something equivalent using some different formulas and I agree this seems to be the most efficient approach. It is possible to include the types in the Table.FromRecords step, but since the data is text, it won't properly convert it, so you'll need a separate step to convert to the correct data types.
let
Source = null,
T1 = #table({"Data"},{{"John Smith"}, {" 32"}, {" 1989-07-15"}, {" New York City"}, {"Maria Garcia"}, {" 25"}, {" 1996-02-23"}, {" Los Angeles"}, {"David Lee"}, {" 46"}, {" 1977-09-01"}, {" San Francisco"}, {"Emily Chen"}, {" 19"}, {" 2004-05-10"}, {" Toronto"}, {"Mark Johnson"}, {" 56"}, {" 1967-12-28"}, {" Chicago"}, {"Samantha Wilson"}, {" 41"}, {" 1982-06-18"}, {" London"}, {"Robert Nguyen"}, {" 28"}, {" 1995-11-07"}, {" Houston"}, {"Karen Davis"}, {" 37"}, {" 1984-03-29"}, {" Sydney"}, {"Michael Brown"}, {" 63"}, {" 1958-10-12"}, {" Miami"}, {"Grace Kim"}, {" 22"}, {" 2001-01-09"}, {" Seoul"}}),
Custom1 = Table.FromRecords(List.Transform(List.Split(T1[Data],4), each Record.FromList(_, {"Name", "Age", "BDay", "City"}))),
#"Changed Type" = Table.TransformColumnTypes(Custom1,{{"Name", type text}, {"Age", Int64.Type}, {"BDay", type date}, {"City", type text}})
in
#"Changed Type"
Very clever.👍
Thanks Chrisinfreo!
Hi, video is mind blowing, suppose if column is sorted like all texts are in upper records and all the numbers are started from end of text values, how to get the sort order which shown in the video from that?
Love the Power Query tutorial. Using the TRANSPOSE formula also would have been a quick way to get the vertical data horizontally by counting out the lines in column B and creating an if statement in column C like =IF(B1=1,TRANSPOSE(A1:A4),"") and then filtering out the blank lines. Using Power query is cleaner though :) Love using custom columns. Last time I used Power Query was to duplicate lines based on a header reference as the value using a table within a table Table.Repeat(Table.FromRecords({[Col1=1]}),[#"header_reference"])
Do you mean Table.Transpose ?
@@arne.munther No, just the regular TRANSPOSE excel formula to make an array of data that is vertical to horizontal or vice versa. Sometimes diving into Power Query is more trouble than it's worth and simple regular formulas can do the job much quicker.
Sir both the approaches are Awesome 👏 Thanks for making such tutorials 🙂
In the second approach using M, can we directly use Table.FromRows after the split step, giving column names as list in second parameter ??
I guess not.. Each list item needs to be separated with a comma to put it out in columns.
Yes you can ❤
@@GoodlyChandeep I mean something like this -
let
Source = {"A", 25, "Mumbai", "B", 30, "Delhi", "C", 27, "Chennai"},
tbl = Table.FromRows(List.Split(Source,3),{"Name","Age","City"})
in
tbl
@@BhavyaGuptaGood
@@TheMoh148 Thank you :)
Hello this is a great solution! I was wondering after converting all of those into individual records. When load it into excel sheet how to load the records only with the column headers values of records only. Thanks in advance
Chandeep, nice video. What would you do if the number of rows to unstack varied. I have an issue where I am unstacking an address. At time it has the first level and then immediately the city and state. at other times it has the first address, then say a suite number, and then the line with city and state. Therefore, it is at times three lines and at other times only two lines.
Hey Chandeep, Is there a way to group this if the number of rows in the bucket vary. Thank you
DAX advanced course available? If yes link please
HI Goodly, I love the approach, but I would like to use your approach on a huge data set. The loading into power query is the easy bit, but how to transform it into a list when the number of rows per record varies and each rows contains has different number of elements on it.
name and id might be on row 1 or split over 2 rows. each record contains about 12 elements but extracting it across possible up to 8 rows.
thanks for your insight.
the data set is a mainframe extract
Hi Chandeep, my query is the same - I have different numbers of rows per record. To make it even worse, my list has an additional column with related information. The first 2 columns have payables from a salary sheet and the next 2 columns have the deductions
Send me a sample and expected output - goodly.wordpress@gmail.com
@@GoodlyChandeep Waiting for this Reply....Can i send you my own dataset too?
Yes sir, please!
Thanks
@@GoodlyChandeep I have done so, thanks in advance!
I do have a question, please.
I was able to follow along the first method all the way till the pivot column part. When I clicked on the pivot column button, instead of giving me the table as your example, I got a table where the first batch of rows only contain value in the first column, then the next batch of rows only contain value in the second column, and so on. It appears that the pivot did not reference both the Module and Subtraction as index for columns and rows.
My data has a slight difference than yours. Your data was in a single column and you added column headers later on. My data is in two columns, all the headings in one column and all the values in the other. I tried it with the headers column removed to mimic your data and it's worse than keeping the headers column and pivoting it.
Could you please give me some pointers? Thank you.
Great approach, as usual, but... is it more efficient? Whenever I work with large set of data an use list functions I struggle to output the results!
I doubt that lists are slowing down your query. It could be something else.
I generally try to avoid pivoting, transposing, sorting, distinct count large tables
Sequence formula can now make it super quick.
kool. Very informative
Would this method work with a uneven or random number fields, say a comment field or a 2nd phone number.
How would you handle that?
Upcoming video 😀
What column did you pivot on at mm 2:00 (Pivoted Column1)?
Given we do not have PQ and M code, can we still do this in plain Excel with formula?
Hi, please can you share the logic to show the week from Sunday to Saturday. If month ends in between week like on Tuesday or Wednesday, it should still consider the dates from next month till Saturday and consider those dates in previous month's last week
Can you help how to code dynamically Multiple skills and proficiency pivoted
Great video, nut I have a question, how applied this method to a data where number of information change for every person. For example Cecilia has 3 additional information, Iris has 7, Joanne has 5, Peter has 3 and so on.
May be the next video 😁
What if you have many columns of data on the same spreadsheet?
How would you do if there are 1-M rows under a heading
Shall be make a live steps for interface instead the recorded
May I ask what software you use for drawing on screen?
Zoom it
@@GoodlyChandeep, thank you! And from MS. Awesome. Looks like a great aide for classroom settings when training.
Que hago en este caso: tengo una lista D ,x1,x2 D c1,c2,c3, F d1,d2,d3,d4 y necesito que D,D y F me queden como otra columna al lado de la lista?? 0:09
[D,D],[D,x1] ,[D,x2], [D,D],[D,c1] ,[D,c2], [D,c3], [F,F],[F,d1] ,[F,d2], [F,d3],[F,d4] sería el resultado de la matriz
Hi can any 1 share the M Code so that its easy to copy as noting it down from video causes troubles....
You can get the M code from the description's downloadable file.
Afer List.Split, you could have simply used Table.FromRows. No need for records here :).
I agree. During recording the video some error cropped so I switched to records.
Thanks Victor!
Actually, I left many of comments to you with appreciation or explanation but unfortunately you did not answer for any!
I admire all your comments!
Cheers :)