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

Question

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:

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.

Answer 1

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 Attribute then Transform...Pivot Column.... Choose Value as the Value Column and leave other options as is

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Removed Columns" = Table.RemoveColumns(Source,{"County", "Country_Region"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Province_State"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value", List.Sum)
in  #"Pivoted Column"