How to replace values based on another cell of another column

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgqNVNJRcnRyVnD2DwoAMo0MDAyUYnWilYJdfXzQpExxS5nhljIHScUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Trade = _t, Stock = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Trade", type text}, {"Stock", type text}, {"Price", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Output Price", (k)=>  List.Sum(Table.SelectRows(#"Changed Type",each [Stock]=k[Stock] and [Trade]="BUY")[Price]))
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

Hi Harshc374 ,

lbendlin Good Answer!
And I have considered the more complex case of data, you can try this method as well.
Put all of the M function into Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgqNVNJRcnRyVnDOLyoAMo0MDAyUYnWilYJdfXzQpExxS5nhljKHSvmFYkhZ4JayhEpBHOji6gaTMURzILKUKW4pM1SrkKXADowFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Trade = _t, Stock = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Trade", type text}, {"Stock", type text}, {"Price", Int64.Type}}),
    BuyPrice = Table.SelectRows(#"Changed Type", each [Trade] = "BUY"),
    BuyPriceOnly = Table.SelectColumns(BuyPrice, {"Stock", "Price"}),
    UniqueBuyPrice = Table.Distinct(BuyPriceOnly),
    Custom = Table.AddColumn(#"Changed Type", "CustomPrice", each try 
        if [Trade] = "BUY" or [Trade] = "SELL" then
            let 
                currentStock = [Stock],
                priceRecord = 
                Table.SelectRows(UniqueBuyPrice, each [Stock] = currentStock)
                in
                    if Table.IsEmpty(priceRecord) then null else priceRecord{0}[Price]
        else null),
    #"Expanded CustomPrice" = Table.ExpandRecordColumn(Custom, "CustomPrice", {"Value"}, {"CustomPrice.Value"})
in
    #"Expanded CustomPrice"

The final output is as below:

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