Forum Discussion
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/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
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.
3 Replies
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.
Hi Dinesh_Suranga , that worked a treat, thanks very much! 🙂
MichaelF1 Follow the below steps,
- UNPIVOT num1 and num2 columns
- Merge category and attribute( has num1 and num2 as values) columns
- 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!
