cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Resolver I

## 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_DU_Key]=Group)[Counter])

otherwise 0

And the following m code to create a 13 period rolling average:

I want to create a grouped, 13 period rolling average, but am going round and round and keep getting errors when I try to combine them. Can anyone help with this? Thanks.

2 ACCEPTED SOLUTIONS
Super User

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'.

``````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 my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!

Resolver I

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

7 REPLIES 7
Super User

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'.

``````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 my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!

Resolver I

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

Community Champion

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.

Resolver I

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

Employee

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Resolver I

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

Community Champion

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]}}),
#"Expanded Index" = Table.ExpandListColumn(#"Added Custom", "Index"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Index",{{"Index", Int64.Type}}),
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

Best Regards,
Mariusz

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

Please feel free to connect with me.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors