As I mentioned in your previous similar question, you can
- Group by material
- then apply the same algorithm to each group
To ***apply the same algorithm***, one simple method is to
- convert the previous code into a function
- call that function from the `Table.Group` aggregation section
- and a few changes are made in order to account for the extra column
***Source Data***<br>
[![enter image description here][1]][1]
***Function M Code***<br>
*Add as a blank query*<br>
*Rename `fnRT_AP`*
```
//rename fnRT_AP
(tbl as table)=>
let
#"Add Running Total Column" =
Table.FromColumns(
Table.ToColumns(tbl) &
{List.Generate(
()=>[rt=tbl[Qty]{0}, idx=0],
each [idx] < Table.RowCount(tbl),
each [rt = [rt] + tbl[Qty]{[idx]+1}, idx=[idx]+1],
each [rt])},
type table[Date=date, Material=text, Type=text, In Price=Currency.Type, Qty=Int64.Type, Running Total=Int64.Type]),
#"Add Avg Cost Column" =
Table.FromColumns(
Table.ToColumns(#"Add Running Total Column") &
{List.Generate(
()=>[cst=if #"Add Running Total Column"[Type]{0}="In" then #"Add Running Total Column"[In Price]{0} else null, idx=0],
each [idx] < Table.RowCount(#"Add Running Total Column"),
each [cst=if #"Add Running Total Column"[Type]{[idx]+1}="Out" then [cst]
else ((if [cst]=null then 0 else [cst]) * #"Add Running Total Column"[Running Total]{[idx]} +
#"Add Running Total Column"[In Price]{[idx]+1} * #"Add Running Total Column"[Qty]{[idx]+1})
/ #"Add Running Total Column"[Running Total]{[idx]+1} , idx=[idx]+1],
each [cst])},
type table[Date=date, Material=text, Type=text, In Price=Currency.Type, Qty=Int64.Type, Running Total=Int64.Type, Avg Cost=Currency.Type])
in
#"Add Avg Cost Column"
```
***Main Code***
```
let
//Change next line to reflect your actual data source
Source = Excel.CurrentWorkbook(){[Name="Table11"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Material", type text}, {"Type", type text},
{"In Price", Currency.Type}, {"Qty", Int64.Type}}),
#"Capitalized Each Word" = Table.TransformColumns(#"Changed Type",{{"Type", Text.Proper, type text}}),
#"Grouped Rows" = Table.Group(#"Capitalized Each Word", {"Material"}, {
{"rtap", each fnRT_AP(_),
type table[Date=date, Material=text, Type=text, In Price=Currency.Type, Qty=Int64.Type, Running Total=Int64.Type, Avg Cost=Currency.Type]}
}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Material"}),
#"Expanded rtap" = Table.ExpandTableColumn(#"Removed Columns", "rtap", {"Date", "Material", "Type", "In Price", "Qty", "Running Total", "Avg Cost"})
in
#"Expanded rtap"
```
***Results***<br>
[![enter image description here][2]][2]
[1]: https://i.stack.imgur.com/P6eJ5.png
[2]: https://i.stack.imgur.com/qjfzX.png
You can use the `List.Generate` function to generate both the Running Total Column as well as the Weighted Average Cost column.
***Starting with:***<br>
[![enter image description here][1]][1]
***M Code***
```
let
//Change next line to reflect your actual data source
Source = Excel.CurrentWorkbook(){[Name="Table11"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Type", type text},
{"In Price", Currency.Type}, {"Qty", Int64.Type}}),
#"Capitalized Each Word" = Table.TransformColumns(#"Changed Type",{{"Type", Text.Proper, type text}}),
#"Add Running Total Column" =
Table.FromColumns(
Table.ToColumns(#"Capitalized Each Word") &
{List.Generate(
()=>[rt=#"Capitalized Each Word"[Qty]{0}, idx=0],
each [idx] < Table.RowCount(#"Capitalized Each Word"),
each [rt = [rt] + #"Capitalized Each Word"[Qty]{[idx]+1}, idx=[idx]+1],
each [rt])},
type table[Date=date, Type=text, In Price=Currency.Type, Qty=Int64.Type, Running Total=Int64.Type]),
#"Add Avg Cost Column" =
Table.FromColumns(
Table.ToColumns(#"Add Running Total Column") &
{List.Generate(
()=>[cst=if #"Add Running Total Column"[Type]{0}="In" then #"Add Running Total Column"[In Price]{0} else null, idx=0],
each [idx] < Table.RowCount(#"Add Running Total Column"),
each [cst=if #"Add Running Total Column"[Type]{[idx]+1}="Out" then [cst]
else ((if [cst]=null then 0 else [cst]) * #"Add Running Total Column"[Running Total]{[idx]} +
#"Add Running Total Column"[In Price]{[idx]+1} * #"Add Running Total Column"[Qty]{[idx]+1})
/ #"Add Running Total Column"[Running Total]{[idx]+1} , idx=[idx]+1],
each [cst])},
type table[Date=date, Type=text, In Price=Currency.Type, Qty=Int64.Type, Running Total=Int64.Type, Avg Cost=Currency.Type])
in
#"Add Avg Cost Column"
```
***results***<br>
[![enter image description here][2]][2]
[1]: https://i.stack.imgur.com/fxAqV.png
[2]: https://i.stack.imgur.com/sHXqq.png
