Convert Excel formula to Power Query

[Kyros]

HI,

I ask for help in converting the Excel formula to Power Query. I attach the Excel file for better understanding. The first row of the Calculated Stock column (E2) comes from the Inventory Stock + Goods Arrived - Consumed Goods (=C2+B2-D2) calculation. The second row of the Calculated Stock column (E3), however, comes from the calculation of the previous row Calculated Stock + Goods Arrived - Consumed Goods (=E2+B3-D3) and so on until a value is encountered again in Inventory Stock ("C18 " - see line 18), where the calculation returns as the first line. It can be done?
I thank everyone for their contribution.

[Anonymous · Accepted solution]

Hi ,

Thanks for the solution @AlienSx  and @twi1  offered, and i want to offer some more information for user to refer to.

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

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZAxDsQwCAT/4joFeA0hb7Hy/29EiaXc7TZ3hYsRY1iYs7kPs7a19c5ttn/Io4hRSTwML8f+DAg4KdFD/iRzoHMdEB/i64CSuvBYOzkiP06I84O1p3PmTL5bOfs1OHP14+WyZezSga9UkPpXwm6rxb3UeQE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Inventory Stock" = _t, #"Good Arrived" = _t, #"Consumed Goods" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Inventory Stock", Int64.Type}, {"Good Arrived", Int64.Type}, {"Consumed Goods", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let a=[Index],
_Index=List.Max(Table.SelectRows(#"Added Index",each [Index]<=a and [Inventory Stock]<>0)[Index]),
_Invetoty=Table.SelectRows(#"Added Index",each [Index]=_Index)[Inventory Stock]{0},
_filter=Table.SelectRows(#"Added Index",each [Index]<=a and [Index]>=_Index)
in _Invetoty+List.Sum(_filter[Good Arrived])-List.Sum(_filter[Consumed Goods]))
in
    #"Added Custom"

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.

[AlienSx]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
    rows = List.Buffer(Table.ToRows(Source)), 
    gen = List.Generate(
        () => [i = 0, calc = List.Sum({rows{0}{2}, rows{0}{1}, - rows{0}{3}})], 
        (x) => x[i] < List.Count(rows),
        (x) => 
            [
                i = x[i] + 1,
                calc = List.Sum({rows{i}{1}, - rows{i}{3}, rows{i}{2} ?? x[calc]})
            ],
        (x) => rows{x[i]} & {x[calc]}
    ),
    tbl = Table.FromRows(gen, Table.ColumnNames(Source) & {"CALCULATED STOCK"})
in
    tbl

[Kyros]

Hi AlienSx, I inserted your function, but the result is not as expected. I am sending screenshots for better understanding. 

Language M code:

Origine = Excel.Workbook(File.Contents("C:\Users\ciro.urselli\Desktop\Stock.xlsx"), null, true),
Sheet1_Sheet = Origine{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Intestazioni alzate di livello" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Modificato tipo" = Table.TransformColumnTypes(#"Intestazioni alzate di livello",{{"Date", type date}, {"Goods Arrived", Int64.Type}, {"Inventory Stock", Int64.Type}, {"Consumed Goods", Int64.Type}, {"EXCEL calculated column for reference", Int64.Type}}),

rows = List.Buffer(Table.ToRows(#"Modificato tipo")),
gen = List.Generate(
() => [i = 0, calc = List.Sum({rows{0}{2}, rows{0}{1}, - rows{0}{3}})],
(x) => x[i] < List.Count(rows),
(x) =>
[
i = x[i] + 1,
calc = List.Sum({rows{i}{1}, - rows{i}{3}, rows{i}{2} ?? x[calc]})
],
(x) => rows{x[i]} & {x[calc]}
),
tbl = Table.FromRows(gen, Table.ColumnNames(Origine) & {"CALCULATED STOCK"})
in
tbl

[twi1]

Hi @Kyros ,

I have seen your problem and I want to advise you creating custom column in power quiry uding M code to create column that calculates the Calculated stock:

let
// Load your source data here
Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],

// Add Index Column to maintain order
AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),

// Define a function to calculate the running stock
CalculateStock = (tbl as table, currentIndex as number) as number =>
let
// Get the previous row index
prevIndex = currentIndex - 1,
// Base case: if it's the first row, use the initial stock formula
result = if currentIndex = 1 then
tbl{currentIndex-1}[Inventory Stock] + tbl{currentIndex-1}[Goods Arrived] - tbl{currentIndex-1}[Consumed Goods]
else
// Check if Inventory Stock is not null for the current row
if tbl{currentIndex-1}[Inventory Stock] <> null then
tbl{currentIndex-1}[Inventory Stock] + tbl{currentIndex-1}[Goods Arrived] - tbl{currentIndex-1}[Consumed Goods]
else
// Recursive case: calculate stock using the previous row's calculated stock
CalculateStock(tbl, prevIndex) + tbl{currentIndex-1}[Goods Arrived] - tbl{currentIndex-1}[Consumed Goods]
in
result,

// Add the custom column using the defined function
AddCalculatedStock = Table.AddColumn(AddIndex, "Calculated Stock", each CalculateStock(AddIndex, [Index]), type number),

// Remove the Index column
RemoveIndex = Table.RemoveColumns(AddCalculatedStock, {"Index"})
in
RemoveIndex 
AddIndex: Adds an index column to your table to keep track of row numbers.
CalculateStock: Defines a recursive function to calculate the running stock. It handles the base case (first row) and checks for non-null Inventory stock in the current row. If Inventory stock is not null, it uses that as the starting point; otherwise, it continues to calculate based on the previous row's Calculated stock. AddCalculatedStock: Adds theCalculated stock. column to your table using the Calculated stock function. RemoveIndex: Removes the index column to clean up the table.

Replace '' Ypu table name'' with the actual name of your table in Excel.

 

Make sure the table name in the Source step matches the name of your table in Excel. Adjust the column names in the M code if they differ from the ones shown in the screenshot.

Please tell me if it was helpful, if you need further details and discussion I am here anytime, you can contact me here, 
I look forward to your response

Best regards,
Tamar

[Kyros]

I entered the code as indicated by Tamar, but the result of the "Calculated Stock" column is not as expected. As you can see, both in the Power Query editor and in the Power BI desktop, it does not calculate correctly taking into account the stock of the previous row. I attach the screenshots for better understanding. HELP ME!!!

M language code:

let
Origine = Excel.Workbook(File.Contents("C:\Users\xxxxx\Desktop\Stock.xlsx"), null, true),
Sheet1_Sheet = Origine{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Intestazioni alzate di livello" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Modificato tipo" = Table.TransformColumnTypes(#"Intestazioni alzate di livello",{{"Date", type date}, {"Goods Arrived", Int64.Type}, {"Inventory Stock", Int64.Type}, {"Consumed Goods", Int64.Type}, {"EXCEL calculated column for reference", Int64.Type}}),

// Add Index Column to maintain order
AddIndex = Table.AddIndexColumn(#"Modificato tipo", "Index", 1, 1, Int64.Type),

// Define a function to calculate the running stock
CalculateStock = (tbl as table, currentIndex as number) as number =>

let
// Get the previous row index
prevIndex = currentIndex - 1,

// Base case: if it's the first row, use the initial stock formula
result = if currentIndex = 1 then
tbl{currentIndex-1}[Inventory Stock] + tbl{currentIndex-1}[Goods Arrived] - tbl{currentIndex-1}[Consumed Goods]

else
// Check if Inventory Stock is not null for the current row
if tbl{currentIndex-1}[Inventory Stock] <> null then
tbl{currentIndex-1}[Inventory Stock] + tbl{currentIndex-1}[Goods Arrived] - tbl{currentIndex-1}[Consumed Goods]

else
// Recursive case: calculate stock using the previous row's calculated stock
CalculateStock(tbl, prevIndex) + tbl{currentIndex-1}[Goods Arrived] - tbl{currentIndex-1}[Consumed Goods]

in
result,

// Add the custom column using the defined function
AddCalculatedStock = Table.AddColumn(AddIndex, "Calculated Stock", each CalculateStock(AddIndex, [Index]), type number),
// Remove the Index column
RemoveIndex = Table.RemoveColumns(AddCalculatedStock, {"Index"})
in
RemoveIndex

[Anonymous · Accepted solution]

Hi ,

Thanks for the solution @AlienSx  and @twi1  offered, and i want to offer some more information for user to refer to.

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

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZAxDsQwCAT/4joFeA0hb7Hy/29EiaXc7TZ3hYsRY1iYs7kPs7a19c5ttn/Io4hRSTwML8f+DAg4KdFD/iRzoHMdEB/i64CSuvBYOzkiP06I84O1p3PmTL5bOfs1OHP14+WyZezSga9UkPpXwm6rxb3UeQE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Inventory Stock" = _t, #"Good Arrived" = _t, #"Consumed Goods" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Inventory Stock", Int64.Type}, {"Good Arrived", Int64.Type}, {"Consumed Goods", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let a=[Index],
_Index=List.Max(Table.SelectRows(#"Added Index",each [Index]<=a and [Inventory Stock]<>0)[Index]),
_Invetoty=Table.SelectRows(#"Added Index",each [Index]=_Index)[Inventory Stock]{0},
_filter=Table.SelectRows(#"Added Index",each [Index]<=a and [Index]>=_Index)
in _Invetoty+List.Sum(_filter[Good Arrived])-List.Sum(_filter[Consumed Goods]))
in
    #"Added Custom"

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.

[Kyros · Accepted solution]

Works! Great work Yolo Zhu. Thank you so much. I also thank all the others who proposed their solution. It was a great lesson learned.