Unpivoting data with multiple rows

hi qsong 

You could try these steps as below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRCnZ29XMM8vQHMh2dgYSbsxGYNAaSAT44RGN1opWc/X1Cff0MgQIQFkiBrz8mERnigoUEmZAIZFcAsQFeDFKZBGRVEqUyGciqIqwyFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Filtered Rows" = Table.SelectRows(#"Transposed Table", each ([Column1] <> "")),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"SCENARIO", type text}, {"COLUMN2", type text}, {"x", Int64.Type}, {"y", Int64.Type}, {"z", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"SCENARIO", "COLUMN2"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[COLUMN2]), "COLUMN2", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute", "COLUMN2"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "COLUMN1", each if [COLUMN2] = "x" then "a" else if [COLUMN2] = "y" then "b" else "c"),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Conditional Column",{"COLUMN1", "COLUMN2", "SCENARIO", "MO", "YTD"})
in
    #"Reordered Columns"

Result:

and here is sample pbix file, please try it.

Regards,

Lin