Replace Multiple Text Strings in One Step

[MichaelJSpence]

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!

[dharmendars007 · Accepted solution]

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

LinkedIN 

[dufoq3 · Accepted solution]

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"

[Omid_Motamedise · Accepted solution]

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=12s

[wdx223_Daniel · Accepted solution]

NewStep=Table.TransformColumns(YourTableName,{"Transaction"},each Text.Combine(List.RemoveItems(Splitter.SplitTextByAnyDelimiter({" ","XLM","STR"})(_),{""})," ")})

[Omid_Motamedise · Accepted solution]

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=12s

[dufoq3 · Accepted solution]

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"

[dharmendars007 · Accepted solution]

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

LinkedIN