Hello,
In trying to tidy up some data, I've come across a really useful function to bulk replace text (see attached). This is fine, but it only replaces strings within each cell and I'd like it to check the entire cell contents only.
For example, in the attached I've put an old value of Red and a new value of xRed. In the output table, I'd like a cell containing "Red" to be changed to "xRed", but a cell containing "Red and White" should stay as "Red and White". At present it changes to "xRed and White".
Could somebody perhaps kindly point me in the right direction to match the entire cell contents?
Very many thanks.
Pieter
Hi Pieter,
You need to perform a logical test to check if the rows in the UpdateColours table = the 'Old' value in the TextSubs query. If TRUE, then you can perform the Replace Text step. The row containing 'Red and white' would not match 'Red', and thus wouldn't be replaced. Remember, this would be case sensitive comparison.
Hope that points you in the right direction.
Mynda
Hi Mynda,
Thanks - that's exactly the prompt I needed to get me going. I'll report back!
Pieter
Hi Pieter,
In case you are interested in an all-in-one version that doesn't require a function, try this script:
let
Source = Excel.CurrentWorkbook(){[Name="tblColours"]}[Content],
FromTo = Excel.CurrentWorkbook(){[Name="tblTextSubs"]}[Content],
Subsitutions =
Table.ToRecords (
Table.Pivot(FromTo, List.Distinct(FromTo[From]), "From", "To")
) {0},
Replace =
Table.TransformColumns (
Source, {
"Colour", each Record.FieldOrDefault(Subsitutions, _,_)
}
)
in
Replace
I found this technique on the web quite a while ago but forgot to note down the web-site. Applied these steps in the attached workbook.
Hi Riny,
Thanks for your post - I now have two ways of approaching the topic, which is fantastic! I'm still trying to get my head around M code, and functions in particular, so this will be really helpful.
Much appreciated.
Pieter
