Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |