Unstacking Rows into Columns - Using M Code in Power Query

[@wayneedmondson1065]

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!!

[@user-mma173]

Yes, this is better.

[@joneskiller8]

💛Thank! I was scratching my head why isn't he using a Table.FromRows function, there has to be one!

[@GoodlyChandeep]

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!

[@damionc]

Where do you learn this stuff? As usual mind blown.

[@joedi]

I’ve seen this pattern in Matt Allington’s work but Chandeep has so much more to share. The bro rocks!

[@GoodlyChandeep]

All thanks to the shitty data that work with. 😀

[@stretch233]

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!

[@willm7994]

Dude Thank You!!!!! 😀😃😀 looking for this solution for a while thanks !!!!

[@christophertauss3640]

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.

[@andrewsinha2785]

Peace and love to you. Thanks and good luck in your life.

[@guilouback]

man, you make sooooo usable tutorials. thank you!

[@GoodlyChandeep]

Happy to help Gui!

[@TheMoh148]

Many thanks Chandeep. You can do it in two steps only List.Split & Table.FromRows Regards Mohammed from Algeria

[@MrAbrandao]

once more your work is great.

[@shrirajdeshpande8125]

Always comes up with super fantastic approaches

[@GoodlyChandeep]

Thanks Shriraj!

[@arne.munther]

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.

[@danishnawaz7504]

Excellent, Plz post maximum videos on Power Query Vidoe..

[@KuldeepSingh-nq1vi]

You are really champ of Power Query❤❤❤

[@GoodlyChandeep]

Thanks Kuldeep!

[@mostafayeganeh6862]

Thanks so much, borther! It is really helpful.

[@saikumarboga3383]

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?

[@tecwzrd]

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"])

[@arne.munther]

Do you mean Table.Transpose ?

[@tecwzrd]

@@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.

[@josh_excel]

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.

[@josh_excel]

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"

[@m.raedallulu4166]

Beautifully Professional !

[@GoodlyChandeep]

Thank you Raed!

[@enricomendiola9952]

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

[@abhijeetshetye3764]

Again amazing video sir… thank you 😊

[@GoodlyChandeep]

Glad you like it Abhijeet!

[@PurplePegasus2531]

Omg I literally have a project I can use thus solution for right now!!!!

[@GoodlyChandeep]

Cool!

[@BhavyaGupta]

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 ??

[@GoodlyChandeep]

I guess not.. Each list item needs to be separated with a comma to put it out in columns.

[@TheMoh148]

Yes you can ❤

[@BhavyaGupta]

@@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

[@TheMoh148]

@@BhavyaGuptaGood

[@BhavyaGupta]

@@TheMoh148 Thank you :)

[@megabuilds3007]

Great, Thank you.

[@007lbp]

Fantastic!

[@johnedwards7024]

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.

[@sabarirajagopalan9688]

You are just awesome 🎉

[@rrrraaaacccc80]

Excellent 💯👍

[@PavanKumar-vi7hd]

Great Video Chandeep

[@GoodlyChandeep]

Thanks Pavan!

[@Luciano_mp]

Thank you!👍

[@anuradhaabewardana314]

Hey Chandeep, Is there a way to group this if the number of rows in the bucket vary. Thank you

[@ssurajkr1234]

DAX advanced course available? If yes link please

[@ym10up]

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.

[@Chris_in_fremantle]

Very clever.👍

[@GoodlyChandeep]

Thanks Chrisinfreo!

[@JohnAdair-k7j]

What column did you pivot on at mm 2:00 (Pivoted Column1)?

[@amitsawant2577]

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

[@nunolourenco1532]

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!

[@GoodlyChandeep]

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

[@ArthurCapers]

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

[@excelworx8712]

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

[@GoodlyChandeep]

Send me a sample and expected output - [e-mail]

[@wensesvincen4877]

@@GoodlyChandeep Waiting for this Reply....Can i send you my own dataset too?

[@GoodlyChandeep]

Yes sir, please! Thanks

[@excelworx8712]

@@GoodlyChandeep I have done so, thanks in advance!

[@shanshines11]

Given we do not have PQ and M code, can we still do this in plain Excel with formula?

[@VijayYadav-tx6ft]

if i want to make list in base of blank row what is necessary changes

[@marivicmawanay3246]

Can you help how to code dynamically Multiple skills and proficiency pivoted

[@pattyboy1233]

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?

[@GoodlyChandeep]

Upcoming video 😀

[@eslamfahmy87]

Shall be make a live steps for interface instead the recorded

[@piotrsiwiec4396]

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.

[@GoodlyChandeep]

May be the next video 😁

[@ricckymishra]

Sequence formula can now make it super quick.

[@sundarun]

How would you do if there are 1-M rows under a heading

[@choiseunghyunify]

What if you have many columns of data on the same spreadsheet?

[@michaelt312]

May I ask what software you use for drawing on screen?

[@GoodlyChandeep]

Zoom it

[@michaelt312]

@@GoodlyChandeep, thank you! And from MS. Awesome. Looks like a great aide for classroom settings when training.

[@victor_wang_1]

Afer List.Split, you could have simply used Table.FromRows. No need for records here :).

[@GoodlyChandeep]

I agree. During recording the video some error cropped so I switched to records. Thanks Victor!

[@AzharKhan-kd9bf]

Hi can any 1 share the M Code so that its easy to copy as noting it down from video causes troubles....

[@GoodlyChandeep]

You can get the M code from the description's downloadable file.

[@natatdem]

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

[@natatdem]

[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

[@eslamfahmy87]

Actually, I left many of comments to you with appreciation or explanation but unfortunately you did not answer for any!

[@GoodlyChandeep]

I admire all your comments! Cheers :)