Power Query - M Language: Sum with Group By for multiple columns

0
votes

I'm looking to write a DataTransform for an imported csv file which performs the following:

  • GroupBy: State
  • Action: Sums all columns

The Input data looks like: enter image description here

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}})

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}))

The above gives me the correct number of columns and rows but the List.Sum is not right.

Thanks for the help.

1
excelpowerquerym

1 Answers

2
votes

click select both County and County_Region then right click Remove columns...

that just leaves Province_state and all the date 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...

Leave set to [x] Basic

For operation use Sum and for Column use Value

Click ok

click select Attribute then Home...Pivot Column..

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"