Forum Discussion

pbix1's avatar
pbix1
Resolver II
5 years ago
Solved

Grouped Rolling Average

Hi   I have the following m code to create a grouped running average:   try let Group=[Route_DU_Key], Row=[Index] in List.Average(Table.SelectRows(#"Added Index", each [Index]<=Row and [Route_D...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    Hi pbix1 

    This M code creates a rolling average based on the sample data you supplied.  It does not need an Index column to work.

    It will work with any number of 'Keys'.

    You can download a sample PBIX file here

     

    let

GRAList = (values as list, grouping as list) as list =>

  let

    TheList = List.Generate
    ( 
        ()=> [ GRT = values{0}, GRA = values{0}, i = 0, j = 1 ],

        each [i] < List.Count(values),

        each try 
                 if grouping{[i]} = grouping{[i] + 1} then [GRT = [GRT] + values{[i]+1} , GRA = GRT/j, i = [i] + 1, j = [j] + 1]

                 else [GRT = values{[i]+1} , GRA = GRT/j, i = [i] + 1, j = 1]

             otherwise [i = [i] + 1],

        each [GRA]
    )
in
    TheList,


    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsovLUlVMDBU0lECYyMgYaAUq4MiAxI0A2JLdAljILYAYnNkCZBqEyg2QJcwBVkCMssQQwoqCiSMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Index = _t, Counter = _t, MAA = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Counter", Int64.Type}, {"MAA", Int64.Type}}),

    BufferedValues = List.Buffer(#"Changed Type"[Counter]),
    BufferedGrouping = List.Buffer(#"Changed Type"[Key]),

    GroupedRunningAverage = Table.FromList(GRAList(BufferedValues, BufferedGrouping), Splitter.SplitByNothing(), {"GRA"}, null, ExtraValues.Error),
    Columns = List.Combine({Table.ToColumns(#"Changed Type"),Table.ToColumns(GroupedRunningAverage)}),
    #"Converted to Table" = Table.FromColumns(Columns,List.Combine({Table.ColumnNames(#"Changed Type"),{"Rolling Avg"}})),
    #"Reordered Columns" = Table.ReorderColumns(#"Converted to Table",{"Key", "Index", "Counter", "Rolling Avg", "MAA"})
in
    #"Reordered Columns"

     

    Regards

    Phil

    If I answered your question please mark my post as the solution.
    If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

  • pbix1's avatar
    pbix1
    5 years ago

    Hi. Thanks for all the responses.  Phil, I tried your method but I could only see how it worked for a rolling average which I had already had a method for. I was after a rolling average by group. I found this method, which after a bit of adapting, worked for what I needed. Link below in case it helps anyone else:

     

    https://stackoverflow.com/questions/55337952/power-query-m-language-50-day-moving-average

     

    Finally! found a solution. First, apply Index by product see this post for further details Then index again without criteria (index all rows) Then, apply below code
    = Table.AddColumn(#"Previous Step", "Volume SMA(50)", each if [Index_byProduct] >= 50 then List.Average(List.Range(#"Previous Step"[Volume], ([Index_All]-50),50)) else 0),
    For large dataset, Table.Buffer function is recommended after index-expand step to improve PQ calculation speed

pbix1's avatar
pbix1
Resolver II
5 years ago

Hi Pat

 

Thanks for your reply. That looks like DAX though. I'm after a solution in m code to use in Power Query. I'm not really comfortable with DAX, and as an MS Access user I find Power Query a lot easier to understand.

 

Thanks - Neil 

Mariusz's avatar
Mariusz
Community Champion
5 years ago

Hi pbix1 

 

Please see the below, however, DAX calculation would probably be a better solution for this sort of manipulations.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsovLUlVMDBU0lECYyMgYaAUq4MiAxI0A2JLdAljILYAYnNkCZBqEyg2QJcwBVkCMssQQwoqCiSMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Index = _t, Counter = _t, MAA = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", type text}, {"Index", Int64.Type}, {"Counter", Int64.Type}, {"MAA", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Key"}, {{"rows", each _, type table [Key=nullable text, Index=nullable number, Counter=nullable number, MAA=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Index", each [rows][Index], type list),
    #"Expanded Index" = Table.ExpandListColumn(#"Added Custom", "Index"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Index",{{"Index", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each 
        let 
            lesThanIndex = Table.SelectRows( [rows], (a) =>  a[Index] <= [Index] ),
            sort = Table.Sort(lesThanIndex,{{"Index", Order.Descending}}),
            namberOfPeriods = 2,
            FirstN =  Table.FirstN( sort, namberOfPeriods )[Counter]
        in
            if List.NonNullCount(  FirstN ) = namberOfPeriods then List.Average( FirstN ) else 0 
    )
in
    #"Added Custom1"

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn