Weighted Average Cost in POWER QUERY

Thank you. 😊

You can try this script in power query

"
let
//Change next line to reflect your actual data source
Source = Excel.CurrentWorkbook(){[Name="PMP_5"]}[Content],

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date},
{"In Price", Currency.Type}, {"Qty", Int64.Type}, {"ItemID", type text}}),

#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}, {"Qty", Order.Descending}}),

#"Grouped Rows" = Table.Group(#"Sorted Rows", {"ItemID"}, {
{"All", each _, type table [Date=date, Type=text, In Price=Currency.Type, Qty=Int64.Type, ItemID=text]}
}),

#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let
currentTable = [All],
#"Add Running Total Column" =
Table.FromColumns(
Table.ToColumns(currentTable) &
{List.Generate(
()=>[rt=currentTable[Qty]{0}, idx=0],
each [idx] < Table.RowCount(currentTable),
each [rt = [rt] + currentTable[Qty]{[idx]+1}, idx=[idx]+1],
each [rt])},
type table[Date=date, In Price=Currency.Type, Qty=Int64.Type, ItemID=text, Running Total=Int64.Type]),

#"Add Avg Cost Column" =
Table.FromColumns(
Table.ToColumns(#"Add Running Total Column") &
{List.Generate(
()=>[cst=if currentTable[Qty]{0}>0 then #"Add Running Total Column"[In Price]{0} else null, idx=0],
each [idx] < Table.RowCount(#"Add Running Total Column"),
each [cst=if currentTable[Qty]{[idx]+1}<0 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, In Price=Currency.Type, Qty=Int64.Type, ItemID=text, Running Total=Int64.Type, Avg Cost=Currency.Type])
in
#"Add Avg Cost Column"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"All"}),

#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Date", "In Price", "Qty", "Running Total", "Avg Cost"}, {"Date", "In Price", "Qty", "Running Total", "Avg Cost"}),
#"Rounded Off" = Table.TransformColumns(#"Expanded Custom",{{"Avg Cost", each Number.Round(_, 2), type number}})
in
#"Rounded Off"
"