# M language query. Bulk replacement - match cell content instead of part of string

• ### Question

• Hi all,

I got the function below online and it worked great with my data. However, the bulk replacement works for any part of the string. Since I have in my replacement column strings like X, XY and ZYX, the X gets replaced in the other cells so it becomes a mess. I wanted the bulk replacement to work using the cell content instead of part of the string. Just not sure how to modify the function below.

Anybody could help?

Thank you!

``````let BulkReplace = (DataTable as table, FindReplaceTable as table, DataTableColumn as list) =>
let
//Convert the FindReplaceTable to a list using the Table.ToRows function
//so we can reference the list with an index number
FindReplaceList = Table.ToRows(FindReplaceTable),
//Count number of rows in the FindReplaceTable to determine
//how many iterations are needed
Counter = Table.RowCount(FindReplaceTable),
//Define a function to iterate over our list
//with the Table.ReplaceValue function
BulkReplaceValues = (DataTableTemp, n) =>
let
//Replace values using nth item in FindReplaceList
ReplaceTable = Table.ReplaceValue(
DataTableTemp,
//replace null with empty string in nth item
if FindReplaceList{n}{0} = null then "" else FindReplaceList{n}{0},
if FindReplaceList{n}{1} = null then "" else FindReplaceList{n}{1},
Replacer.ReplaceText,
DataTableColumn
)
in
//if we are not at the end of the FindReplaceList
//then iterate through Table.ReplaceValue again
if n = Counter - 1
then ReplaceTable
else @BulkReplaceValues(ReplaceTable, n + 1),
//Evaluate the sub-function at the first row
Output = BulkReplaceValues(DataTable, 0)
in
Output
in
BulkReplace``````

Monday, April 1, 2019 1:40 PM

• ``````let BulkReplace = (DataTable as table, FindReplaceTable as table, DataTableColumn as list) =>
let
//Convert the FindReplaceTable to a list using the Table.ToRows function
//so we can reference the list with an index number
FindReplaceList = Table.ToRows(FindReplaceTable),
//Count number of rows in the FindReplaceTable to determine
//how many iterations are needed
Counter = Table.RowCount(FindReplaceTable),
//Define a function to iterate over our list
//with the Table.ReplaceValue function
BulkReplaceValues = (DataTableTemp, n) =>
let
//Replace values using nth item in FindReplaceList
ReplaceTable = Table.ReplaceValue(
DataTableTemp,
//replace null with empty string in nth item
if FindReplaceList{n}{0} = null then "" else FindReplaceList{n}{0},
if FindReplaceList{n}{1} = null then "" else FindReplaceList{n}{1},
Replacer.ReplaceText,
DataTableColumn
)
in
//if we are not at the end of the FindReplaceList
//then iterate through Table.ReplaceValue again
if n = Counter - 1
then ReplaceTable
else @BulkReplaceValues(ReplaceTable, n + 1),
//Evaluate the sub-function at the first row
Output = BulkReplaceValues(DataTable, 0)
in
Output
in
BulkReplace``````

Try replacing this part with Replacer.ReplaceValue
Monday, April 1, 2019 3:00 PM
• If you want to replace whole strings only, you might want to try this approach instead: https://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/

Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com

Saturday, April 27, 2019 4:57 AM

### All replies

• ``````let BulkReplace = (DataTable as table, FindReplaceTable as table, DataTableColumn as list) =>
let
//Convert the FindReplaceTable to a list using the Table.ToRows function
//so we can reference the list with an index number
FindReplaceList = Table.ToRows(FindReplaceTable),
//Count number of rows in the FindReplaceTable to determine
//how many iterations are needed
Counter = Table.RowCount(FindReplaceTable),
//Define a function to iterate over our list
//with the Table.ReplaceValue function
BulkReplaceValues = (DataTableTemp, n) =>
let
//Replace values using nth item in FindReplaceList
ReplaceTable = Table.ReplaceValue(
DataTableTemp,
//replace null with empty string in nth item
if FindReplaceList{n}{0} = null then "" else FindReplaceList{n}{0},
if FindReplaceList{n}{1} = null then "" else FindReplaceList{n}{1},
Replacer.ReplaceText,
DataTableColumn
)
in
//if we are not at the end of the FindReplaceList
//then iterate through Table.ReplaceValue again
if n = Counter - 1
then ReplaceTable
else @BulkReplaceValues(ReplaceTable, n + 1),
//Evaluate the sub-function at the first row
Output = BulkReplaceValues(DataTable, 0)
in
Output
in
BulkReplace``````

Try replacing this part with Replacer.ReplaceValue
Monday, April 1, 2019 3:00 PM
• If you want to replace whole strings only, you might want to try this approach instead: https://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/

Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com