Forum Discussion
Using ReplaceValue to conditionally replace a cell with a numeric value based on another column's value
I've seen lots of suggestions for when one is populating cells with text, but what if the new value is a number? In the example below, I filtered a table to a single row and requesting if the ticker column is "T", then put a number in the Total Cost column.
I can't seem to get this to work. Any guidance would be greatly appreciated.
= Table.ReplaceValue(#"Filtered Rows1", each [Ticker], each if Text.Contains([Ticker],"T") then 5 else 3,Replacer.ReplaceValue,{"Total Cost"})
6 Replies
You old value argument is incorrect
= Table.ReplaceValue( #"Filtered Rows1", each[Total Cost], each if Text.Contains([Ticker],"T") then 5 else 3, Replacer.ReplaceValue, {"Total Cost"})Thank you Admin- It worked! Would you mind helping me understand how the code works with respect to "null" and x,y,z variables?
Much appreciated!
Since you seem to be copying my responses to another forum, make the appropriate edits also.
I don't know why @Syndicate_Admin seems to be taking credit for the answer I posted. If you go to the linked forum where I answered this question, you will see that I edited that answer to a correct one, and also explained the error in your original code (which was pretty close). What @Syndicate_Admin copied here, although it works, should not be used as it is, in my opinion, bad programming technique. In the example that @Syndicate_Admin copied here, the null argument is irrelevant, as it never gets used; and the (x,y,z)=> refer to the three arguments normally passed to the Replacer.ReplaceValue function, by the Table.ReplaceValue function.
RonR, I'm really confused as I don't see any other solution/suggestion other than the (x,y,z) solution...
I guess Microsoft and @Syndicate_Admin would rather obscure things than make it simple.
If you look at the answers that @Syndicate_Admin posted, you will see they make a reference to a "Source Community". If you click on that link, you will find the forum where I actually answered this question. The real source of this answer
