Forum Discussion

jcastr02's avatar
jcastr02
Post Prodigy
2 years ago
Solved

calculating by distinct date

I am trying to add the totals from each store and then divide by the unique # of dates provided (as some stores will not have all the dates).  How could I achieve this in within Power Query?  I under...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    Change the Source line to reflect your actual data source:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Store Number", Int64.Type}, {"Totals", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Store Number"}, {
        {"Average Sales per Day", each List.Sum([Totals]) / List.Count(List.Distinct([Date])), type number}})
in
    #"Grouped Rows"

     

     

     

ronrsnfld's avatar
ronrsnfld
Super User
2 years ago

Change the Source line to reflect your actual data source:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Store Number", Int64.Type}, {"Totals", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Store Number"}, {
        {"Average Sales per Day", each List.Sum([Totals]) / List.Count(List.Distinct([Date])), type number}})
in
    #"Grouped Rows"