Capture the value of previous row in Power Query]

Hi Anonymous ,

According to your description, I update the code here:

et
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdFNCsMgEAXgqxTXaZh5/iVZ9wCli24k979GnaeQTTUg8pAP0TelOHWLE10lrxDgIXJw1dPP9113jZZ1d+dSHAxjjNFwJPaG/S0OxOEGK7NuxJEHY+wbVuLEF/3DL+Jn14k63+neR9Mb40TD75e2iEl7/WoQq2VMGsnxqlptighjzJshDYPlTxuxEttg1MaI2R+T5VT1+QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source, {{"Column1", "Serial"}, {"Column2", "Date"}, {"Column3", "Type"}, {"Column4", "qty"}, {"Column5", "price"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Serial", Int64.Type}, {"Date", type datetime}, {"Type", type text}, {"qty", Int64.Type}, {"price", Int64.Type}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "TA", each [qty]*[price]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Total_holdings", each List.Sum(List.Range(#"Added Custom"[qty],0,[Serial]))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Total_cost", each List.Sum(List.Range(#"Added Custom"[TA],0,[Serial]))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "cost_per_share", each [Total_cost]/[Total_holdings]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom3",{{"cost_per_share", type number}, {"TA", Int64.Type}, {"Total_holdings", Int64.Type}, {"Total_cost", Int64.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type2",{"Serial", "Date", "qty", "Type", "price", "TA", "Total_holdings", "Total_cost", "cost_per_share"}),
    #"Replace Values"=Table.ReplaceValue(#"Reordered Columns",each[price],each if[Type]="DVP"then Table.SelectRows(#"Reordered Columns",(x)=>x[Serial]=List.Max(Table.SelectRows(#"Reordered Columns",(x)=>x[Serial]<[Serial]and x[Type]<>"DVP")[Serial]))[cost_per_share]{0}else[price],Replacer.ReplaceValue,{"price"}),
    #"Replace Values2"=Table.ReplaceValue(#"Replace Values", each [TA], each [qty]*[price],Replacer.ReplaceValue,{"TA"}),
    #"Replace Values3"=Table.ReplaceValue(#"Replace Values2",each [Total_cost],each List.Sum(List.Range(#"Replace Values2"[TA],0,[Serial])),Replacer.ReplaceValue,{"Total_cost"}),
    #"Replace Values4"=Table.ReplaceValue(#"Replace Values3",each [cost_per_share],each [Total_cost]/[Total_holdings],Replacer.ReplaceValue,{"cost_per_share"})
    
in
    #"Replace Values4"

Best Regards,
Community Support Team _ kalyj

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