Unpivot by number of columns and rows in PowerBI and PowerQuery in Excel

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

Comments (7) Write a comment

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

    Reply

  2. 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… 🙂

    Reply

    • 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 😉

      Reply

  3. Pingback: #Excel Super Links #76 – shared by David Hager | Excel For You

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

      Reply

Leave a Reply