Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
MichaelJSpence
New Member

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: 

 

PersonTransactionAmount
Brian MayPlywood XLM Boards£175.00
Farrokh BulsaraPlywood Boards STR£342.51
John DeaconXLM Hardwood Boards£18.79
Roger TaylorHardwood STR Boards£167.42

 

Any help would be greatly appreciated!

 

 

 

4 ACCEPTED SOLUTIONS
dharmendars007
Super User
Super User

Hello @MichaelJSpence , 

 

You can create the custom column with the below code..

Text.Trim(Text.Replace(Text.Replace([Transaction], "XLM", ""), "STR", ""))

 

dharmendars007_0-1725453126233.png

 

dharmendars007_1-1725453162852.png

 

If you find this helpful , please mark it as solution and Your Kudos are much appreciated!

 

Thank You

Dharmendar S

LinkedIN 

 

View solution in original post

dufoq3
Super User
Super User

Hi @MichaelJSpence, I don't understand why do you need sigle step, but here you go:

 

Result

dufoq3_0-1725453292471.png

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"

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

Omid_Motamedise
Super User
Super User

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

Omid_Motamedise_0-1725488593187.png



for more explanation about List.Accumulate check my video here
https://www.youtube.com/watch?v=G8PRbWuDcmQ&t=12s

If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h

View solution in original post

wdx223_Daniel
Super User
Super User

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

View solution in original post

4 REPLIES 4
wdx223_Daniel
Super User
Super User

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

Omid_Motamedise
Super User
Super User

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

Omid_Motamedise_0-1725488593187.png



for more explanation about List.Accumulate check my video here
https://www.youtube.com/watch?v=G8PRbWuDcmQ&t=12s

If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h
dufoq3
Super User
Super User

Hi @MichaelJSpence, I don't understand why do you need sigle step, but here you go:

 

Result

dufoq3_0-1725453292471.png

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"

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

dharmendars007
Super User
Super User

Hello @MichaelJSpence , 

 

You can create the custom column with the below code..

Text.Trim(Text.Replace(Text.Replace([Transaction], "XLM", ""), "STR", ""))

 

dharmendars007_0-1725453126233.png

 

dharmendars007_1-1725453162852.png

 

If you find this helpful , please mark it as solution and Your Kudos are much appreciated!

 

Thank You

Dharmendar S

LinkedIN 

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors