Mass Find/Replace Code Not Working When Pasting Into Another Workbook

Question

I am trying to do a mass find and replace within Power Query to avoid having to create individual steps for each replacement. I had previously researched this and used the solution here and it worked but due to the length of the replace and how often it is used, it does not make sense to recreate it each time, so I pasted it into OneNote for later reference.

Now, each time that I try to use the code, it won't work. Thinking that it might be some invalid character from OneNote messing it up, I paste it into Visual Studio to ensure the code looks good and no invalid characters were added.

Source data is exactly the same, just the data within the actual table is updated to match the most up to date information from the system.

Due to the security of the content, I am unable to share the source file but I am able to attach the Power Query M code (Mass Replace.txt).

What am I missing?

Column name:

Code for reference:

Answer 1

Hi @Renee Crozier, 

Thank you for posting your question in the Microsoft Q&A forum. 

Based on your sharing, the issue highly comes from the variable definition in your code #"Replaced Value" = Table.TransformColumns(#"Filtered Rows", {{"LinkURL", each Record.FieldOrDefault(AllReplace, _, _)}}). When defining, you used #"AllReplace" but in the code you referenced AllReplace, which leading to using unknown AllReplace variable before you defined it.  

I suggest you can try fixing the variable name from

#"AllReplace" = [ 

// Your entire replacement record 

] 

To  

AllReplace = [ 

// Your entire replacement record 

] 

Then you can check again to see if the query works properly.  

Please understand that our initial response does not always resolve the issue immediately. However, you can try workaround and share us more detailed information, we can work together to find a solution.  

Thank you for your understanding and cooperation. I'm looking forward to your reply.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

@Renee Crozier

BTW,

Creating a Replacement table is a very dynamic approach; as you can easily add, new words/strings to find and replace, without having to modify the M-Code each time.

It can also be used in multiple queries and/or workbooks

You may find in the link the updated file

https://1drv.ms/x/c/0690c2e5540f9131/ERurW4VOR4dEtuUc6LRUP7wBRFDXgEoYHCuSSxU4n3ZEbQ?e=ZB18uc

Regards

Jeovany

Answer 3

Hi Renee

No problem at all, I'm here to help you. Let me show you how it works.

Regarding, "...Does this solution work solely if the table exists in Excel? If so, I can't use this code as I run a lot of reports, so adding a table just for this is not great."

It doesn't matter where the table source comes from; it can be imported from a File, Folder, SharePoint, or from the Web.

The Replacement table, though, has to be located/created on an Excel file. It can be in the current file where the Query is created (recommended) or be created in a separate file.

To answer/explain the solution, I had to create a fake Source Table in an Excel file, as I do not have access to the original source.

To mimic or partially reproduce your scenario,

1. Let's rename the Source table as "AllContentReport". (as the name on your table query).
2. Similarly, let's rename the column we want to replace the values in as "LinkURL"
3. In the Replacement Table (in Green), we enter the values to find and replace, respectively. Once the Tables are uploaded to PQ.
4. Make sure the Replacement table columns and the "LinkURL" column are formatted as "Text". Also, filter out "Null" rows in the Replacement table.

5. Select the "AllContentReport" source table >> Go to the Add Column Tab and "Add Custom Column" with the following formula.

List.Accumulate(
    List.Numbers(0, Table.RowCount(Replacements)), 
    [LinkURL], 
    (state, current) => 
    Text.Replace(state, Replacements[Find]{current}, Replacements[Replace]{current}))

Where "Replacements" refers to the same-named table.

NOTE: Based on the picture below, you must be adding the custom column step after filtering your Source table.

6. Then simply, remove the (old) "LinkURL" column and rename the "Custom1" column as per your needs.

and VOILA!!!!, JOB DONE!

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

On the other hand,

If the answer helped you. Please consider marking this thread as answered.

It would help others in the community with similar questions or problems.

Thank you in advance

Regards

JeovanyCV

Volunteer Moderator

Answer 4

Hi Renee

In the link below, you may find a solution to your problem with a different approach.

https://1drv.ms/x/c/0690c2e5540f9131/EZAb_3MyFd5EmAADM2fjOCEBQjaMBigPryWBWI_XA3bd9g?e=Gbqmoc

1. This Query uses a table for listing all the words and/or characters to find, with their respective replacement values (the green table in the picture below). Then simply "Refresh" the Table

Here is the M Code:

let
    Source = Input_Text,
    #"Added Custom" = Table.AddColumn(Source, "Output_Text", each List.Accumulate(
    List.Numbers(0, Table.RowCount(replacements)), 
    [Column1], 
    (state, current) => 
    Text.Replace(state, replacements[Find]{current}, replacements[Replace]{current}))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"})
in
    #"Removed Columns"

Please, adapt the M code to your scenario.

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

On the other hand,

If the answer helped you. Please consider marking this thread as answered.

It would help others in the community with similar questions or problems.

Thank you in advance

Regards

Jeovany