calculating by distinct date

[jcastr02]

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 understand I would have to do some grouping by store number, but not sure how to do the calculation by theunique # of dates.  

Current			Desired
Date	Store Number	Totals			Store Number	Avg Total by Unique # of Dates
1/1/2022	18	15			18	60 (***420/7***)
1/2/2022	18	30			20	42.5 (***170/4***)
1/3/2022	18	45			31	30.5 (***61/2***)
1/4/2022	18	60
1/5/2022	18	70
1/6/2022	18	100
1/7/2022	18	100
1/1/2022	20	50
1/2/2022	20	50
1/3/2022	20	50
1/4/2022	20	20
1/1/2022	31	11
1/2/2022	31	50

[ronrsnfld · Accepted solution]

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"