如何对Power Query中的N列求和 [英] How to sum N columns in Power Query
本文介绍了如何对Power Query中的N列求和的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的数据每个月都会更新,所以我试图创建一个能查询的表,该表将显示我创建的数据透视(N)列的总和,但是我似乎无法弄清楚该如何执行该操作查询.
My data gets updated every month so I'm trying to create a power query table that would show the sum of the pivoted (N) columns that I created but I can't seem to figure out how to do it in power query.
我目前有以下代码:
推荐答案
- 旋转后:
- 创建要累加的列的列表
- 添加索引列以限制到每一行
- 添加一列,仅汇总该行的列
- 删除索引列
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"
这篇关于如何对Power Query中的N列求和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文