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"