Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MichaelF1
Helper II
Helper II

Help needed with PIVOT/UNPIVOT in Power Query

Hi everyone,

 

I have the following data structure in Power Query:

date categorynum1num2
01/01/2022A17
01/01/2022B28
02/01/2022A39
02/01/2022B410
03/01/2022A511
03/01/2022B612

 

I need to get it into this form:

 

datenum1Anum2Anum1Bnum2B
01/01/20221728
02/01/202239410
03/01/2022511612

 

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

 

 

 

1 ACCEPTED SOLUTION
Dinesh_Suranga
Responsive Resident
Responsive Resident

@MichaelF1 ,

Hi

Try following steps.

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

Dinesh_Suranga_0-1664543406573.png

 

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

Dinesh_Suranga_1-1664543591823.png

 

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

Dinesh_Suranga_2-1664543721310.png

Finally you will get table as follow.

Dinesh_Suranga_3-1664543772536.png

 

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.

View solution in original post

3 REPLIES 3
MichaelF1
Helper II
Helper II

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

Dinesh_Suranga
Responsive Resident
Responsive Resident

@MichaelF1 ,

Hi

Try following steps.

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

Dinesh_Suranga_0-1664543406573.png

 

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

Dinesh_Suranga_1-1664543591823.png

 

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

Dinesh_Suranga_2-1664543721310.png

Finally you will get table as follow.

Dinesh_Suranga_3-1664543772536.png

 

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
Resident Rockstar
Resident Rockstar

@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

SivaMani_0-1664543095711.png

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.