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 sentence contains any text in the [Contain] column then it will replace the sentence with the corresponding text in the [Replace] column, otherwise it will just return the same sentence:
| Contain | Replace |
| CAT | DOG |
| FRUIT | VEGE |
The expected results table is as below:
| Sentence |
| DOG |
| VEGE |
| DOG |
| SKY IS BLUE |
I really need this function as there is a long list of text to lookup and replace.
Thanks for the advise and help in advance.
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)
4 Replies
Hi Anonymous
If you sentence all have " " as delimiter, and only match the first Contain keyword, then here is one way
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8lRw9FVwdgxRitWBctyCQj0h3BAP1yBXBc9gBUeQCgVPPwWgiEKQv78vWDrYOxIk6eQT6qoUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Sentence = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sentence", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Replace", each try Replace[Replace]{List.PositionOf(Replace[Contain], List.Intersect({ Text.Split([Sentence]," "), Replace[Contain]}){0}?)} otherwise [Sentence]) in #"Added Custom"I have the Replace as another table
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PK0nMzFPSUQpKLchJTE5VitUBijqGAEVc/N3BPLegUE8QP8zV3VUpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Contain", type text}, {"Replace", type text}}) in #"Changed Type"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})))
