Case insensitive replacement in Power Query

[Ashish_Mathur]

Hi,

This M code replaces old value with new values in the Particulars column

= List.Accumulate({0..List.Count(rename_particulars[Old name])-1},#"Removed line feed",(state,current)=>Table.ReplaceValue(state,rename_particulars[Old name]{current},rename_particulars[New name]{current},Replacer.ReplaceText,{"Particulars"}))

Rename_particulars is a 2 column table with wrong spellings in the Old name column and correct spellings in the new name column.  So this code iterates through each entry in the Particulars column of the current table, find the wrong spellings in the Odl name column and replaces them wit the correct spellings in the new name column.  However, this is case sensitive.  What modificiation can i make in this to make it case insensitive?

Thank you.

[rohit1991 · Accepted solution]

Hii @Ashish_Mathur 

Power Query text replacements are case-sensitive by default. To make them case-insensitive, we normalize both the source text and lookup values using Text.Upper or Text.Lower, and apply replacements via Table.TransformColumns instead of Replacer.ReplaceText.

[v-priyankata]

Hi @Ashish_Mathur 

Thank you for reaching out to the Microsoft Fabric Forum Community.

@rohit1991 @Tahreem24 Thanks for the inputs.

I hope the information provided by users was helpful. If you still have questions, please don't hesitate to reach out to the community. We are glad to help.

Thanks.

[v-priyankata]

Hi @Ashish_Mathur 

Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.

[Tahreem24 · Accepted solution]

@Ashish_Mathur Try this "Comparer.OrdinalIgnoreCase" . This particular function ignores case and applies ordinal rules for comparison.

https://learn.microsoft.com/en-us/powerquery-m/comparer-ordinalignorecase

[rohit1991 · Accepted solution]

Hii @Ashish_Mathur 

Power Query text replacements are case-sensitive by default. To make them case-insensitive, we normalize both the source text and lookup values using Text.Upper or Text.Lower, and apply replacements via Table.TransformColumns instead of Replacer.ReplaceText.