Rolling average on calculated table

[Anonymous]

This is the output I’m looking for. Each value in the “rolling 3-row sum window” is the sum of three rows in the adjacent column. If there aren’t 3 rows to sum, It should sum the remaining rows in the category.

Category	Value	Rolling 3-row sum window (filtered by category)
red	1	8
red	3	12
red	4	9
red	5	5
blue	5	13
blue	6	8
blue	2	2
green	3	3

[smpa01 · Accepted solution]

I solved it in the following way

let
    Source = Web.Page(Web.Contents("https://community.powerbi.com/t5/Desktop/Rolling-average-on-calculated-table/m-p/648448#M310912")),
    Data0 = Source{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data0, [PromoteAllScalars=true]),
    #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Rolling 3-row sum window (filtered by category)"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Category"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Value", "Index"}, {"Value", "Index"})
in
    #"Expanded Custom"

Table 0 (3)

let
    Source = Web.Page(Web.Contents("https://community.powerbi.com/t5/Desktop/Rolling-average-on-calculated-table/m-p/648448#M310912")),
    Data0 = Source{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data0, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Category", type text}, {"Value", Int64.Type}, {"Rolling 3-row sum window (filtered by category)", Int64.Type}}),
    #"Removed Columns3" = Table.RemoveColumns(#"Changed Type",{"Rolling 3-row sum window (filtered by category)"}),
    #"Grouped Rows2" = Table.Group(#"Removed Columns3", {"Category"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns3",{"Category"},#"Grouped Rows2",{"Category"},"Table 0",JoinKind.LeftOuter),
    #"Expanded Table 0" = Table.ExpandTableColumn(#"Merged Queries", "Table 0", {"Count"}, {"Count"}),
    #"Grouped Rows" = Table.Group(#"Expanded Table 0", {"Category"}, {{"AD", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([AD],"IX",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AD"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Value", "Count", "IX"}, {"Value", "Count", "IX"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"IX", "Start"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Value", Int64.Type}, {"Count", Int64.Type}, {"Start", Int64.Type}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Subtraction", each [Count]-[Start]+1),
    #"Added Custom1" = Table.AddColumn(#"Added Custom2", "Custom", each if [Subtraction]>=3 then {[Start]..[Start]+2} else if [Subtraction]<3 then List.Numbers([Start],[Subtraction]) else null),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Category", "Value", "Start", "Subtraction", "Custom"}),
    #"Expanded Custom1" = Table.ExpandListColumn(#"Removed Other Columns", "Custom"),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Custom1",{"Category", "Custom"},#"Table 0 (3)",{"Category", "Index"},"Table 0 (3)",JoinKind.LeftOuter),
    #"Expanded Table 0 (3)" = Table.ExpandTableColumn(#"Merged Queries1", "Table 0 (3)", {"Value"}, {"Value.1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Table 0 (3)",{{"Value", Order.Ascending}, {"Category", Order.Ascending}, {"Start", Order.Ascending}}),
    #"Grouped Rows1" = Table.Group(#"Sorted Rows", {"Category", "Start"}, {{"AD", each _, type table}}),
    #"Sorted Rows1" = Table.Sort(#"Grouped Rows1",{{"Category", Order.Ascending}, {"Start", Order.Ascending}}),
    #"Added Custom3" = Table.AddColumn(#"Sorted Rows1", "Custom", each let
   Source=[AD],
   Custom_1 = List.Accumulate(Source[Value.1],0,(state,current)=>state+current)
 in
    Custom_1),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom3",{"AD"}),
    Custom1 = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Value", "Rolling 3-row sum window (filtered by category)", "Count", "IX"}, {"Value", "Rolling 3-row sum window (filtered by category)", "Count", "IX"}),
    #"Merged Queries2" = Table.NestedJoin(Custom1,{"Category", "IX"},#"Removed Columns1",{"Category", "Start"},"Custom1",JoinKind.LeftOuter),
    #"Expanded Custom2" = Table.ExpandTableColumn(#"Merged Queries2", "Custom1", {"Custom"}, {"Custom"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded Custom2",{"Rolling 3-row sum window (filtered by category)", "Count", "IX"})
in
    #"Removed Columns2"

Output