Results 1 to 3 of 3

Thread: 30d Average - Power Query

  1. #1
    Neophyte Julien's Avatar
    Join Date
    Oct 2019
    Posts
    2
    Articles
    0
    Excel Version
    1

    30d Average - Power Query



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    60
    Articles
    0
    Excel Version
    Office 365
    Probably not the most efficient method but the attached code should work.

    Code:
    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"
    Last edited by cyborgski; 2019-10-25 at 02:48 AM.

  3. #3
    Neophyte Julien's Avatar
    Join Date
    Oct 2019
    Posts
    2
    Articles
    0
    Excel Version
    1
    Hi cyborgksi - thanks a lot for your reply! Works great.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •