Find value and replace entire column with custom values

[jytech]

Hello,

I would like to search for a single text string in a cell that contains a long URL and replace that entire URL with a custom value/string.

For example this URL: /Login?ReturnUrl=https://www.company.org/support/articles-and-how-tos/10-steps-for-planning-a-successful-webinar. I would like to search the entire table for URL's containing the text string "articles" then replace that cell with the word "articles" only. The cells that do not contain "articles" to be replaced with "none." How can I do this?

Also, this URL: /posts/how-can-cloud-storage-benefit-my-company-http://blog.company.org/posts/how-can-cloud-storage-benefit-my-company, I would like to search the entire table for URL's containing the text string "blog" then replace that cell with the word "blog" only.

[Ashish_Mathur]

Hi,

In a calculated column formula, you may use the IF() and SEARCH() functions.

[jytech]

@Ashish_Mathur can you please provide an example of what that formula would look like?

[Ashish_Mathur · Accepted solution]

Hi,

You may download the PBI file from here.

Hope this helps.

[jytech]

@Ashish_Mathur it worked perfectly. Thank you very much!

[Ashish_Mathur]

You are welcome.

[lbendlin]

Check the documentation

https://docs.microsoft.com/en-us/powerquery-m/table-replacevalue

You will notice that the replacer is a function. This means you can use whatever custom function you want in that step, including the checks that you describe.  You could even have a reference table with keywords that should be searched for, and that should replace the entire cell contents if found.