Power Query: calculate and use result in the next row

[Christophe11]

Hello everyone

I need to calculate a stock forecast in columns Qty and Value.

Table before calculation

Material	YearMonth	RowNumber	StockQty	StockValue	PurchasingQty	PurchasingValue	SalesQty
AAAA	202410	1	10	15	3	4	8
AAAA	202411	2			7	8	3
AAAA	202501	3					4
AAAA	202507	4			8	5
BBBB	202410	1			3	4
BBBB	202411	2					4

Table after calculation

Material	YearMonth	RowNumber	StockQty	StockValue	PurchasingQty	PurchasingValue	SalesQty	Qty	Value
AAAA	202410	1	10	15	3	4	8	5	7,30769231
AAAA	202411	2	5	7,307692308	7	8	3	9	11,4807692
AAAA	202501	3	9	11,48076923			4	5	6,37820513
AAAA	202507	4	5	6,378205128	8	5		13	11,3782051
BBBB	202410	1			3	4		3	4
BBBB	202411	2	3	4			4	-1	-1,33333333
CCCC	202410	1	3	5			8	-5	-8,33333333

The first row by Material can have a StockQty and Value or not.

By Material, each next row is using Qty result in StockQty and Value result in StockValue from previsous row, as shown in Bold.

Calculation is done by Material after sort by RowNumber. or YearMonth: 

• Qty = StockQty + PurchasingQty - SalesQty
• Value = StockValue + PurchasingValue - SalesQty * ( StockValue + PurchasingValue ) / ( StockQty + PurchasingQty ) 

The logic corresponds to Periodic Unit Price calculation in SAP, that is different from Moving Average Price:

• Periodic Unit Price is calculated by period, only stock and purchase orders receipts are considered for stock/value calculation, and you just remove quantities after at the qty/value calculation
• Moving Average Price has no period, it's a sequentil calculation over the time that takes each thing into account, whatever it's stock, raw materials receipt on purchase order receipt, good exits are done at the stock/value when it's done, for this Grouped Running Total work but it's a different story than Periodic Unit Price which is what I need

I need to do this in Power Query, not in Power BI.

I have tried to use the logic from several topics but I struggle to manage it working. Here are the topics

Grouped Running Totals: https://www.myonlinetraininghub.com/grouped-running-totals-in-power-query 

Use Previous Rows Value in Current Calculation: https://community.fabric.microsoft.com/t5/Desktop/Power-Query-Use-Previous-Rows-Value-in-Current-Cal... 

Refer to next row to perform calcuation in power Query: https://community.fabric.microsoft.com/t5/Power-Query/Refer-to-next-row-to-perform-calcuation-in-pow... 

I understand that due to the specific calculation, I cannot use Grouped Runing Totals

Thanks a lot in advance for your help.

Christophe

[Anonymous · Accepted solution]

Hi,

Thanks for PhilipTreacy 's  and Omid_Motamedise 's concern about the problem, and i want to offer some more information for user to refer to.

