PowerQuery: avg last three dates values

[illusionek]

Hello

I am looking for a way in PowerQuery to do an average of last three values (based on the week) for each product. The challenge I have is that dates are not always consecutive and there is also scenario where I will have less than three values available.

I provided sample of data in below table and then expected results in the second table. The desire outcome is to get avg of last three dates for each product and if there are less than three values available in total then avg just whats available.

Product	Week	Sold Qty
BC0002	10/10/2021	21000​
BC0006	03/10/2021	90000​
BC0006	17/10/2021	1000​
BC0006	31/10/2021	0​
BC0006	07/11/2021	82000​
BC0022	03/10/2021	81000​
BC0022	10/10/2021	80000​
BC0022	17/10/2021	100000​
BC0022	24/10/2021	59000​
BC0022	31/10/2021	81000​
BC0022	07/11/2021	33000​
BC0086	03/10/2021	82090​
BC0086	07/11/2021	57600​
BC0109	03/10/2021	111725​
BC0109	10/10/2021	105000​
BC0109	17/10/2021	185950​
BC0109	24/10/2021	158975​
BC0109	31/10/2021	161325​
BC0109	07/11/2021	219000​

BC0002	21000​
BC0006	27666.67​
BC0022	57666.67​
BC0086	69845​
BC0109	179766.7​

 

[JGordon11]

Change the first line to reflect the table name you are using, and try:

let
    tbl1 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    tblType = Table.TransformColumnTypes(tbl1,{{"Sold Qty", Int64.Type}, {"Week", type date}}),
    tblSort = Table.Sort(tblType,{{"Week", Order.Ascending}}),
    tbl2 = Table.Group(tblSort, {"Product"}, {{"All", each _}}),
    tbl3 = Table.AddColumn(tbl2, "Avg", each let trc = Table.RowCount(_[All]) in 
            List.Average(List.Skip(_[All][Sold Qty], if trc<4 then 0 else trc-3))),
    tbl4 = Table.RemoveColumns(Table.Sort(tbl3,{{"Product", Order.Ascending}}),{"All"})
in
    tbl4

 

[lrobbo314]

Another way.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Product"}, {{"Data", each _, type table [Product=text, Week=datetime, Sold Qty=number]}}),
    Avg = Table.TransformColumns(Group,{{"Data", each List.Average(Table.MaxN(_,"Week", 3)[Sold Qty])}})
in
    Avg

 

[shaowu459]

= Table.Group(Source,"Product",{"Avg",each List.Average(Table.MaxN(_,"Week",3)[Sold Qty])})