Power Query将指定列转置为表头并保留分组的实现方法

问题背景 #

已经在Power Query里按New_Vendor和MONTH分组，算出了每个供应商每月的总支出。现在要把MONTH列转成表头，让供应商行和月份列交叉的地方显示对应月度的支出金额。

当前数据 #

期望数据 #

遇到的问题 #

试过透视（Pivot）和逆透视（Unpivot）列，虽然成功把MONTH设成了表头，但表头下面只显示1或0，金额没正确填到对应的交叉位置。

透视前的M代码 #

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"

解决方案 #

手动操作步骤 #

1. 正确透视列

   • 选中分组后的表格里的New_Vendor列（作为保留的行标识）
   • 选中MONTH列，点击「转换」选项卡的「透视列」
   • 在弹窗里：
     • 值列选Amount
     • 聚合函数一定要选「求和」（选“不要聚合”就会出现1/0的问题）

2. 空值替换为0

   • 选中所有月份列（除了New_Vendor）
   • 点击「转换」选项卡的「替换值」
   • 查找值留空，替换值输入0，确认即可

整合后的最终M代码 #

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}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[MONTH]), "MONTH", "Amount", List.Sum),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue, List.RemoveFirstN(Table.ColumnNames(#"Pivoted Column"),1))
in
    #"Replaced Value"

代码说明 #

• Table.Pivot里指定List.Sum作为聚合函数，确保每个交叉点的金额是正确的求和结果，避免出现1/0的错误
• Table.ReplaceValue会自动把所有月份列里的空值替换成0，刚好匹配期望的数据格式

内容的提问来源于stack exchange，提问作者CostcoGridlock