hello @Christophe11 , you can create a blank query and put the following code in advanced editor in power query to calculate the qty.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgQCJR0lIwMjE0MDIMMQhMEMUyBhDMQmQGyhFKuDqhakzgiIIcgcrAakHlWdqYEh1BQIUoBiEwx15lCbIAhklilYLUidExCguxFmEsyFmCpRXYhkcywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Material = _t, YearMonth = _t, RowNumber = _t, StockQty = _t, StockValue = _t, PurchasingQty = _t, PurchasingValue = _t, SalesQty = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Material", type text}, {"YearMonth", Int64.Type}, {"RowNumber", Int64.Type}, {"StockQty", Int64.Type}, {"StockValue", Int64.Type}, {"PurchasingQty", Int64.Type}, {"PurchasingValue", Int64.Type}, {"SalesQty", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"StockQty", "StockValue", "PurchasingQty", "PurchasingValue", "SalesQty"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Qty", each let a=[Material],
b=[RowNumber],
c=Table.SelectRows(#"Replaced Value",each [Material]=a and [RowNumber]<=b),
d=List.Sum(c[StockQty])+List.Sum(c[PurchasingQty])-List.Sum(c[SalesQty])
in d),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each try Table.SelectRows(#"Added Custom",(x)=>x[Material]=[Material] and x[RowNumber]=[RowNumber]-1)[Qty]{0} otherwise [StockQty]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Value", each let
 a=[RowNumber],
BufferedTable = Table.Buffer(Table.SelectRows(#"Added Custom1",(x)=>x[Material]=[Material])),
            InitialValue = BufferedTable{0}[StockValue]+ BufferedTable{0}[PurchasingValue]-BufferedTable{0}[SalesQty]*( BufferedTable{0}[StockValue]+ BufferedTable{0}[PurchasingValue])/(BufferedTable{0}[Custom]+BufferedTable{0}[PurchasingQty]),
            CustomColumnValues = List.Generate(
                () => [prevValue = InitialValue, idx = 1],
                each [idx] <= Table.RowCount(BufferedTable),
                each [prevValue = [prevValue] +  BufferedTable{[idx]}[PurchasingValue]-BufferedTable{[idx]}[SalesQty]*(  [prevValue] +  BufferedTable{[idx]}[PurchasingValue])/(BufferedTable{[idx]}[Custom]+BufferedTable{[idx]}[PurchasingQty]),idx = [idx] + 1],
                
                each [prevValue]
            )
            in CustomColumnValues{a-1}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom"})
in
    #"Removed Columns"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Christophe11]

Thanks a lot, 2 solutions are working, I have learned a lot from you! Very useful, thanks again!

[Anonymous · Accepted solution]

Hi,

Thanks for PhilipTreacy 's  and Omid_Motamedise 's concern about the problem, and i want to offer some more information for user to refer to.

hello @Christophe11 , you can create a blank query and put the following code in advanced editor in power query to calculate the qty.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgQCJR0lIwMjE0MDIMMQhMEMUyBhDMQmQGyhFKuDqhakzgiIIcgcrAakHlWdqYEh1BQIUoBiEwx15lCbIAhklilYLUidExCguxFmEsyFmCpRXYhkcywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Material = _t, YearMonth = _t, RowNumber = _t, StockQty = _t, StockValue = _t, PurchasingQty = _t, PurchasingValue = _t, SalesQty = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Material", type text}, {"YearMonth", Int64.Type}, {"RowNumber", Int64.Type}, {"StockQty", Int64.Type}, {"StockValue", Int64.Type}, {"PurchasingQty", Int64.Type}, {"PurchasingValue", Int64.Type}, {"SalesQty", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"StockQty", "StockValue", "PurchasingQty", "PurchasingValue", "SalesQty"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Qty", each let a=[Material],
b=[RowNumber],
c=Table.SelectRows(#"Replaced Value",each [Material]=a and [RowNumber]<=b),
d=List.Sum(c[StockQty])+List.Sum(c[PurchasingQty])-List.Sum(c[SalesQty])
in d),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each try Table.SelectRows(#"Added Custom",(x)=>x[Material]=[Material] and x[RowNumber]=[RowNumber]-1)[Qty]{0} otherwise [StockQty]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Value", each let
 a=[RowNumber],
BufferedTable = Table.Buffer(Table.SelectRows(#"Added Custom1",(x)=>x[Material]=[Material])),
            InitialValue = BufferedTable{0}[StockValue]+ BufferedTable{0}[PurchasingValue]-BufferedTable{0}[SalesQty]*( BufferedTable{0}[StockValue]+ BufferedTable{0}[PurchasingValue])/(BufferedTable{0}[Custom]+BufferedTable{0}[PurchasingQty]),
            CustomColumnValues = List.Generate(
                () => [prevValue = InitialValue, idx = 1],
                each [idx] <= Table.RowCount(BufferedTable),
                each [prevValue = [prevValue] +  BufferedTable{[idx]}[PurchasingValue]-BufferedTable{[idx]}[SalesQty]*(  [prevValue] +  BufferedTable{[idx]}[PurchasingValue])/(BufferedTable{[idx]}[Custom]+BufferedTable{[idx]}[PurchasingQty]),idx = [idx] + 1],
                
                each [prevValue]
            )
            in CustomColumnValues{a-1}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom"})
in
    #"Removed Columns"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Christophe11]

Hello, thanks for your answers, I have modified the first post to include all information for precisions.

[AlienSx · Accepted solution]

let
    moving_average = (tbl) => 
        [
            rows = List.Buffer(Table.ToRecords(Table.Sort(tbl, "RowNumber"))),
            gen = List.Generate(
                () => 
                    [
                        i = 0, 
                        r = rows{0}, 
                        stock = [StockQty = r[StockQty], StockValue = r[StockValue]],
                        close = 
                            [
                                Qty = stock[StockQty] + r[PurchasingQty] - r[SalesQty],
                                Value = r[StockValue] + r[PurchasingValue] 
                                    - r[SalesQty] * (r[StockValue] + r[PurchasingValue]) / (r[StockQty] + r[PurchasingQty])
                            ]
                    ],
                (x) => x[i] < List.Count(rows), 
                (x) => 
                    [
                        i = x[i] + 1, 
                        r = rows{i},
                        stock = Record.RenameFields(x[close], {{"Qty", "StockQty"}, {"Value", "StockValue"}}),
                        close = 
                            [
                                Qty = stock[StockQty] + r[PurchasingQty] - r[SalesQty],
                                Value = stock[StockValue] + r[PurchasingValue] 
                                    - r[SalesQty] * (stock[StockValue] + r[PurchasingValue]) / (stock[StockQty] + r[PurchasingQty])
                            ]
                    ],
                (x) => x[r] & x[stock] & x[close]
            )
        ][gen],
    // replace Excel.CurrentWorkbook with a ref to your table
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    zero = Table.TransformColumns(Source, {}, (x) => if List.Contains({" ", "", null}, x) then 0 else x),
    group = Table.Group(zero, "Material", {"x", moving_average}), 
    result = Table.FromRecords(List.Combine(group[x]))
in
    result

[PhilipTreacy]

@Christophe11 

Hard to help without the file but sounds like you are trying to do running total - see if these help

Quickly Create Running Totals in Power Query 

Grouped Running Totals in Power Query

Regards

Phil

[Omid_Motamedise]

File is deleted and there is no access into it but you can use Python or R in Power query fut such mathematical model