How to update a cell value based on the conditional result of another column

[bc2022]

I come across a situation which I need to update the cell value with the correspondent value of other column based on the conditions.

Here is an exmple:  

code	category	value
aba	a	1
abd	e	2
abc	c	5
abe	c	5
abc	d	3
abf	b

I want to update the value of code abf with the correspondent value of category c which is 5. I was trying to use Table.ReplaceValue function but it seems somehow I need a column or variable to store the value (5) first. So I can use the following formula:

Table.ReplaceValue(#"Prior Step", each [value], each if [code] = "abf" then column/variable).

But I am not sure how to generate this column or variable. Or maybe I need to create a table which only has category C then merge it with the original one?

Any suggestions would be greatly appreciated.

[ImkeF · Accepted solution]

Hi @bc2022 ,
you can do it without additional merging like so:

let
  Source = Table.Buffer( Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WSkxKVNJRAmFDpVgdED8FyE4FYiMoPxnIBmFTKD8VjQ9ig/QYQ/lpQHYSECsoxcYCAA==", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [code = _t, category = _t, value = _t]
  )), 
  #"Replaced Value" = Table.ReplaceValue(
    Source, 
    each [value], 
    each 
      if [code] = "abf" then
        Table.SelectRows(Source, (x) => x[category] = "c"){0}[value]
      else
        [value], 
    Replacer.ReplaceValue, 
    {"value"}
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    #"Replaced Value", 
    {{"code", type text}, {"category", type text}, {"value", Int64.Type}}
  )
in
  #"Changed Type"

For performance reasons, buffering the Source-table is strongly recommended, as it will be referenced in each row of the new table during the replacement operation.

[bc2022]

Many thanks, @ImkeF!

Really appreciate your quick response.