大佬教程收集整理的这篇文章主要介绍了如何在 Power Query 中对 N 列求和,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
我的数据每个月都会更新,所以我正在尝试创建一个电源查询表,该表将显示我创建的旋转 (N) 列的总和,但我似乎无法弄清楚如何在电源中执行此操作查询。
我目前有这个代码:
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]),"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",//Sum each row of "colToSum"
@R_769_10586@ls = Table.Addcolumn(IDX,"Sum",each List.Sum(
Record.ToList(
Table.SELEctcolumns(IDX,colToSum){[Index]})
),Currency.TypE),#"Removed columns1" = Table.Removecolumns(@R_769_10586@ls,{"Index"})
in
#"Removed columns1"
,
可以分组,旋转后合并到表格中
#"Grouped Rows" = Table.Group(#"Changed Type",{"Atribute"},{{"Sum",each List.Sum([Value]),type number}}),#"Pivoted column" = Table.Pivot(Table.TransformcolumnTypes(#"Changed Type",{{"Month Year",type text}},"en-US"),List.DisTinct(Table.TransformcolumnTypes(#"Changed Type","en-US")[#"Month Year"]),"Month Year",#"Merged Queries" = Table.nestedJoin(#"Pivoted column",#"Grouped Rows","table2",JoinKind.LeftOuter),#"Expanded Table" = Table.ExpandTablecolumn(#"Merged Queries",{"Sum"},{"Sum"})
in #"Expanded Table"
或者您可以分组,将其添加到表格中,然后旋转组合的新集合
#"Grouped Rows" = Table.Group(#"Changed Type",{{"Value",#"Added Custom" = Table.Addcolumn(#"Grouped Rows",each "Sum"),#"Reordered columns" = Table.Reordercolumns(#"Added Custom",{"Month Year","Atribute","Value"}),combined = #"Reordered columns" & #"Changed Type",#"Pivoted column" = Table.Pivot(Table.TransformcolumnTypes(combined,List.DisTinct(Table.TransformcolumnTypes(combined,List.Sum)
in #"Pivoted column"
以上是大佬教程为你收集整理的如何在 Power Query 中对 N 列求和全部内容,希望文章能够帮你解决如何在 Power Query 中对 N 列求和所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。