Power Query - Replace multiple substrings in one column

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!

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

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.