Find and Replace values in 1 column in a tbl with values defined in different table

[El Cid]

I tried to follow this - https://blog.crossjoin.co.uk/2014/06/25/using-list-generate-to-make-multiple-replacements-of-words-in-text-in-power-query/
Here's my M-Code:
let
//Get table of word replacements - Old Text and New Text - tblRenameNew is a CSV file that has columns OLDNAME and NEWNAME
Replacements = tblRenameNew,
//Get table containing BAD text to change - SALES_NEW01 contains two columns JOBID and SALES
TextToChange = SALES_NEW01,
//Get list of words to replace - OLDNAME from tblRenameNew
WordsToReplace = Table.Column(Replacements,"OLDNAME"),
//Get list of words to replace them with - NEWNAME from tblRenameNew
WordsToReplaceWith = Table.Column(Replacements, "NEWNAME"),
//A non-recursive function to do the replacements
ReplacementFunction = (InputText)=>
let
//Use List.Generate() to do the replacements
DoReplacement = List.Generate(
()=> [Counter=0, MyText=InputText],
each [Counter]<=List.Count(WordsToReplaceWith),
each [Counter=[Counter]+1,
MyText=Text.Replace(
[MyText],
WordsToReplace{[Counter]},
WordsToReplaceWith{[Counter]})],
each [MyText]),
//Return the last item in the list that List.Generate() returns
GetLastValue = List.Last(DoReplacement)
in
GetLastValue,
//Add a calculated column to call the function on every row in the table containing the text to change
Output = Table.AddColumn(TextToChange, "Changed Text", each ReplacementFunction([Text]))
in
Output

This is what I end up with...

Obviously, I'm not understanding how this function is working. Can someone point me in the right direction?

 

[Rudi]

It is necessary to have the extra table called: SALES_NEW01
Can't you just add the renamed field directly to the Source table?

See the attached workbook as an example...

View attachment Sample.zip

 

[El Cid]

It is necessary to have the extra table called: SALES_NEW01
Can't you just add the renamed field directly to the Source table?

See the attached workbook as an example...

View attachment 7782

Dahyam! Thank you very much. I think I see what I was doing wrong [palm to forehead].

Yes, I can and will on my model, but when working on something like this where I might need outside help I'm trying to separate the "raw data processing" I do before testing the new process into a separate step. That SALES_NEW01 file is an import of a CSV file that has a bunch of unrelevant lines of M code.

 

[Rudi]

I notice I typed: "It is necessary...".
I meant it as: "Is it necessary...?"

Unless you are doing something I'm not aware of, it is not necessary to have the Sales_New01 table.

Sorry for the confusion!!

 

[El Cid]

I notice I typed: "It is necessary...".
I meant it as: "Is it necessary...?"

Unless you are doing something I'm not aware of, it is not necessary to have the Sales_New01 table.

Sorry for the confusion!!

I just added something like this to my SALES_NEW01 table import:


#"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "JOBID", each fxReplacementNewFunction([JOBID.1])),


Worked like a charm one I had that function defined. Much thanks.

 

[Rudi]

Great to hear! :thumb:

 

[El Cid]

Great to hear! :thumb:

I spoke too soon...one of my "WordsToReplaceWith" went wild. I got to find a way to limit the WordsToReplace to just those exact words in that field and no others.

 

[El Cid]

http://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/
I think I'm going to have to figure this stuff out....

let
Source = ReplacementsTable,
CreateListOfLists = Table.Buffer(Table.AddColumn(Source, “Liste”, each ({[Word To Replace], [Replace With]}))[
[Liste]]), TurnTextToList = Table.AddColumn(Text, “Custom”, each Text.Split([Text], ” “)),
Replacements = Table.AddColumn(TurnTextToList, “Changed Text Expected”, each Text.Combine(List.ReplaceMatchingItems([Custom],CreateListOfLists
[Liste]),” “))[[Changed Text Expected]]
in
Replacements

 

[El Cid]

Bill and Imke...I loose them in the translation.

ChangedType = Table.TransformColumnTypes(GroupedRows,{{"JOBID.1", type text}})
// Repl = tblRenameNew,
// CreateListOfLists = Table.Buffer(Table.AddColumn(Rep1, “Liste”, each ({[OLDNAME], [NEWNAME]}))[
[Liste]]), TurnTextToList = Table.AddColumn(Text, “Custom”, each Text.Split([Text], ” “)),
// Replacements = Table.AddColumn(TurnTextToList, “Changed Text Expected”, each Text.Combine(List.ReplaceMatchingItems([Custom],CreateListOfLists
[Liste]),” “))[[Changed Text Expected]]
in
ChangedType

 

[Rudi]

Would it not help to program your lookup table (the csv file with the OLD and NEW entries) to have leading and trailing spaces.

Taking the examples in the link you gave; have an entry of"
OLD, NEW
" air ", " water "
" cat ", " bear "

This way, chair will not become chwater and catamaran witll not become bearamaran.

Just a thought?

 

[El Cid]

I think that's what they are doing with their code. I keep getting errors trying to replicate it at the end of my query. Their references are not clear to me yet. I guess I'll keep banging on it until I work out the errors.