Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello everyone!
I want to replace any instance of Ltd, whether it's ltd or LTD or Ltd that will appear as part of a string with a nothing. Plus, i have a few other words i want to do this with
I did a simple right click, replace text and have been experimenting with where to put the comparer.orignalignorecase function but unable to determine!
Here's my formula:
= Table.ReplaceValue(#"Renamed Columns","LTD","",Replacer.ReplaceText,{"Supplier (cleansed)"})
Os anyone smart enough to help?
Solved! Go to Solution.
You can use below formula in a custom column
= Text.Combine(List.RemoveMatchingItems(Text.Split([#"Supplier (cleansed)"]," "),{"ltd"},Comparer.OrdinalIgnoreCase)," ")
Hi there,
If you purely want to remove a certain string, the marked solution works fine. If you want to replace the string with something else you can use the method in chapter 6.4 of my article: Replace Values in Power Query M (Ultimate Guide) - Gorilla BI
= Text.Combine(
List.ReplaceMatchingItems (
Text.Split( [Text], " " ) ,
{ { "ValueToReplace", "ValueToReplaceWith" } },
Comparer.OrdinalIgnoreCase ),
" ")
If you only want to remove List.RemoveMatchingItems works perfectly fine 🙂
Regards,
Rick de Groot
--------------------------------------------------
@ me in replies or I'll lose your thread
Master Power Query M? -> https://powerquery.how
Read in-depth articles? -> BI Gorilla
Youtube Channel: BI Gorilla
If this post helps, then please consider accepting it as the solution to help other members find it more quickly.
You can use below formula in a custom column
= Text.Combine(List.RemoveMatchingItems(Text.Split([#"Supplier (cleansed)"]," "),{"ltd"},Comparer.OrdinalIgnoreCase)," ")
YOU SIR, ARE A GENIUS!
Here's one way to do it.
In Transform/Format - make the column lower case
With Replace Values - replace " ltd" with ""
In Transform/Format - choose Capitalize Each Word
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
No i cannot, it ruins the whole column...
Not sure why this one problem is so difficult for you powerbi geniuses!
@doubleclick Try something like:
= Table.ReplaceValue(
#"Changed Type",
each [Consumer_ID],
each if Text.Contains([Consumer_ID] ), "rick",
Comparer.OrdinalIgnoreCase ) then "Rick" else
[Consumer_ID],
Replacer.ReplaceValue,
{"Consumer_ID"}
)
Replace Values in Power Query M (Ultimate Guide) - Gorilla BI
Not sure this works.
Say there's 3 entries:
1) Microsoft LTD
2) Google Ltd
3) Amazon ltd
I want the text replace to replace any instance of ltd with nothing, to delete it so i'm only left with the company name
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
71 | |
63 | |
42 | |
28 | |
22 |