Unpivot question

Here is one way to do it, with the Shift and Fill Down approach.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("5ZZdC4IwFIb/SnitkB8EXQ7SSDACCQrxYpmEYBaWgf8+pzbnnLMvKBQ8nNeXw3Hw7Mw5jjD34yOMUkEUeI8rOoIRXDwYjrY+jDNLnkjL002Sp4We+R7WJowkZVxow99hbcEYa3COCT/F2kwiQodVfXJAGq0DWJmlIjsLhRuoXLd0QJgqFXUv778ibF5/uSjf4Ndm9/qX8vJZWarVOlVZxq3ycpuxdo3ISpmLxYwy9Vyg6kW0D+BgyLOihXxXUOT5nf+S/BWG/Z959ox/izxv1lvI06R/QH5tg95zf2PiWduj46x/ceLprdHBnd4eH3MH+iC5N3/iTO4KlSvNPelbuj+48y8bDe40c7Xhue4d", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable 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, Column11 = _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}, {"Column11", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> " ")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Country", each if [Column2] = "" then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Country"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each ([Column2] <> "")),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows1", [PromoteAllScalars=true]),
    #"Filtered Rows2" = Table.SelectRows(#"Promoted Headers", each ([Fiscal Year] <> "Fiscal Year")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows2",{{List.Last(Table.ColumnNames(#"Filtered Rows2")), "Country"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Fiscal Year", "Country"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Country", type text}, {"Attribute", type date}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Fiscal Year", "Region"}})
in
    #"Renamed Columns1"

Here is what the output looks like using your example data (I wasn't sure what to call your first column so I went with "Region".

Pat

Hi Anonymous ,

In the POwer Query Editor please mark the column with the areas and click the following:

Best regards,

Mikelytics

-------------------------------------------------------------------------------

Did I answer your request? Please mark my post as solution.

Appreciate your Kudos.