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

Mariusz's avatar
Mariusz
Community Champion
5 years ago

Hi pbix1 

 

Can you create a sample data and add the results that you expecting?

 

 

Best Regards,
Mariusz

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

Please feel free to connect with me.
LinkedIn

 

pbix1's avatar
pbix1
Resolver II
5 years ago

 Hi Mariusz

 

I'm trying to create a moving average that resets at each change of 'Key'. Where it produces an error because there are not enough periods to create it, this can show as zero. I have based this on a 2 period rolling average but would like the flexibility to change the number of periods.

 

Key                    Index                Counter            MAA

Route 01           1                       12                      0
Route 01           2                        6                       9
Route 01           3                        8                       7
Route 02           4                        4                       0
Route 02           5                        16                     10
Route 02           6                        10                     13

 

Does this help? Many thanks.

 

Neil

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    Here is a column expression that gets your desired results (although it does not give zero for the first value of a Key).

     

    MAA =
    VAR thisindex = Counter[Index]
    VAR lasttwoindex =
        TOPN (
            2,
            CALCULATETABLE (
                VALUES ( Counter[Index] ),
                ALLEXCEPT (
                    Counter,
                    Counter[Key]
                ),
                Counter[Index] <= thisindex
            ),
            Counter[Index], DESC
        )
    RETURN
        CALCULATE (
            AVERAGE ( Counter[Counter] ),
            ALLEXCEPT (
                Counter,
                Counter[Key]
            ),
            lasttwoindex
        )

     

    Regards,

    Pat

    • 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