Forum Discussion
Case insensitive replacement in Power Query
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.
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.
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
4 Replies
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.
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
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.
