// atulvij.com Power Query Intermediate - Lesson 7: Two header rows: unpivot a Region-over-Month report
// Paste into Home > Advanced Editor of a blank query. Change Folder to where you unzipped the practice files.

let
    Folder = "D:\PQ\",
    Raw = Csv.Document(File.Contents(Folder & "two-row-header.csv"),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
    Transposed = Table.Transpose(Raw),
    FilledRegion = Table.FillDown(Table.ReplaceValue(Transposed, "", null, Replacer.ReplaceValue, {"Column1"}), {"Column1"}),
    Header = Table.CombineColumns(FilledRegion, {"Column1", "Column2"}, each Text.Combine(List.RemoveNulls(_), "|"), "Key"),
    Back = Table.PromoteHeaders(Table.Transpose(Header), [PromoteAllScalars = true]),
    Unpivoted = Table.UnpivotOtherColumns(Back, {"Product"}, "Key", "Qty"),
    Split = Table.SplitColumn(Unpivoted, "Key", Splitter.SplitTextByDelimiter("|"), {"Region", "Month"}),
    Typed = Table.TransformColumnTypes(Split, {{"Qty", Int64.Type}})
in
    Typed