Calculate values row by row in Power Query

[Gabriel_Pedri]

Hello everyone, I'm having some difficulty.

I have a table of sold products, and I want to create a column for Inicial Cash and Final Cash row by row in this table.

The calculation is simple:

• Initial Cash: If Index = 0, then InitialCashValue; otherwise, Final Cash from the previous row (Index -1).
• Final Cash = Initial Cash + Total

InitialCashValue is a parameter created to pass the first value.

Query Script

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dVHLDoIwEPwVwlnMtlDAo4+DJ0Pi0XCoplFiY4nU/7e7JRFpObDtNjOzO8zlknLgIgOWgUhX6U5q3ZvuZZNGvVzP1wLwwCJgDZC2qx+F4fPJWHU15umuTCDCnVQieBxxtrLX6o2qIxxfmW+maBrayF69k73u+gGRI4F2CvU5Km2LhEi09mQfDjM895aP3f2h3WeJkntGjmUTEEioMYPNOutu9SjvDYRofD+o4Znspb59tLSGRowuctpMzFlkeykHVCyp+aOIeA51bK/c2w5jqBBdiTmaLcZQ+j8V6LOlGDg2RYAnx9EYCog7IMNBDqTDSo9uvw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Product = _t, Price = _t, Quantity = _t, Total = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Price", type number}, {"Quantity", type number}, {"Date", type date}, {"Total", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Inicial Cash", each null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Final Cash", each null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Inicial Cash", type number}, {"Final Cash", type number}})
in
    #"Changed Type1"

Table:

Parameter:

Result:

[danextian · Accepted solution]

Hi @Gabriel_Pedri 

Not really a good idea to be doing this in the query editor. Expect for a very slow performance on large tables.

Also, you can't be accessing Index -1 of the previous step as that is simply accessing the total and not the running total.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dVHLDoIwEPwVwlnMtlDAo4+DJ0Pi0XCoplFiY4nU/7e7JRFpObDtNjOzO8zlknLgIgOWgUhX6U5q3ZvuZZNGvVzP1wLwwCJgDZC2qx+F4fPJWHU15umuTCDCnVQieBxxtrLX6o2qIxxfmW+maBrayF69k73u+gGRI4F2CvU5Km2LhEi09mQfDjM895aP3f2h3WeJkntGjmUTEEioMYPNOutu9SjvDYRofD+o4Znspb59tLSGRowuctpMzFlkeykHVCyp+aOIeA51bK/c2w5jqBBdiTmaLcZQ+j8V6LOlGDg2RYAnx9EYCog7IMNBDqTDSo9uvw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Product = _t, Price = _t, Quantity = _t, Total = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Price", type number}, {"Quantity", type number}, {"Date", type date}, {"Total", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Inicial Cash", each let 
initial = 10,
prevStep = #"Added Index",
currentIndex = [Index],
filteredTable = Table.SelectRows(prevStep, each [Index] < currentIndex)[Total],
Total = List.Sum(filteredTable)
in ( Total ??  0 ) + initial
, type number),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Final Cash", each [Inicial Cash] + [Total], type number),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Inicial Cash", type number}, {"Final Cash", type number}})
in
    #"Changed Type1"

[danextian · Accepted solution]

Hi @Gabriel_Pedri 

Not really a good idea to be doing this in the query editor. Expect for a very slow performance on large tables.

Also, you can't be accessing Index -1 of the previous step as that is simply accessing the total and not the running total.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dVHLDoIwEPwVwlnMtlDAo4+DJ0Pi0XCoplFiY4nU/7e7JRFpObDtNjOzO8zlknLgIgOWgUhX6U5q3ZvuZZNGvVzP1wLwwCJgDZC2qx+F4fPJWHU15umuTCDCnVQieBxxtrLX6o2qIxxfmW+maBrayF69k73u+gGRI4F2CvU5Km2LhEi09mQfDjM895aP3f2h3WeJkntGjmUTEEioMYPNOutu9SjvDYRofD+o4Znspb59tLSGRowuctpMzFlkeykHVCyp+aOIeA51bK/c2w5jqBBdiTmaLcZQ+j8V6LOlGDg2RYAnx9EYCog7IMNBDqTDSo9uvw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Product = _t, Price = _t, Quantity = _t, Total = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Price", type number}, {"Quantity", type number}, {"Date", type date}, {"Total", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Inicial Cash", each let 
initial = 10,
prevStep = #"Added Index",
currentIndex = [Index],
filteredTable = Table.SelectRows(prevStep, each [Index] < currentIndex)[Total],
Total = List.Sum(filteredTable)
in ( Total ??  0 ) + initial
, type number),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Final Cash", each [Inicial Cash] + [Total], type number),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Inicial Cash", type number}, {"Final Cash", type number}})
in
    #"Changed Type1"

[Gabriel_Pedri]

Which method do you recommend?

[danextian]

DAX is more optimized at scanning a table than M. But the size of the table will always remain a factor.

[Gabriel_Pedri]

Circular dependency problem.

https://community.fabric.microsoft.com/t5/Desktop/Power-BI-Recursion-DAX/m-p/3789525

[Gabriel_Pedri]

Unfortunately I have already tried to do it in DAX but due to the loop needed to solve the problem it is not possible.

[danextian · Accepted solution]

Hi @Gabriel_Pedri 

There's nothing in the M code I provided that loops. It is simply filtering the previous step based on the current step's index column and summing up the value. This is the DAX version.

Initial Cash DAX = 
VAR _initial = 10
RETURN
    CALCULATE (
        SUM ( 'Table'[Total] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] < EARLIER ( 'Table'[Index] ) )
    ) + _initial


Final Cash DAX = 
'Table'[Initial Cash DAX] + 'Table'[Total]

As a calculated column, this will still be slow on a large table since the result needs to be stored in memory. It’s better to implement this as a measure instead.

[Gabriel_Pedri]

You are right, it has become much more optimized and efficient.

Thank you very much for your support.