Forum Discussion
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...
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)
b = = {{"CAT","DOG"},{"FRUIT","VEGE"}} ---Need replace text as a list
a = Table.AddColumn(Source,"Result",each List.Accumulate({0..(List.Sum(b)-1)},[Sensense],(x,y)=>Text.Replace(x,b{y}{0},b{y}{1})))