cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Find value and replace entire column with custom values

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
Super User V

Re: Find value and replace entire column with custom values

Hi,

You may download the PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Highlighted
Super User V
Super User V

Re: Find value and replace entire column with custom values

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.

Highlighted
Super User V
Super User V

Re: Find value and replace entire column with custom values

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Helper I
Helper I

Re: Find value and replace entire column with custom values

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

Highlighted
Super User V
Super User V

Re: Find value and replace entire column with custom values

Hi,

You may download the PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Highlighted
Helper I
Helper I

Re: Find value and replace entire column with custom values

@Ashish_Mathur it worked perfectly. Thank you very much!

Highlighted
Super User V
Super User V

Re: Find value and replace entire column with custom values

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors