Power Query - Replace multiple substrings in one column

[Floriankx]

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.

[dufoq3]

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.

[Lawrenceiow]

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")

[Anonymous]

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!

[v-yulgu-msft · Accepted 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