Unpivot Multiple Column Groups | Advanced Power Query - Case Study

[@GoodlyChandeep]

Download the Excel file ⬇ - goodly.co.in/unpivot-multiple-column-groups Master {M} in Power Query - goodly.co.in/learn-m-powerquery/

[@НиколайСавенко-ы5ъ]

let from = Excel.CurrentWorkbook(){[Name="Data"]}[Content], lst = List.Buffer(List.Zip({List.RemoveNulls(Record.ToList(from{0}))})), nms = {"Country"}&List.Distinct(Record.ToList(from{1})), tr = Table.ToList(Table.Skip(from,2),(x)=>List.Zip({lst,List.Split(x,3)})), to = Table.FromList(List.Combine(tr),List.Combine,nms) in to

[@theowemer5782]

The Wizard of India, fantastic!

[@ankursharma6157]

Thank You Master 🙏🏽

[@thierrysouchard471]

Great! as usual.🎆

[@alateng]

謝謝！

[@ZAN29]

Thank you! 👍 But usually I transpose, change the type of first joining columns by delimiter then transpose back, then you you can split it back

[@mrbartuss1]

Straight to the point. Really appreciate that

[@Bhavik_Khatri]

Excellent tutorial.

[@lukev730]

Awesome Chandeep 🎉

[@alexrosen8762]

Excellent!

[@paspuggie48]

Big round of applause Chandeep 😊😊

[@karolinab9749]

Thank you!😊

[@williamarthur4801]

That was really good, I think I 've seen you do a similar technique before, I had a go before watching and came up with getting the countries and tables as lists and then table form columns ; Table.FromColumns( { List.RemoveNulls( Record.FieldValues( Source {0} )) } & {ttables} ,{"Country", "Xpand"} )

[@williamarthur4801]

I also had a go using accumuate but as yet no luck my idea was sort of zipping the lists so; List.Accumulate( {0..2} , ttables, (s,c)=> { country {c} } & ttables {c} )

[@williamarthur4801]

Just as an update, I'm not sure this is a great way of going about things but; let listA = List.Repeat({{{1, 2, 2}, {3, 3, 3}, {3, 4, 2}}}, 3), listB = List.Repeat({{{"a", "a", "a"}, {"b", "b", "b"}, {"c", "c", "c"}}}, 3) , result = List.Transform( {0..2}, (x)=> listA{x} & {listB{x}{x}} ) in List.Transform( result,(x)=> Table.FromColumns(x))

[@jaybuddhadev3543]

Awesome.

[@mogarrett3045]

magical

[@Vije99521]

Felt like a magic !🪄

[@sudbn]

chandeep sir ❤

[@HachiAdachi]

As other commenters have also suggested, my approach would have been: transpose, fill down, pivot, and change column names. I assume you wanted to show us the M techniques that you showcased in the video (which was great, btw), but are there any practical advantages with your approach over mine? Performance or otherwise?

[@GoodlyChandeep]

All is good but pivoting is an expensive operation

[@akashVarasada]

I guess transpose with fill down is way easy approach to do this.

[@Bhaskar_Joshi_there]

Kiya baat h Chandeep day by day glow kr rahe ho 😅😅

[@briandennehy6380]

M code is so powerful when you know how

[@balagapallavi782]

Hi goodly i have the date like you created with list like this and i want to change the date what u shown at the starting model in my last step is it possible

[@GeertDelmulle]

Hey Chandeep, question: why use an index solution and not just HSTACK the tables using the Table.FromColumns function like you did before? Am I missing something?

[@boissierepascal5755]

It seems possible to replace table indexation by Liste.Position over the list of countries. Right !?

[@liam3401]

how do you do this on reverse (pivot) Chandeep?

[@naumanafzal6600]

I am mainting employee payroll register. I have allowances and deductions. In allowances, there are multiple items like, base, hr, utilities, transport etc. similarly i have different columns in deductions. I want to dusplay allowances and deductions for each employee in 2 rows. First row needs to be allowances and in second row deduction to appear. How do i do that in power query?

[@abhishekawari919]

What if we have not fixed length of columns for unpivoting

[@dmclean3324]

🤯🤯🤯

[@vigneshs3488]

Unpivot and pivot action on multiple files from same folder is taking time, is there any way to process them faster ? My raw file will have sales demand for next 52 weeks. Will get new file every week. So how to process them in best way

[@GoodlyChandeep]

try not to pivot the data.. it's an expensive operation.

[@vinhwizard]

super siêu

[@Ratnakumarwrites]

Chandeep the {M}AN...

[@gurvindersingharora5301]

Now upload DAX videos also

[@GoodlyChandeep]

Sure!

[@chrism9037]

This is great, thanks Chandeep!

[@jasvindersingh4992]

Hello Sir, I'm trying to enroll for Dax course and unable to pay. No fault from your side. Need help for successful payment. Pls help

[@jasvindersingh4992]

Is it bcz I'm paying by debit card not credit card?

[@GoodlyChandeep]

please drop us a email and we'll help you out - [e-mail]

[@JoseAntonioMorato]

Dear, Same result with dynamic formula: =LET(_rows,ROWS(B7:B11), _country_1,B5, _country_2,E5, _country_3,H5, _array,VSTACK(B6:D11,E7:G11,H7:J11), _repcount_1,TEXTSPLIT(REPT(_country_1&",",_rows),,",",1), _repcount_2,TEXTSPLIT(REPT(_country_2&",",_rows),,",",1), _repcount_3,TEXTSPLIT(REPT(_country_3&",",_rows),,",",1), _result,HSTACK(VSTACK("Country",_repcount_1,_repcount_2,_repcount_3),_array), _result) 🤗

[@navisalomi]

Excellent as always Chandeep! I tried this on my own before watching the video. Longer step, but got the job done 😎: //---------------------- let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], Headers = let first2rows = Table.FirstN(Source, 2), tbl = Table.Transpose(first2rows), tbl_filldown = Table.FillDown(tbl, Table.ColumnNames(tbl)), add_col = Table.AddColumn(tbl_filldown, "Headers", each Text.Combine({[Column1], [Column2]}, "|")) in tbl_filldown, NumCols = List.PositionOf(Headers[Column2], "Sh%") + 1, RowsCombined = Table.ToRows(Table.Skip(Source, 2) ), EachRowSplitToPages = List.Transform(RowsCombined, each List.Split(_, NumCols)), MapDataToColumns = let data_rec = List.Transform(EachRowSplitToPages, (x)=> List.Transform(x, each Record.FromList(_, List.Distinct(Headers[Column2]) ) )), countries = List.Distinct (Headers[Column1]), result = List.Transform(data_rec, (x)=> List.Transform(x, each let pos = List.PositionOf(x, _) in [Country = countries{pos}] & x{pos})) in Table.FromList(result, Splitter.SplitByNothing(), null, null, ExtraValues.Error), CreateTable = let tbl = Table.ExpandListColumn(MapDataToColumns, "Column1") in Table.FromRecords(tbl[Column1]) in CreateTable //-----------------------------

[@НиколайСавенко-ы5ъ]

let from = Excel.CurrentWorkbook(){[Name="Data"]}[Content], lst = List.Buffer(List.Zip({List.RemoveNulls(Record.ToList(from{0}))})), nms = {"Country"}&List.Distinct(Record.ToList(from{1})), tr = Table.ToList(Table.Skip(from,2),(x)=>List.Zip({lst,List.Split(x,3)})), to = Table.FromList(List.Combine(tr),List.Combine,nms) in to