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


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————————————————————–#



Source link

Be the first to comment

Leave a Reply

Your email address will not be published.


*