How to replace values based on another cell of another column

[Harshc374]

I have the below data with trade, stock and price column where the output price column should be 

If Trade= "BUY" then Price corresponding to BUY

Else if Trade= "SELL" then Price corresponding to BUY in all sell rows

So basically the output for all the trades should be the Price for BUY trade i.e. 2000 in our example 

[lbendlin · Accepted solution]

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.

[Anonymous · Accepted solution]

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.

[lbendlin · Accepted solution]

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.