cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User I
Super User I

Identifying Duplicates by not taking Case Sensitivity into consideration - Power BI/Power Query

Hi ,

 

Is there a way Power BI can identify entries in a column as duplicates by taking into consideration the case sensitivity of the entires. I have attached a screenshot of what I want Power BI to achieve.Capture.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

Thank you in advance.

 

 

 

 

 

 

 

 

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Identifying Duplicates by taking Case Sensitivity into consideration - Power BI/Power Query

@smpa01 right, so you dont want it to take the case sensitivity into account you mean?

 

there are 2 ways of dealing with this

 

if you going to work it out in power query (m) then change all text to either upper or lower case

if you going to do it in dax (which ignores it)  just do a count unique = distinctcount(columnname) and place the value and the measure in your visual.  dax will ignore the different cases





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Community Champion
Community Champion

Re: Identifying Duplicates by taking Case Sensitivity into consideration - Power BI/Power Query

If you want to do it "the Power Query way", just group on data and adjust the generated code as illustrated:

Group case insensitive.png

 

Note: the resulting values in the Data column will be just one of the upper/lower case data values from the source (the one that comes first). You may still want to change that to upper or lower as the next step.

 

Full syntax of Table.Group:

Table.Group(table as table, key as any, aggregatedColumns as list, optional groupKind as nullable number, optional comparer as function) as table

You don't need groupKind.

 

Specializing in Power Query Formula Language (M)

View solution in original post

13 REPLIES 13
Highlighted
Super User II
Super User II

Re: Identifying Duplicates by taking Case Sensitivity into consideration - Power BI/Power Query

@smpa01 power query takes case sensitivity into consideration -  if you using imported mode, i suggest doing a group by in power query (m)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User I
Super User I

Re: Identifying Duplicates by not taking Case Sensitivity into consideration - Power BI/Power Query

Highlighted
Super User II
Super User II

Re: Identifying Duplicates by taking Case Sensitivity into consideration - Power BI/Power Query

@smpa01 ok, is that wrong or right?  explain more in detail if there is an issue





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User I
Super User I

Re: Identifying Duplicates by not taking Case Sensitivity into consideration - Power BI/Power Query

Thanks for your reply. I have attached a screenshot of what I want to achieve. I want PBI to return the count AB and ab as 2 and CD and cd count as 2

Highlighted
Super User II
Super User II

Re: Identifying Duplicates by taking Case Sensitivity into consideration - Power BI/Power Query

@smpa01 right, so you dont want it to take the case sensitivity into account you mean?

 

there are 2 ways of dealing with this

 

if you going to work it out in power query (m) then change all text to either upper or lower case

if you going to do it in dax (which ignores it)  just do a count unique = distinctcount(columnname) and place the value and the measure in your visual.  dax will ignore the different cases





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Community Champion
Community Champion

Re: Identifying Duplicates by taking Case Sensitivity into consideration - Power BI/Power Query

If you want to do it "the Power Query way", just group on data and adjust the generated code as illustrated:

Group case insensitive.png

 

Note: the resulting values in the Data column will be just one of the upper/lower case data values from the source (the one that comes first). You may still want to change that to upper or lower as the next step.

 

Full syntax of Table.Group:

Table.Group(table as table, key as any, aggregatedColumns as list, optional groupKind as nullable number, optional comparer as function) as table

You don't need groupKind.

 

Specializing in Power Query Formula Language (M)

View solution in original post

Highlighted
Super User I
Super User I

Re: Identifying Duplicates by not taking Case Sensitivity into consideration - Power BI/Power Query

Highlighted
Super User I
Super User I

Re: Identifying Duplicates by taking Case Sensitivity into consideration - Power BI/Power Query

Highlighted
Super User I
Super User I

Re: Identifying Duplicates by not taking Case Sensitivity into consideration - Power BI/Power Query

@MarcelBeugI have a question regarding Comparer.OrdinalIgnoreCase. Can I apply this to Replace Values.

 

For Example I have a column called NAME

 

cell A1 contains SAM and cell A2 contains  sam

 

How can I use Replace Values Function to replace SAM/sam with 1. I could not employ Comparer.OrdinalIgnoreCase to replace values.

 

Thank you in advance.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors