Today I’m sharing with you one of my killer M(inja)-strikes: Unpivot a table by simply passing the number of columns that shall remain (at the left side of the table) and the number of rows (who hold the header-information) as parameters to them.
This is not only incredibly flexible (multiple header rows), but also very robust: You don’t have to care about changing column names for future refreshed or when you apply it to partitioned tables where the partitions itself have different column names already.
Unpivot by numbers
here: 3,2 (number of columns, number of rows):
returns:
Flexible and robust:
Your table has to be prepared as follows:
- The columns that shall NOT be unpivoted must stand on the left side of your table. Their number must match the 2nd parameter you feed into the function (“FirstNColumnsToKeep”)
- The header rows that shall be unpivoted must sit in the first rows of your table. So one of them still sit as the header row itself, you have to demote it (Home -> Transform -> dropdown at “Use First Row as Headers” -> “Use Headers as first rows”)
M-Code
M-code as text: MCodeDoc.txt
The magic trick in this query is step “ValuesRecord” in row 18. It basically unpivots the values of each row and returns a table holding everything you need:
The red arguments holds the magic that creates the table and transposes it in one go.
Subscribers can download the zipped pbix: UnpivotByNumbers.zip That file also holds a query that allows you to follow every step of the function like shown in the picture above 😉
Enjoy & stay queryious 🙂
Edit 21-June-2017: My M-teacher Bill Szysz has come up with a solution that is much faster than mine. He describes it as “old fashion” and I agree, mine looks better … and is shorter. But this all doesn’t help, if performance counts 🙂 So here we go & say many thanks to Bill again:
Bill’s code for download: UnpivotByNumbersBillSzysz.M.txt
Excellent solution, Imke! Although I readon’t it from phone and can’t check this trike right now, I can say that it is one of the best unpivot functions i’ve seen
Thx Maxim 😉
Awesome solution! If you get a chance, could you please explain what List.Buffer does in this context, and why performance would suffer without it in that particular step? I’ve not been quite able to wrap my head around the List.Buffer and Table.Buffer concepts, beyond dropping them in willy nilly… 🙂
Thank you Brian!
Actually, I cannot remember why I’ve included that here. Maybe I was trying a different method first & then forgot to remove it.
I normally only use it when the buffered content will be referenced multiple times in the subsequent query (like in List.Generate). – But sharing your experience: It’s is often just trial & error, as the execution plans that will result at the end are hidden and performance sometimes shows unexpected behaviour 😉
Hi,
What value do i put in for “t”?
There goes the reference to the table you want to apply this operation on.
It’s either the query-name, if you apply this as a new query, or the name of the previous step.
Hello! Thanks for the help!
Is there any updates on this with recent changes or newer versions of Power BI?
..just curious if there is any improved way or easier way, from then to now.
No to my knowledge Jorge.
/Imke
Pingback: Writing data to GitHub using Power Query only – The BIccountant
Awesome soo good. You’re a genius freind