PowerQuery Pivot Multiple Columns/Categories

Hi, greenguy2012 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

You may create a new query with the following m codes in 'Advanced Editor'.

let
    #"Pivoted Column 1" = Table.Pivot(Table, List.Distinct(Table[Category]), "Category", "Sales Month"),
    Custom1 = Table.Group(#"Pivoted Column 1",{"Region","Sales Rep"},{{"Retail Month",each List.First(List.RemoveNulls([Retail]))},{"Channel Month",each List.First(List.RemoveNulls([Channel]))}}),
    #"Pivoted Column 2" = Table.Pivot(Table, List.Distinct(Table[Category]), "Category", "Sales Amount", List.Sum),
    Custom2 = Table.Group(#"Pivoted Column 2",{"Region","Sales Rep"},{{"Retail Sales Amount",each List.First(List.RemoveNulls([Retail]))},{"Channel Sales Amount",each List.First(List.RemoveNulls([Channel]))}}),
    Res = Table.NestedJoin(Custom1, {"Region", "Sales Rep"}, Custom2, {"Region", "Sales Rep"}, "Res Table", JoinKind.Inner),
    #"Expanded Res Table" = Table.ExpandTableColumn(Res, "Res Table", {"Retail Sales Amount", "Channel Sales Amount"}, {"Retail Sales Amount", "Channel Sales Amount"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Res Table",{{"Retail Month", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Region", "Sales Rep", "Retail Month", "Retail Sales Amount", "Channel Month", "Channel Sales Amount"})
in
    #"Reordered Columns"

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.