Forum Discussion
Transform data with Pivot function for multiple value columns
Hi,
In powerquery, I'm trying to pivot a table consisting of a single attribute column, with 6 value columns. I did find this post, and I've tried multiple ways to adapt it to my dataset. But it isn't giving me the desired result unfortunately:
https://community.fabric.microsoft.com/t5/Power-Query/Power-Query-How-to-transform-data-with-Pivot-function-for/m-p/1862383#M55343
I would like to get the following result:
But what happens with the solution provided in the linked post, is that the duplicate values of column 4 result in getting 10000+ columns. I also tried to manually adapt various parts of the custom function, but didn't seem to get the desired result.
I hope someone has a suggestion how I can adapt the previously provided solution to work for this dataset, or has another suggestion. Thank you!
Best regards,
Arno
Hi
3 steps :
- UnPivot "Value 1" to "Value 6"
- Combine Column 4 + Attribute
- Pivot this new column with List.Sum
let
Source = YourSource,
UnPivot = Table.Unpivot(Source, {"Value 1", "Value 2", "Value 3", "Value 4", "Value 5", "Value 6"}, "Attribute", "Value"),
Combine = Table.CombineColumns(UnPivot,{"Column 4", "Attribute"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Attibute"),
Pivot = Table.Pivot(Combine, List.Distinct(Combine[Attibute]), "Attibute", "Value", List.Sum)
in
PivotStéphane
2 Replies
slorinSuper User
Hi
3 steps :
- UnPivot "Value 1" to "Value 6"
- Combine Column 4 + Attribute
- Pivot this new column with List.Sum
let
Source = YourSource,
UnPivot = Table.Unpivot(Source, {"Value 1", "Value 2", "Value 3", "Value 4", "Value 5", "Value 6"}, "Attribute", "Value"),
Combine = Table.CombineColumns(UnPivot,{"Column 4", "Attribute"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Attibute"),
Pivot = Table.Pivot(Combine, List.Distinct(Combine[Attibute]), "Attibute", "Value", List.Sum)
in
PivotStéphane
Hi Stéphane,
That works perfectly! Thank you so much for the quick reply.Have a great day!
Best regards,
Arno
