Transform data with Pivot function for multiple value columns

[Tribal]

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

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

[slorin · Accepted solution]

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
Pivot

 Stéphane

[Tribal]

Hi Stéphane,

That works perfectly! Thank you so much for the quick reply. 

Have a great day!

Best regards,

Arno