Saturday, May 7, 2022

How to Ignore the Case Sensitivity in Power Query to Remove Duplicates from a Dataset

How to use Comparer.OrdinalIgnoreCase Function in Power Query while Filtering or Removing Duplicates from a Dataset
Scenario:
Suppose we have a Dataset as follows, with duplicated records and different Case Sensitivity.

Now we need to ignore the Case Sensitivity while filtering a Column "Product" and also while removing the Duplicates from the Dataset.
1) Filtering Dataset - by Ignoring the Case Sensitivity of [Product] Column :
Suppose, want to filter all rows where [Product] in {"Radio", "radio", "raDio" , "RADIO"} . This can be done by simply ignoring the Case Sensitivity as per below:

FilterRows
= Table.SelectRows(ds_Dataset, each Comparer.OrdinalIgnoreCase([Product] , "Radio")=0 )

#Result:


2) Remove duplicates from a Dataset - by Ignoring the Case Sensitivity:
It can be achieved by simply ignoring the Case Sensitivity with use of the MQuery Function Comparer.OrdinalIgnoreCase as per below.

RemoveDuplicates
= Table.Distinct(ds_Dataset, Comparer.OrdinalIgnoreCase)

#Result:

#--------------------------------------------------------------Thanks--------------------------------------------------------------#

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.