Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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!
Solved! Go to 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
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=12s
NewStep=Table.TransformColumns(YourTableName,{"Transaction"},each Text.Combine(List.RemoveItems(Splitter.SplitTextByAnyDelimiter({" ","XLM","STR"})(_),{""})," ")})
NewStep=Table.TransformColumns(YourTableName,{"Transaction"},each Text.Combine(List.RemoveItems(Splitter.SplitTextByAnyDelimiter({" ","XLM","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=12s
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"
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
17 | |
9 | |
8 | |
7 | |
7 |