Power query - replace all contents if word appears

Hi all,

I'm working on a Power Query to distill our OneDrive-based (multiple-user) tracker to a to-do list. Most of the functionality is "if this cell is blank/null, it needs to be on the to-do list" which works quite well. However there are often times when it's important to note that a task needs to be done in the future (e.g. the "Call Dig-Alert" task can only be done within 21 days of digging, but one might identify that a dig-alert call is needed while going through the initial overview of the project). In this case, we've just been putting notes in the tracker spreadsheet and making the background red, and whoever sees it next should address it. (I've come to find out that Power Query won't be able to pull in background colors as data, which is a shame). If the task is accomplished, we'll input the confirmation code and it should be bypassed in the to-do list. The obvious problem comes into view when one isn't looking at the tracker, but only at the to-do list, and we miss that task because it wasn't addressed by the Power Query.

So what I need is some way to replace all the text in the cell if a certain word appears ("Need to call dig-alert" becomes NULL). All I've found so far is the ability to replace string-for-string, so my question is, is there a replace-all-if-string functionality? Or do you think there's a better way to go about it?

Thanks for your help!

Originally Posted by Norie

Are you looking to try to find a string within a string and if found do a replace on the entire string?

For example, you have the string 'Need to call dig-alert"' and you if you find 'Need' replace the entire string with Null?

Exactly that. As I mentioned, I found the Replace Values function but I don't know if it's possible to replace/nullify the whole value.

I think the inelegant solution I'll use is to create additional conditional columns, where the column formula is

Please Login or Register  to view this content.

I used "eed" and "equire" just to get around forcing uppercase or some other strategy