need help with M query to unpivot

This is one way to do it.

Remark: lately I have been exploring conversions between lists, records and tables in Power Query.

This may have influenced the solution :smileyhappy:

let
    Source = Excel.CurrentWorkbook(){[Name="RawData"]}[Content],

    // 1. Transpose and pivot right part of the table
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column1", "Column2"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Removed Columns1" = Table.RemoveColumns(#"Transposed Table",{"Column1"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns1", "Index", 0, 1),
    #"Integer Divided" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 4), type number}}),
    #"Merged Columns" = Table.CombineColumns(#"Integer Divided",{"Column3", "Column4", "Column5", "Column6"}, each _, "Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Column2]), "Column2", "Merged"),
    Zipped = Table.CombineColumns(#"Pivoted Column",{"Accounts", "Invoices", "Monthly Billing", "Balance"}, each List.Zip(_),"Zipped"),

    // 2. Fill down first 2 columns:
    #"Removed Top Rows" = Table.Skip(Source,2),
    #"Filled Down1" = Table.FillDown(#"Removed Top Rows",{"Column1"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Filled Down1",{"Column1", "Column2"}),
    #"Merged Columns1" = Table.CombineColumns(#"Removed Other Columns",{"Column1", "Column2"},each Record.FromList(_,{"Type", "Category"}),"TypeCat"),

    // 3. List with clubs:
    Clubs = List.Select(Record.ToList(Source{0}), each _ <> null),

    // 4. Combine Clubs with part1 and expand:
    Custom2 = Table.FromColumns({Clubs,Zipped[Zipped]}),
    #"Expanded Column2" = Table.ExpandListColumn(Custom2, "Column2"),
    
    // 5. Repeat first 2 columns:
    Custom3 = Table.Repeat(#"Merged Columns1",Table.RowCount(#"Expanded Column2")/Table.RowCount(#"Merged Columns1")),

    // 6. Combine all parts and expand:
    Custom4 = Table.FromColumns({Custom3[TypeCat],Table.ToRecords(#"Expanded Column2")}),
    #"Expanded Column1" = Table.ExpandRecordColumn(Custom4, "Column1", {"Type", "Category"}, {"Type", "Category"}),
    #"Expanded Column3" = Table.ExpandRecordColumn(#"Expanded Column1", "Column2", {"Column1", "Column2"}, {"Column1", "Column2.1"}),

    // 7. Create columns from nested lists:
    RecordFromList = Table.TransformColumns(#"Expanded Column3",{{"Column2.1", each Record.FromList(_,{"Accounts", "Invoices", "Monthly Billing", "Balance"})}}),
    #"Expanded Column2.1" = Table.ExpandRecordColumn(RecordFromList, "Column2.1", {"Accounts", "Invoices", "Monthly Billing", "Balance"}, {"Accounts", "Invoices", "Monthly Billing", "Balance"}),

    // 8. Finishing touches.
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column2.1",{{"Column1", "Club"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Type", type text}, {"Category", type text}, {"Club", type text}, {"Accounts", type any}, {"Invoices", Int64.Type}, {"Monthly Billing", Currency.Type}, {"Balance", Currency.Type}})
in
    #"Changed Type"