- 转身后:
- 创建要求和的列列表
- 添加一个索引列以限制每一行
- 添加一列,对仅该行的列求和
- 删除索引列
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Month Yr", Date.Type}, {"Attribute", type text}, {"Value", Currency.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "MonthYear", each Date.ToText([Month Yr],"MMMM yyyy")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Month Yr"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[MonthYear]), "MonthYear", "Value", List.Sum),
//NEW code added after your Pivoted Column line
//Get List of columns to sum
// Assumes this list all columns **except the first** in the Pivot table
// There are other methods of generating this list if this assumption is incorrect
colToSum = List.RemoveFirstN(Table.ColumnNames(#"Pivoted Column"),1),
//Add Index Column
IDX = Table.AddIndexColumn(#"Pivoted Column","Index",0,1),
//Sum each row of "colToSum"
totals = Table.AddColumn(IDX, "Sum", each List.Sum(
Record.ToList(
Table.SelectColumns(IDX,colToSum){[Index]})
), Currency.Type),
#"Removed Columns1" = Table.RemoveColumns(totals,{"Index"})
in
#"Removed Columns1"