Forum Discussion
How to summarize columns using Power Query Editor NOT DAX
Hi Anonymous
Ok, it is all about the dates, so let's extract the min and max date in the original data, then generate every single day in between, have a try
let Source = Table.Group(Table.SelectColumns(Pos_Case_DPH_2,{"Preferred MSOA Name", "Specimen Date", "Count"}), {"Preferred MSOA Name", "Specimen Date"}, {{"Count", each List.Sum([Count]), type nullable number}}), Date = List.Transform( { Number.From(List.Min(Source[Specimen Date]))..Number.From(List.Max(Source[Specimen Date]))}, Date.From), MSOA = Table.FromList( List.Distinct(Source[Preferred MSOA Name])), #"Renamed Columns" = Table.RenameColumns(MSOA,{{"Column1", "Preferred MSOA Name"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Specimen Date", each Date), #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Specimen Date"), #"Merged Queries" = Table.NestedJoin(#"Expanded Date", {"Preferred MSOA Name", "Specimen Date"}, Source, {"Preferred MSOA Name", "Specimen Date"}, "Expanded Date", JoinKind.LeftOuter), #"Expanded Expanded Date" = Table.ExpandTableColumn(#"Merged Queries", "Expanded Date", {"Count"}, {"Count"}), #"Replaced Value" = Table.ReplaceValue(#"Expanded Expanded Date",null,"0",Replacer.ReplaceValue,{"Count"}) in #"Replaced Value"
Hi Anonymous
Understood, so sum up the Count first, try it
let
Source = Table.Group(Table.SelectColumns(Pos_Case_DPH_2,{"Preferred MSOA Name", "Specimen Date", "Count"}), {"Preferred MSOA Name", "Specimen Date"}, {{"Count", each List.Sum([Count]), type nullable number}}),
Date = List.Distinct( Source[Specimen Date]),
MSOA = Table.FromList( List.Distinct(Source[Preferred MSOA Name])),
#"Renamed Columns" = Table.RenameColumns(MSOA,{{"Column1", "Preferred MSOA Name"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Specimen Date", each Date),
#"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Specimen Date"),
#"Merged Queries" = Table.NestedJoin(#"Expanded Date", {"Preferred MSOA Name", "Specimen Date"}, Source, {"Preferred MSOA Name", "Specimen Date"}, "Expanded Date", JoinKind.LeftOuter),
#"Expanded Expanded Date" = Table.ExpandTableColumn(#"Merged Queries", "Expanded Date", {"Count"}, {"Count"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Expanded Date",null,"0",Replacer.ReplaceValue,{"Count"})
in
#"Replaced Value"Hi Anonymous that has worked to get the count column thank you. The only thing missing is still the lack of rows for every single date. I've taken a snip of the table when sorted in acending order by date. You can see there are rows for the 3rd March 2020 and the 5th March 2020 but the query hasn't generated rows for the 4th March and other dates when there isn't a case in any single MSOA on that date. The 4th March ideally should have 99 rows that have a count of 0. Is that possible? Thanks.
Hi Anonymous
Ok, it is all about the dates, so let's extract the min and max date in the original data, then generate every single day in between, have a try
let Source = Table.Group(Table.SelectColumns(Pos_Case_DPH_2,{"Preferred MSOA Name", "Specimen Date", "Count"}), {"Preferred MSOA Name", "Specimen Date"}, {{"Count", each List.Sum([Count]), type nullable number}}), Date = List.Transform( { Number.From(List.Min(Source[Specimen Date]))..Number.From(List.Max(Source[Specimen Date]))}, Date.From), MSOA = Table.FromList( List.Distinct(Source[Preferred MSOA Name])), #"Renamed Columns" = Table.RenameColumns(MSOA,{{"Column1", "Preferred MSOA Name"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Specimen Date", each Date), #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Specimen Date"), #"Merged Queries" = Table.NestedJoin(#"Expanded Date", {"Preferred MSOA Name", "Specimen Date"}, Source, {"Preferred MSOA Name", "Specimen Date"}, "Expanded Date", JoinKind.LeftOuter), #"Expanded Expanded Date" = Table.ExpandTableColumn(#"Merged Queries", "Expanded Date", {"Count"}, {"Count"}), #"Replaced Value" = Table.ReplaceValue(#"Expanded Expanded Date",null,"0",Replacer.ReplaceValue,{"Count"}) in #"Replaced Value"Perfect, this worked exactly as I wanted now. The only issue is speed. My system takes forever to run the query still. Wondering if this is the VPN I work on as I have 16gb ram to play with. I've noticed the CPU usage heading up to 85-90% when trying to run it. Probably need to contact IT.
Thanks again though!Anonymous
have you, by chance, tried the solution proposed by me?
Since it uses GUI functions it should be pretty fast, if it does what you ask (in fact, I don't remember what the problem was anymore)