Forum Discussion

MichaelF1's avatar
MichaelF1
Helper III
3 years ago
Solved

Help needed with PIVOT/UNPIVOT in Power Query

Hi everyone,   I have the following data structure in Power Query: date  category num1 num2 01/01/2022 A 1 7 01/01/2022 B 2 8 02/01/2022 A 3 9 02/01/2022 B 4 10 03...
Need Help
  • Dinesh_Suranga's avatar
    Dinesh_Suranga
    3 years ago

    MichaelF1 ,

    Hi

    Try following steps.

    Step 1.  Select num1 and num2 columns then click unpivot column.

     

    Step 2. First select Attribute column then select Value column then click MergeColumns.

     

    Step 3. First select Merged column then select Value column then click Pivet Column.

    Finally you will get table as follow.

     

    M code.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMjJR0lRyA2BGJzpVgdNCknIAbRFhApIzRdxkBsiSkF0mUCMtQAImeMps0UJGeIKQfSZwaSM1KKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"date " = _t, category = _t, num1 = _t, num2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date ", type date}, {"category", type text}, {"num1", Int64.Type}, {"num2", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"date ", "category"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Columns",{"Attribute", "category"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value", List.Sum)
    in
    #"Pivoted Column"

     

    Thank you.