Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Christophe11
Regular Visitor

Power Query: calculate and use result in the next row

Hello everyone

 

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

 

Table before calculation

 

MaterialYearMonthRowNumberStockQtyStockValuePurchasingQtyPurchasingValueSalesQty
AAAA20241011015348
AAAA2024112  783
AAAA2025013    4
AAAA2025074  85 
BBBB2024101  34 
BBBB2024112    4

 

Table after calculation

 

MaterialYearMonthRowNumberStockQtyStockValuePurchasingQtyPurchasingValueSalesQtyQtyValue
AAAA2024101101534857,30769231
AAAA202411257,307692308783911,4807692
AAAA2025013911,48076923  456,37820513
AAAA202507456,37820512885 1311,3782051
BBBB2024101  34 34
BBBB202411234  4-1-1,33333333
CCCC202410135  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

2 ACCEPTED SOLUTIONS
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1728553501906.png

 

 

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.

View solution in original post

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

View solution in original post

6 REPLIES 6
Christophe11
Regular Visitor

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

v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1728553501906.png

 

 

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
Regular Visitor

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

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
Super User
Super User

@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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Omid_Motamedise
Memorable Member
Memorable Member

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors