超级查询-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屋！