Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
smpa01
Super User
Super User

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.

 

 

 

 

 

 

 

 

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
2 ACCEPTED SOLUTIONS

@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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




View solution in original post

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

14 REPLIES 14
vanessafvg
Super User
Super User

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




This is what I see

Capture90.JPG

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Capture111.JPG

 

Awesome @vanessafvg Thanks!!

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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)

This is the answer I was looking for.   The tagged "Solution" was not helpful.

 

Add this before the last parenthesis in the Table.Group function:

, type number}},null,Comparer.OrdinalIgnoreCase

 

Capture 211.JPGThis is awesome !!! Thanks @MarcelBeug

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@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.

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

The syntax for Table.ReplaceValues is:

 

Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list) as table

 

The standard replacer functions, Replacer.ReplaceText and Replacer.ReplaceValue, don't have a comparer parameter:

 

Replacer.ReplaceText(text as nullable text, old as text, new as text) as nullable text
Replacer.ReplaceValue(value as any, old as any, new as any) as any

 

A solution is to use Table.ReplaceValue with a custom replacer function , e.g.:

 

= Table.ReplaceValue(Source,"sam",1,(value,old,new) => if Comparer.Equals(Comparer.OrdinalIgnoreCase,value,old) then new else value,{"NAME"})

 

Specializing in Power Query Formula Language (M)

@MarcelBeug thanks for the solution. However, I could not write the code as you suggested. Can you please help.

Thank you in advance.
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

That will be difficult, blindfolded.

 

So please share what you tried so far and I may be able to help you.

Specializing in Power Query Formula Language (M)

Hi @MarcelBeug

 

You mentioned

= Table.ReplaceValue(Source,"sam",1,(value,old,new) => if Comparer.Equals(Comparer.OrdinalIgnoreCase,value,old) then new else value,{"NAME"}) as a solution.

 

I tried the following.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NAME", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","sam","1",Replacer.ReplaceText,{"NAME"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each if Comparer.Equals(Comparer.OrdinalIgnoreCase,[NAME],"sam") then 1 else [NAME]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}})
in
    #"Changed Type1"

It did the job.  Did you mean the same ?

 

Thanks

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.