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):
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 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.
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