Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to summarize columns using Power Query Editor NOT DAX

Hi all,  I need a query that summarizes and generates a row for every date and all 99 MSOAs (geographical) area from my source table. For context I have a large dataset of records with a date and ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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"

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous 

 

In M, paste it in Advanced Editor. If you want to use DAX measure, I believe you can try CROSSJOIN

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNDJR0l32B/RwUQw1ApVgebnBGQYQyRM0KXM8YjB2IYQeSMsekzUYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, MSOA = _t, Case = _t]),
    Date = List.Distinct( Source[Date]),
    MSOA = Table.FromList( List.Distinct(Source[MSOA])),
    #"Renamed Columns" = Table.RenameColumns(MSOA,{{"Column1", "MSOA"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Date", each Date),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Date", {"MSOA", "Date"}, Source, {"MSOA", "Date"}, "Expanded Date", JoinKind.LeftOuter),
    #"Expanded Expanded Date" = Table.ExpandTableColumn(#"Merged Queries", "Expanded Date", {"Case"}, {"Case"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Expanded Date",null,"0",Replacer.ReplaceValue,{"Case"})
in
    #"Replaced Value"