Unpivot two rows title

[kapucino]

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

[dilumd · Accepted solution]

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"

[kapucino]

Many thanks Dilmud! 

Data required a lot of cleaning, but transformation  went well for the first table. Learned a lot. Will let you know about second table.

[v-ljerr-msft]

Hi @kapucino,

Have you tried the solution provided by @dilumd above? Does it work in your scenario? If it works, could you accept it as solution to close this thread? 

Regards

[ImkeF]

I've created a function that makes tasks like this very easy:

http://www.thebiccountant.com/2017/06/19/unpivot-by-number-of-columns-and-rows-in-powerbi-and-powerq...