Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am looking for a solution to replace multiple substrings at once.
For Example:
Category | Category after |
AA_A | AB_A |
aa_B | AB_B |
AA_C | AB_C |
BB_A | DE_A |
BB_B | DE_B |
BB_C | DE_C |
so AA->AB, aa->AB and BB->DE.
I found something here, but it is not able to replace substrings and it creates three single steps.
My aim would be to do it in one single step.
I'm really looking forward for your feedback.
Solved! Go to Solution.
Hi @Floriankx,
Here are some blogs written about multiple replacements of words with Power Query:
Multiple replacements or translations in Power BI and Power Query
Multiple replacements of words in Power Query
Replace multiple values in a single step
Regards,
Yuliana Gu
I know this is old topic, but maybe it helps to someone...
Let's assume that you have table with replacement pairs called t_Replacements which contains Find and Replace columns:
Then you can add to your table new Custom Column and paste there this code
(Column with your text should have name Column1)
[
v_replacingTable = Table.Distinct(Table.TransformColumns(t_Replacements,{{"Find", Text.Lower, type text}}), "Find"),
v_replacingTableAsListOfLists = Table.ToRows(v_replacingTable),
v_textAsList = Text.Split([Column1], "_"),
v_replacedTextAsList = List.ReplaceMatchingItems(v_textAsList, v_replacingTableAsListOfLists, Comparer.OrdinalIgnoreCase),
v_replacedTextAsText = Text.Combine(v_replacedTextAsList, "_")
][v_replacedTextAsText]
Result:
Note:
If you want to debug this code - just delete [v_replacedTextAsText] after ] at the end of the code and then you can expand the record to see result of each step/variable.
Text.Replace is case sensitive.
Text.Replace([Category],"AA","AB")
To make the three replacements you can nest it.
Text.Replace(Text.Replace(Text.Replace([Category],"BB","DE"),"aa","AB"),"AA","AB")
Hi, @Floriankx
Try the following M formula in Query Editor:
Text.Combine( List.ReplaceMatchingItems(
Text.Split ([Category], ”_“), { {“AA”, “AB”} , {“aa”, ”AB“} , {“BB” , “DE” } }
))
Here is more information to assist you:
https://www.syntelli.com/easy-guide-to-bulk-replace-values-in-power-bi-or-power-query
Let me know if it works!
Hi @Floriankx,
Here are some blogs written about multiple replacements of words with Power Query:
Multiple replacements or translations in Power BI and Power Query
Multiple replacements of words in Power Query
Replace multiple values in a single step
Regards,
Yuliana Gu
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |