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:
And it shall look like this:
You first unpivot your measure columns:
Resulting in this:
Now merge Product & Attribute in order to create your new header column:
On this new column you now perform your new pivot operation:
& voilà: Pivot multiple measures in Power Query and Power BI:
You can download the file: Pivot multiple columns
Enjoy & stay queryious 🙂
This is fantastic. The steps worked flawlessly for what i was trying to do.
Very pleased to hear – thanks for leaving this feedback!
Thank you so much! These problems would be really hard without clear documentation like yours!
Thank you Christopher 🙂
This was the perfect solution for me, thanks!
Worked like a charm, thanks 😉
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.
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
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
Superb!! Works exactly the way I want. Kudos to the admin team.
Thanks a lot ! Efficient and a nice description of the process.
Thank you very much for this elegant solution!
When pivoting multiple columns, is it possible to set different types of aggregation for each pivoted column? For example, can one pivoted column use SUM while another column uses MAX? Thanks in advance.
Man you saved my life! thanks a lot !
Pingback: Can you use Excel formulas in Power Query? – Jiuya Tech Blog
I’ve been struggling with it for good couple of hours. Thank you!
Thank you so much for this solution ❤
My intention was to do pivot table like behaviour in Power Query so that branches are in the “columns bucket”, and then do a join(merge) on the result with other tables => a fully dynamic non-standard pivot table.
This solution helped me a lot. But I wonder whether there is a way to split the branch names (or Products in this article) from the titles after merging with the Attribute Name. and put them on the top (to get real 2 cascading rows for the headers – branch at the top and attribute name in the next row.
This helped massively, thank you!