30d Average - Power Query

[Julien]

Hi everyone

I'm trying to calculate the 30d average for turbidity data for a number of sampling locations. In other words for location X on a given day I'd like the average of any values collected after a point in time 30 days before. OK. So in simplified form something like below.

If 30d haven't elapsed yet (from start of sampling) on a given day, no average is calculated.
Let's assume if sampling hasn't taken place at a given location on a given day, there won't be a zero for that location

So I'm thinking it's some combination of the partition technique with an index for the days elapsed since start, instead of index by location (i.e. product in the linked example) ... I just can't figure it out. If this was excel, I'd do Averageifs, with conditions that the [location] = LocX, [date] > [Date] - 30. I think you all know what I mean there.

Any help appreciated!

Location	Date	TSS	30d Avg
A	1 Jan	3	can't calc yet
A	2 Jan	4	can't calc yet
A	...	...
A	15 Feb	5.4	calc val
B	1 Jan	2	can't calc yet
B	2 Jan	3	can't calc yet
B	...	...
B	15 Feb	5	val

 

[cyborgski]

Probably not the most efficient method but the attached code should work.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"TSS", Int64.Type}}),
    #"Extracted Date" = Table.TransformColumns(#"Changed Type",{{"Date", DateTime.Date, type date}}),
    #"Grouped Rows" = Table.Group(#"Extracted Date", {"Location"},{ {"Date", each [Date], type list}, {"AllRows", each _, type table}}),
    #"Expanded AllDates" = Table.ExpandListColumn(#"Grouped Rows", "Date"),
    AddFiltered = Table.AddColumn(#"Expanded AllDates", "FilteredRows", each List.LastN(Table.SelectRows([AllRows],(intern) => intern[Date] <= [Date])[TSS],30)),
    AddAverage = Table.AddColumn(AddFiltered, "30d Average", each if List.Count([FilteredRows]) < 30 then null else 
List.Average([FilteredRows])),
    #"Removed Other Columns" = Table.SelectColumns(AddAverage,{"Location", "Date", "30d Average"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Date", type date}})
in
    #"Changed Type1"

 

[Julien]

Hi cyborgksi - thanks a lot for your reply! Works great.