unpivot with multiple measures

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