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.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog