Forum Discussion

Tribal's avatar
Tribal
Frequent Visitor
3 years ago
Solved

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 ...
Need Help
  • slorin's avatar
    slorin
    3 years ago

    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

slorin's avatar
slorin
Icon for Super User rankSuper User
3 years ago

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's avatar
    Tribal
    Frequent Visitor
    3 years ago

    Hi Stéphane,

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

    Have a great day!

     

    Best regards,

    Arno