Calculate values row by row in Power Query

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"

 

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.