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.

[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

[tebow]

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!