I have Data Grouped in Power Query by Vendor and Month, aggregating total spend. I want to Transpose the Month Column such that the intersection of the Vendor row and the Month Column represent the spend by the given vendor in the given month.

How Data Looks:

How I want it to look:

I have played around with pivoting and unpivoting columns and managed to promote the month column to headers, but the amount column still is its own and all that shows up under the month headers is 1 or a zero:

POWER QUERY PIVOT COLUMN TO ROWS

Here is my M Code before I try to pivot:

let
    Source = Excel.CurrentWorkbook(){[Name="TECH_GL"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", type text}, {"Department", type text}, {"Posted Dt.", type datetime}, {"Doc Dt.", type datetime}, {"Doc", type text}, {"Memo / Description", type text}, {"Dep_Num", Int64.Type}, {"Vendor Name", type text}, {"Vendor", type text}, {"JNL", type text}, {"Curr", type text}, {"Txn Amt", type number}, {"Debit (USD)", type number}, {"Credit (USD)", type number}, {"Total", type number}, {"MONTH", type datetime}, {"New_Account", type text}, {"New_Department", type text}, {"New_Vendor", type text}, {"Notes", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"New_Vendor", "MONTH"}, {{"Amount", each List.Sum([Total]), type nullable number}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"MONTH", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"New_Vendor", Order.Ascending}, {"MONTH", Order.Ascending}})
in
    #"Sorted Rows"

CodePudding user response：

You need transform .. pivot column ...

Click select the MONTH column, transform .. pivot column ... choose Amount as the value column

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"New_Vendor", type text}, {"MONTH", type date}, {"Amount", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"MONTH", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"MONTH", type text}}, "en-US")[MONTH]), "MONTH", "Amount", List.Sum)
in  #"Pivoted Column"

There is no need for the Group step. As long as you just have the vendor, month and amount columns, pivoting will sum it properly

CodePudding user response：

You can use a simple pivot table. Put the vendor in the "rows", the months in the "column" and the amount in the "value".