How to pivot multiple measures and columns in Power Query and Power BI

The pivot-function in Power Query lets you pivot your data within the query already. This comes in handy quite often when you want to further work on those results within the query. There’s not much to parametrise in the function, so the question is how to handle it when you want more than one measure/value to be returned.
Let’s say you have a table like this:

Ausgangstabelle

And it shall look like this:

02_Ziel

You first unpivot your measure columns:

Schritt1

Resulting in this:

Schritt2

Now merge Product & Attribute in order to create your new header column:

MergeNew

On this new column you now perform your new pivot operation:

NewPivot

& voilà: Pivot multiple measures in Power Query and Power BI:

Zieltabelle

You can download the file: Pivot multiple columns

Enjoy & stay queryious 🙂

Comments (11) Write a comment

  1. This is fantastic. The steps worked flawlessly for what i was trying to do.

    Reply

  2. Thank you so much! These problems would be really hard without clear documentation like yours!

    Reply

  3. Thanks yes, a very clear and memorable explanation and example of how to do this. I came across another solution, this code,

    let
    src = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
    lettersABC=List.Distinct(src[Attribute1]),
    count=List.Count(lettersABC),
    lettersNUM=List.Transform({1..count}, each “Letter”&Number.ToText()),
    numbersNUM=List.Transform({1..count}, each “Number”&Number.ToText(
    )),
    group = Table.Group(src, {“ID”}, {{“attr”, each Record.FromList(lettersABC&[Attribute2], lettersNUM&[Attribute1])}}),
    exp = Table.ExpandRecordColumn(group, “attr”, lettersNUM&lettersABC, lettersNUM&numbersNUM)
    in
    exp

    Which may be elegant and may work, though the questioner (Stack Overflow) I’m sure had no idea how to use it (even after someone told them to paste it into the advanced editor, yeah, then what?) and neither do I.

    Reply

  4. Hi! Does this process also work when the Values column in fact contains values but also text? I tried it out and the result of the last step (pivot of merge) is a bunch of errors unfortunately, and I’m trying to find out why:D If it wer eto work, it would solve all my problems haha

    Reply

    • Hi xy,
      it should work with text as well.
      You need to have a remaining key-column like the DateKey in my example.
      Otherwise: What does the error message say?
      /Imke

      Reply

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz