Forum Discussion
Power Query - Bulk replace value, if it contain a value from a list, with a new value
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)
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)
Hi Anonymous
It's cool, I managed to adopt it for my own use. Thank you