unpivot with multiple measures

[nanma94]

I know the tricks of unpivotting to flatten out an excel matrix to a table. But I have multiple measures (revenue, and quantity) that I want to separate into different measure columns (see below). 

How to do this? Thanks so much. 

NM

[ChandeepChhabra · Accepted solution]

Hi @nanma94,

Here is the code that you can use. I have assumed that the years stay the same for Revenue and Qty

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", type any}, {"Column10", Int64.Type}, {"Column11", Int64.Type}, {"Column12", Int64.Type}, {"Column13", Int64.Type}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column1", "Continent"}, {"Column2", "Country"}, {"Year", "City/State"}}),
    #"Filled Down" = Table.FillDown(#"Renamed Columns",{"Continent", "Country"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filled Down", {"City/State", "Country", "Continent"}, "Year", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Type", each if Text.Contains([Year], "_") then "Qty" else "Rev" ),
    #"Extracted First Characters" = Table.TransformColumns(#"Added Conditional Column", {{"Year", each Text.Start(_, 4), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Extracted First Characters", List.Distinct(#"Extracted First Characters"[Type]), "Type", "Value")
in
    #"Pivoted Column"

Here is the snapshot of the result

Download the excel file from here

Thanks

[Alex_Cepeda]

Hello Chandeep,  i can't download the Excel file.  Is it possible you check download permissions?

Thanks,

Alex-

[ChandeepChhabra]

Hi @Alex_Cepeda, The files don't need any permission to download

Excel file - When you open it in the browser, please click on File and choose Save As

Power BI file

Hope it helps

[Alex_Cepeda]

Hello ChandeepChhabra , i can't download the excel file.  Could you please check download permissions?

Thanks!

Alex-

[nanma94]

ChandeepChhabra, appreciate you looking into this!

Do you have the pbix you can share, so I can track the steps in GUI?

Thanks again. 

NM

[ChandeepChhabra]

@nanma94 Here is the link to download pbi file

Thanks