# Bulk Replace Values

Hi,

Got a scenario where we have around 6 columns which need to be cleaned, they will all have the same values needing to be cleaned. So rather than individually find & replace each value in each column I wanted to do a bulk find & replace, we have around 16 names to “clean” in each column.

Used this function from How To Excel | Master Excel

Bulk Find And Replace In Power Query | How To Excel

Created the Find & Replace table to compliment the function. The From column is the original value and Replace column is the new value.

Works fine when we only have 3 rows in our Find & Replace table but when we add more rows it runs into trouble, but it has worked a few times when we have had the whole Find & Replace table present. So there is not a clear issue springing to mind. Our full table which we clean has around 300,000 rows currently.

Just wondering if this function can’t cope with this volume or is there a better way to carry this out?

Bulk Find & Replace.pbix (27.6 KB)

Hi @DavieJoe,

See if this improves performance.

``````let
Source = DataTable,
Old = List.Buffer(FindReplaceTable[From]),
New = List.Buffer(FindReplaceTable[Replace]),
fxReplacements = (x as table, n as number, ColList as list ) as table =>
let
Replace = Table.ReplaceValue(x, Old{n}, New{n}, Replacer.ReplaceText, ColList),
Checking = if n = List.Count(Old)-1 then Replace else @fxReplacements(Replace, n+1, ColList )
in
Checking,
Result = fxReplacements( Source, 0, Table.ColumnNames(Source) )
in
Result
``````

.
Here’s your sample file
eDNA - Bulk Find & Replace.pbix (28.7 KB)

I hope this is helpful

1 Like

Thanks Melissa, will test this in the morning.

For my own learning, can you give a short explanation why this would be more efficient method?

DJ

Hi @DavieJoe,

1 Like

As I posted anonymized data, in our production table I have 6 columns, from a table of 40 columns, for this to iterate over.

I’m not clear how I define just those columns for this to work properly.

Hi @DavieJoe,

You’ll need to amend the Result variable, where the function is invoked:
Result = fxReplacements( Source, 0, Table.ColumnNames(Source) )

Now, in it’s final argument it’s considering all columns from the table: Table.ColumnNames(Source)
You’ll need to replace that with a list of actual column names, something like:
{ “Column1”, “Column2” }

I hope this is helpful

1 Like

That’s great @Melissa, thanks for the the speedy & clear reply.

As always, really appreciate your help.

DJ

1 Like

How does it compare speedwise, is it acceptable now?

1 Like

It’s for my boss and he backed out the changes that I had in my original posted PBIX.

So I’ll get him to try it out later (knowing him later could be in the next 2-3 weeks )

Currently he is using the 34 separate Find & Replace steps method

Once we test it I will come back to you.

DJ

Thanks, look forward to that update

1 Like