Forum Discussion
How to update a cell value based on the conditional result of another column
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.
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.
2 Replies
- ImkeF
Community Champion
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.
