Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Power Query - Bulk replace value, if it contain a value from a list, with a new value

Dear kind soul,   I have a table of sentences: Sentence I AM CAT I AM FRUIT THERE IS A CAT IN THE ROOM SKY IS BLUE   I would like to loop through the following table, if the se...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    Thank you very much for your time and guidance.

     

    I noted that your solution works if the text in the [Contain] Column only has 1 word. It does not cater for say, CAT RAGE => DOG.

     

    I managed to find a solution for the above on YouTube by improvising Chandoo find replace method. Add one custom column and paste the following:

     

    List.Accumulate(
    List.Numbers(0, Table.RowCount(Replace)),
    [Sentence],
    (state, current) =>
    if Text.Contains(state,
    Replace[Contain]{current}) then
    Replace[Replace]{current} else state)

     

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous ,

 

Thank you very much for your time and guidance.

 

I noted that your solution works if the text in the [Contain] Column only has 1 word. It does not cater for say, CAT RAGE => DOG.

 

I managed to find a solution for the above on YouTube by improvising Chandoo find replace method. Add one custom column and paste the following:

 

List.Accumulate(
List.Numbers(0, Table.RowCount(Replace)),
[Sentence],
(state, current) =>
if Text.Contains(state,
Replace[Contain]{current}) then
Replace[Replace]{current} else state)

 

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous 

 

It's cool, I managed to adopt it for my own use. Thank you