Help needed with PIVOT/UNPIVOT in Power Query

[MichaelF1]

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/01/2022	A	5	11
03/01/2022	B	6	12

I need to get it into this form:

date	num1A	num2A	num1B	num2B
01/01/2022	1	7	2	8
02/01/2022	3	9	4	10
03/01/2022	5	11	6	12

I've tried various iterations of Pivot and Unpivot but can't seem to get it right!

Is anyone able to help please? 🙂

Thank you

Michael

[MichaelF1]

Hi @Dinesh_Suranga , that worked a treat, thanks very much! 🙂

[Dinesh_Suranga · Accepted solution]

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

[SivaMani]

@MichaelF1 Follow the below steps,

1. UNPIVOT num1 and num2 columns
2. Merge category and attribute( has num1 and num2 as values) columns
3. PIVOT the merged column

 #"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)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!

MCT | Power BI Analyst | Azure Data Engineer

LinkedIn