Advanced Unpivoting Tricks in Power Query

In this video, we look at three different examples of pivoted data and learn how you can use Power Query and the M language to unpivot the data, transforming it into a more organized output. If you've found yourself stuck with messy pivoted datasets, this tutorial will show you a step-by-step process on how to transform them into a more usable format.

===== ONLINE COURSES =====
✔️ Power BI Beginner’s Course -

✔️ Master 'M' in Power Query -

✔️ Mastering DAX in Power BI -

✔️ Power Query Course-

✔️ Master Excel Step-by-Step-

✔️ Business Intelligence Dashboards-

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

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

===== 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'll find a ton of interesting videos that I have created :) Cheers!

[flaviobertasi]

Exactly what I was looking for. Thanks a lot. You're a PQ-God.😉

[RogerStocker]

Hey Chandeep

you are teaching like a hurricane.
I really enjoy every second. None can be better for M language, I'm convinced.

[MichaelBrown-lwkz]

This is the best data preparation video I have watched to date. And I have watched a lot of them. Chandeep does an excellent job of informing the viewer which Power Query functions are important to learn. Now I can go back an learn each Power Query function he used in this instructional video. This man is a phenomenal teacher.

[gorxor]

Second 55 and you won my heart. "Who the hello prepared the data" followed by "this is why we have power query"

[stefanusaditya]

Pure {M}agic. And looks efficient too, in terms of resources, by not transposing directly and potentially creating hundreds of columns that could slow down PQ.
Big fan of yours.

[BotondKuti]

that first one contains a solution I couldn't figure out or find anywhere else, your videos are always top quality from every aspect, big thanks, highly appreciated :)

[LambdaBam]

Chandeep, you've not only helped learn power query, but you've taught me some really important coding skills too!

[osoriomatucurane]

Sensational tutorial, diving deep into M Code Magic and playing arround with lists and tables.
Thank you for sharing, you truly master the state art of teaching. Keep it up

[-seesi-]

Hey Chandeep!

In your third example you hardcoded the 3 (25:00).
You could wrap the SecRecordAsHeaderList step into List.Count, so you get the number of the cols!

[baskis]

Incredible, pure magic {M}.... Masterfully explained .... thanks for sharing!

[samitube]

I'm facing a real challenge right now, and this video came at the perfect time. Highly appreciated

[Softwaretrain]

This is an excellent example of utilizing the advanced unpivoting technique. Thank you for the fantastic video and explanation! I’ve attempted to solve these three examples using a different method, which may provide your audience with an alternative solution, as outlined below.

Example1:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Custom1 = Table.Combine(List.Transform(Table.Split(Source, 3), each
in
Custom1

Example2:
let
Source = Excel.CurrentWorkbook(){[Name="Dataset"]}[Content],
split = Table.SplitAt(Source, 2),
Header = List.Transform(Table.ToRows(Table.FillDown(Table.Transpose(split{0}), {"Column1"})), each Text.Combine(_, "|")),
AddHeader = Table.RenameColumns(split{1}, List.Zip({Table.ColumnNames(split{1}), Header})),
Unpivot = Table.UnpivotOtherColumns(AddHeader, {"Bz"}, "Header", "Value"),
SplitColumnbyDelimiter = Table.SplitColumn(Unpivot, "Header", Splitter.SplitTextByDelimiter("|", QuoteStyle.None), {"Company", "Dept"}),
ChangedType = Table.TransformColumnTypes(SplitColumnbyDelimiter, {{"Bz", type text}, {"Company", type text}, {"Dept", type text}, {"Value", Int64.Type}})
in
ChangedType

Example3:
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
Custom1 = Table.Combine(List.Transform(List.Split(Table.ToColumns(Source), 3), each
[a=Table.FromColumns(_),
b=Table.PromoteHeaders(Table.Skip(a, 1)),
c=Table.AddColumn(b, "Country", each a{0}[Column1]) ][c]
))
in
Custom1

[KleanthisSkoulikaris]

Oh man, watching you keep adding to the first M code formula instead of using multiple steps reminded of Matt Damon talking about Jack Nicholson in the movie "Departed"....
"Now, you COULD end the scene there, but if you keep the camera rolling...." 😁😁😁.
You're like the gift that keeps on giving!

[emanuelecostantinocatanzar]

Hey, Chandeep, I love your way of teaching.
First of all, you explain the logic behind the problem and then you also get fun in fixing the issue.
Everything seems so easy 🙂
Your knowledge of the matter is outstanding.
Great great job, man!

[bendreessen]

Amazing! you provided the exact solution I needed! thank you so much for all of your help and easy to understand teaching style!!

[StephenDagg]

Thanks so much Chandeep, these tutorials are fantastic. Keep 'em coming!

[freipfeifenprospekt]

Whow. Apart from the excellent content - where did you learn to teach so well?

[carolshipley]

This is brilliant Chandeep. Just what was needed for the problem I sent you. Thank you very much.

[demris]

Love this... you gave me a lot of good ideas to try for myself!! Thanks

[enocharthur]

This is incredible. I check out your master m language course it's very expensive. Kindly reduce the cost a lot