Forum Discussion
Solved
Unpivot two rows title
Hi Still learning more complex unpivot with two or three levels (rows) in title. Can anybody give a hint how these two tables can be unpivoted or how complex is the task: Unpivot.xlsx
You can do this with Pivot and Unpoint options given in the power BI.
Please refer the below Power BI file and upload sheets and go through the steps i have taken to reach to the final outcome. I have included the code aswell
https://www.dropbox.com/s/zksrv5a91lgaq1o/Bi%20Help.xlsx?dl=0
https://www.dropbox.com/s/il61b48ojsqo6a3/BI%20Help.pbix?dl=0
let Source = Excel.Workbook(File.Contents("C:\Users\Dilumd\OneDrive - Brandix Lanka Pvt Ltd (1)\Brandix Data\PowerBi\Help\Bi Help.xlsx"), null, true), Table_2_Sheet = Source{[Item="Table_2",Kind="Sheet"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Table_2_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Promoted Headers1" = Table.PromoteHeaders(#"Promoted Headers", [PromoteAllScalars=true]), #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers1",{{"Units", "Oct 16-Units"}, {"Value", "Oct 16-Value"}, {"Units_1", "Nov 16-Units"}, {"Value_2", "Nov 16-Value"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"COUNTRY", type text}, {"PRODUCT CATEGORY", type text}, {"PRODUCT GROUP", type text}, {"PRODUCT", type text}, {"PRODUCT ID", type text}, {"Oct 16-Units", Int64.Type}, {"Oct 16-Value", Int64.Type}, {"Nov 16-Units", Int64.Type}, {"Nov 16-Value", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"COUNTRY", "PRODUCT CATEGORY", "PRODUCT GROUP", "PRODUCT", "PRODUCT ID"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.None, false), {"Attribute.1", "Attribute.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type date}, {"Attribute.2", type text}}), #"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Attribute.1", "Date"}}), #"Pivoted Column" = Table.Pivot(#"Renamed Columns1", List.Distinct(#"Renamed Columns1"[Attribute.2]), "Attribute.2", "Value") in #"Pivoted Column"let Source = Excel.Workbook(File.Contents("C:\Users\Dilumd\OneDrive - Brandix Lanka Pvt Ltd (1)\Brandix Data\PowerBi\Help\Bi Help.xlsx"), null, true), Table_1_Sheet = Source{[Item="Table_1",Kind="Sheet"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Table_1_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type text}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"COUNTRY", type text}, {"PRODUCT CATEGORY", type text}, {"PRODUCT", type text}, {"CODE", type text}, {"Measure", type text}, {"10/1/2016", Int64.Type}, {"11/1/2016", Int64.Type}, {"12/1/2016", Int64.Type}, {"1/1/2017", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"COUNTRY", "PRODUCT CATEGORY", "PRODUCT", "CODE", "Measure"}, "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Measure]), "Measure", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute", "Date"}}) in #"Renamed Columns"
You can do this with Pivot and Unpoint options given in the power BI.
Please refer the below Power BI file and upload sheets and go through the steps i have taken to reach to the final outcome. I have included the code aswell
https://www.dropbox.com/s/zksrv5a91lgaq1o/Bi%20Help.xlsx?dl=0
https://www.dropbox.com/s/il61b48ojsqo6a3/BI%20Help.pbix?dl=0
let
Source = Excel.Workbook(File.Contents("C:\Users\Dilumd\OneDrive - Brandix Lanka Pvt Ltd (1)\Brandix Data\PowerBi\Help\Bi Help.xlsx"), null, true),
Table_2_Sheet = Source{[Item="Table_2",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table_2_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Promoted Headers1" = Table.PromoteHeaders(#"Promoted Headers", [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers1",{{"Units", "Oct 16-Units"}, {"Value", "Oct 16-Value"}, {"Units_1", "Nov 16-Units"}, {"Value_2", "Nov 16-Value"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"COUNTRY", type text}, {"PRODUCT CATEGORY", type text}, {"PRODUCT GROUP", type text}, {"PRODUCT", type text}, {"PRODUCT ID", type text}, {"Oct 16-Units", Int64.Type}, {"Oct 16-Value", Int64.Type}, {"Nov 16-Units", Int64.Type}, {"Nov 16-Value", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"COUNTRY", "PRODUCT CATEGORY", "PRODUCT GROUP", "PRODUCT", "PRODUCT ID"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.None, false), {"Attribute.1", "Attribute.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type date}, {"Attribute.2", type text}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Attribute.1", "Date"}}),
#"Pivoted Column" = Table.Pivot(#"Renamed Columns1", List.Distinct(#"Renamed Columns1"[Attribute.2]), "Attribute.2", "Value")
in
#"Pivoted Column"let
Source = Excel.Workbook(File.Contents("C:\Users\Dilumd\OneDrive - Brandix Lanka Pvt Ltd (1)\Brandix Data\PowerBi\Help\Bi Help.xlsx"), null, true),
Table_1_Sheet = Source{[Item="Table_1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table_1_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type text}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"COUNTRY", type text}, {"PRODUCT CATEGORY", type text}, {"PRODUCT", type text}, {"CODE", type text}, {"Measure", type text}, {"10/1/2016", Int64.Type}, {"11/1/2016", Int64.Type}, {"12/1/2016", Int64.Type}, {"1/1/2017", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"COUNTRY", "PRODUCT CATEGORY", "PRODUCT", "CODE", "Measure"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Measure]), "Measure", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute", "Date"}})
in
#"Renamed Columns"
Many thanks Dilmud! :smileyhappy:
Data required a lot of cleaning, but transformation went well for the first table. Learned a lot. Will let you know about second table.
I've created a function that makes tasks like this very easy:
