Forum Discussion
Replace Multiple Text Strings in One Step
I'm looking to find a way to replace values of several different text strings in one singular step in the query editor.
I've found similar solutions such as if you're replacing whole fields or if the substring you want to remove appears at the end of your text. However, I'm looking to replace substrings that exist throughout the text. Does anyone have a solution for this, or is the only way to do it over multiple steps?
For example, replacing both "XLM" and "STR" with blank values in the below table:
| Person | Transaction | Amount |
| Brian May | Plywood XLM Boards | £175.00 |
| Farrokh Bulsara | Plywood Boards STR | £342.51 |
| John Deacon | XLM Hardwood Boards | £18.79 |
| Roger Taylor | Hardwood STR Boards | £167.42 |
Any help would be greatly appreciated!
Hello MichaelJSpence ,
You can create the custom column with the below code..
Text.Trim(Text.Replace(Text.Replace([Transaction], "XLM", ""), "STR", ""))If you find this helpful , please mark it as solution and Your Kudos are much appreciated!
Thank You
Dharmendar S
Hi MichaelJSpence, I don't understand why do you need sigle step, but here you go:
Result
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcirKTMxT8E2sVNJRCsipLM/PT1GI8PFVcMpPLEopBgoeWmxobqpnYKAUqxOt5JZYVJSfnaHgVJpTnFiUiKQHol4hOCQIrMfYxEjP1BCsxys/I0/BJTUxOT8PKAUy2wOoFEkTxBILPXNLsPqg/PTUIoWQxMqc/CKgFFwx0GgUDWbmeiZGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, Transaction = _t, Amount = _t]), #"Removed XML and STR" = Table.TransformColumns(Source, {{"Transaction", each Text.Combine(List.RemoveMatchingItems(Text.Split(_, " "), {"XLM", "STR"}), " "), type text}}) in #"Removed XML and STR"considering Source is your data, run the below code
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"Transaction", type text}, {"Amount", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",{"XLM","STR"},"",(a,b,c)=> List.Accumulate(b,a,(x,y)=>Text.Replace(x,y,c)),{"Person", "Transaction", "Amount"})
in
#"Replaced Value"
to reach this result
for more explanation about List.Accumulate check my video here
https://www.youtube.com/watch?v=G8PRbWuDcmQ&t=12sNewStep=Table.TransformColumns(YourTableName,{"Transaction"},each Text.Combine(List.RemoveItems(Splitter.SplitTextByAnyDelimiter({" ","XLM","STR"})(_),{""})," ")})
4 Replies
considering Source is your data, run the below code
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"Transaction", type text}, {"Amount", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",{"XLM","STR"},"",(a,b,c)=> List.Accumulate(b,a,(x,y)=>Text.Replace(x,y,c)),{"Person", "Transaction", "Amount"})
in
#"Replaced Value"
to reach this result
for more explanation about List.Accumulate check my video here
https://www.youtube.com/watch?v=G8PRbWuDcmQ&t=12sHello MichaelJSpence ,
You can create the custom column with the below code..
Text.Trim(Text.Replace(Text.Replace([Transaction], "XLM", ""), "STR", ""))If you find this helpful , please mark it as solution and Your Kudos are much appreciated!
Thank You
Dharmendar S
Hi MichaelJSpence, I don't understand why do you need sigle step, but here you go:
Result
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcirKTMxT8E2sVNJRCsipLM/PT1GI8PFVcMpPLEopBgoeWmxobqpnYKAUqxOt5JZYVJSfnaHgVJpTnFiUiKQHol4hOCQIrMfYxEjP1BCsxys/I0/BJTUxOT8PKAUy2wOoFEkTxBILPXNLsPqg/PTUIoWQxMqc/CKgFFwx0GgUDWbmeiZGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, Transaction = _t, Amount = _t]), #"Removed XML and STR" = Table.TransformColumns(Source, {{"Transaction", each Text.Combine(List.RemoveMatchingItems(Text.Split(_, " "), {"XLM", "STR"}), " "), type text}}) in #"Removed XML and STR"NewStep=Table.TransformColumns(YourTableName,{"Transaction"},each Text.Combine(List.RemoveItems(Splitter.SplitTextByAnyDelimiter({" ","XLM","STR"})(_),{""})," ")})