Answered

Using ReplaceValue to conditionally replace a cell with a numeric value based on another column's value

(0) Share

Report

Posted on by Wolly

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"})

Categories:

Power Query

All responses (4)

Answers (0)

Sort by

Wolly 4 on at

Like
a
(0)

Report
Copy link

Link copied!

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!
RonR 8 on at

Like
a
(0)

Report
Copy link

Link copied!

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. (See MS Help for that function for details).
Wolly 4 on at

Like
a
(0)

Report
Copy link

Link copied!

RonR, I'm really confused as I don't see any other solution/suggestion other than the (x,y,z) solution...
RonR 8 on at

Like
a
(0)

Report
Copy link

Link copied!

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