超级查询-M语言:多个列的分组依据总和 [英] Power Query - M Language: Sum with Group By for multiple columns
问题描述
我正在为执行以下操作的导入的csv文件编写DataTransform:
I'm looking to write a DataTransform for an imported csv file which performs the following:
- GroupBy:状态
- 操作:汇总所有列
输入数据如下:
我要查找的输出将在每个州显示一行,每个日期显示一列,以及该日期的总和.只需使用Table.Group和List.Sum,我就可以在特定日期使用它:
The output I'm looking for would show a row each State, a column for each date, and the sum for that date. Just using the Table.Group and List.Sum, I'm able to get this for specific dates:
= Table.Group(#"Change Dates to Num", {"Province_State"}, {{"4/19/20", each List.Sum([#"4/19/20"]), type number}, {"4/20/20", each List.Sum([#"4/20/20"]), type number}})
虽然我不知道输入中有多少个日期,所以我正在寻找它以便对N列执行此操作:
I don't know how many dates are in the input though so I'm looking for this to do this for N columns:
= Table.Group(#"Change Dates to Num", {"Province_State"}, List.Transform(List.RemoveFirstN(Table.ColumnNames(#"Promoted Headers"),4), (DateList) => {DateList,each List.Sum(Table.Column(#"Change Dates to Num",DateList)), type number}))
以上内容为我提供了正确的列数和行数,但List.Sum不正确.
The above gives me the correct number of columns and rows but the List.Sum is not right.
感谢您的帮助.
推荐答案
单击同时选择 County 和 County_Region ,然后右键单击删除列..."
click select both County and County_Region then right click Remove columns...
仅离开省/州和所有日期列
点击选择省份,然后右键单击取消透视其他列
click select Province_state and then right click Unpivot other columns
现在您只有三列,省州,属性和值
now you just have three columns, Province_state, Attribute and Value
单击同时选择省/州和属性,然后右键单击分组依据...
click select both Province_state and Attribute then right click Group By...
保留设置为[x]基本
对于操作,请使用总和;对于列,请使用值
For operation use Sum and for Column use Value
点击确定
单击选择属性,然后选择主页...枢轴列".
click select Attribute then Home...Pivot Column..
对于Values_Column,选择您创建的新列的名称,例如SUM
for Values_Column choose the name of the new column you created, like SUM
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Removed Columns" = Table.RemoveColumns(Source,{"County", "Country_Regio"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Province_State"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Province_State", "Attribute"}, {{"Sum", each List.Sum([Value]), type number}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Attribute]), "Attribute", "Sum", List.Sum)
in #"Pivoted Column"
这篇关于超级查询-M语言:多个列的分组依据总和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!