# Case insensitive Table.Distinct

• ### Question

• Is it possible to define the Table.Distinct in a way that it would ignore the case like it is done in List.Distinct like this?:

` List.Distinct({"AA", "Aa", "Ba", "BA"}, Comparer.OrdinalIgnoreCase)`

Imke Feldmann TheBIccountant.com

Wednesday, November 2, 2016 1:02 PM

• Table.Distinct supports the same argument:

M> Table.Distinct(Table.FromRecords({[A="one", B=1], [A="ONe", B=1]}))
Table.FromRecords({ [
A = "one",
B = 1
], [
A = "ONe",
B = 1
]
}, {
"A",
"B"
})

M> Table.Distinct(Table.FromRecords({[A="one", B=1], [A="ONe", B=1]}), Comparer.OrdinalIgnoreCase)
Table.FromRecords({ [
A = "one",
B = 1
]
}, {
"A",
"B"
})

Wednesday, November 2, 2016 2:06 PM
• Looks like this is solved now:

`= Table.Distinct(Table.FromRecords({[A="one", B=1, C=2], [A="ONe", B=1, C=3]}), {{"A", Comparer.OrdinalIgnoreCase}, {"B", Comparer.OrdinalIgnoreCase}} )`

Imke Feldmann TheBIccountant.com

Wednesday, November 2, 2016 7:16 PM

### All replies

• Table.Distinct supports the same argument:

M> Table.Distinct(Table.FromRecords({[A="one", B=1], [A="ONe", B=1]}))
Table.FromRecords({ [
A = "one",
B = 1
], [
A = "ONe",
B = 1
]
}, {
"A",
"B"
})

M> Table.Distinct(Table.FromRecords({[A="one", B=1], [A="ONe", B=1]}), Comparer.OrdinalIgnoreCase)
Table.FromRecords({ [
A = "one",
B = 1
]
}, {
"A",
"B"
})

Wednesday, November 2, 2016 2:06 PM

Forgot to say that the question is about how to combine this with the column-selection: If we just want to remove the rows where A and B are the same here:

Table.FromRecords({[A="one", B=1, C=2], [A="ONe", B=1, C=3]})

So that again, only the first row will be returned.

Thanks again!

Imke Feldmann TheBIccountant.com

Wednesday, November 2, 2016 2:58 PM
• Looks like this is solved now:

`= Table.Distinct(Table.FromRecords({[A="one", B=1, C=2], [A="ONe", B=1, C=3]}), {{"A", Comparer.OrdinalIgnoreCase}, {"B", Comparer.OrdinalIgnoreCase}} )`

Imke Feldmann TheBIccountant.com

Wednesday, November 2, 2016 7:16 PM
• Looks like this is solved now:

`= Table.Distinct(Table.FromRecords({[A="one", B=1, C=2], [A="ONe", B=1, C=3]}), {{"A", Comparer.OrdinalIgnoreCase}, {"B", Comparer.OrdinalIgnoreCase}} )`

Imke Feldmann TheBIccountant.com

Great solution, Imke. :)
Thursday, November 3, 2016 7:23 PM
• thx Colin, but that was not me alone (besides you kicking it all off... , Curt & also: http://community.powerbi.com/t5/Desktop/quot-Remove-Duplicate-quot-doesn-t-remove-all-duplicate/m-p/85262#M35743 )

Imke Feldmann TheBIccountant.com

Thursday, November 3, 2016 9:06 PM
• A great find, nonetheless. There's not the slightest inkling in the documentation sources for the function that remotely suggest that setting the optional equation criteria like that is possible.
Thursday, November 3, 2016 9:35 PM
• Hi Imke :-)

A little while ago I wrote an own function Table.Distinct.
Maybe, it will be useful for someone.
I did this because I wanted to have a possibility to change original Table.Distinct function in easy way - simply do the distinct from UI and then it is easy manually change formula in formula bar.

```  (tbl as table, optional arg as any) as any =>

let
fxTD = (t as table, lst as list) as table =>
let
SelectNumbers = List.Select(lst, each (_=0) or (_=1)),
CheckLst = if List.IsEmpty(SelectNumbers)
then
if List.Last(lst) is function
then Table.Distinct(t, fxChangeList1(lst))
else Table.Distinct(t, lst)
else
Table.Distinct(t,fxChangeList2(lst, SelectNumbers))
in
CheckLst,
//------------------------------------------------------------------------------------------------------
// List not contains numbers and the last item is a function

fxChangeList1 = (lst as list) as list =>
let

ListRemoveLast = List.RemoveLastN(lst,1),
Function = List.Last(lst),
Argument = List.Transform(ListRemoveLast, each {_, Function} )
in
Argument,

//------------------------------------------------------------------------------------------------------
// List contains numbers or/and the last item is number

fxChangeList2 = (lst as list, numbers as list) as list =>
let
fxLast = (lst as list) as list =>
let
ListToCheck = List.Transform(lst, each if _ is number then if _ = 0 then Comparer.OrdinalIgnoreCase else Comparer.Ordinal else _),
Argument = List.Transform(List.Alternate(List.Positions(ListToCheck),1,1,1),each  {ListToCheck{_}, ListToCheck{_+1}})
in
Argument,

LastStep = if List.Count(numbers)=1 and List.Last(lst) is number
then fxLast(List.Combine(List.Transform(List.RemoveLastN(lst,1), each {_}& {List.Last(lst)})))
else fxLast(lst)
in
LastStep ,

//------------------------------------------------------------------------------------------------------
//------Start-----
CheckArg = if arg = null
then Table.Distinct(tbl)
else
if arg is function or arg is text
then Table.Distinct(tbl, arg)
else fxTD(tbl, arg)
in
CheckArg,```

Examples of using  fxTable.Distinct(table, something as any)

fxTable.Distinct(Source, "A")  is equivalent of   Table.Distinct(Source, "A")

fxTable.Distinct(Source, {"A","C"}) is equivalent of Table.Distinct(Source, {"A","C"})

fxTable.Distinct(Source, Comparer.OrdinalIgnoreCase) is equivalent of Table.Distinct(Source, Comparer.OrdinalIgnoreCase)

fxTable.Distinct(Source, {"A",0,"C",1}) is equivalent of Table.Distinct(Source, {{"A",Comparer.OrdinalIgnoreCase},{"C",Comparer.Ordinal}})

fxTable.Distinct(Source, {"A","C",0}) is equivalent of Table.Distinct(Source, {{"A",Comparer.OrdinalIgnoreCase},{"C",Comparer.OrdinalIgnoreCase}})

fxTable.Distinct(Source, {"A","C", Comparer.OrdinalIgnoreCase}) is equivalent of Table.Distinct(Source, {{"A",Comparer.OrdinalIgnoreCase},{"C",Comparer.OrdinalIgnoreCase}})

"A" and "C" are names of columns (this is an example only) and 0 is an equivalent of Comparer.OrdinalIgnoreCase and 1 is equivalent of  Comparer.Ordinal.

Regards :-)

Friday, November 4, 2016 4:58 PM
• Hm... - how comes that this doesn't surprise me at all? :-)

Very cool!

@Colin & @Bill:

Do you think that GitHub would be a good place to share functions between fans like this:  https://github.com/tycho01/pquery ?

I must admit that it scares me a bit, but so far haven't found a better place for it. Or do you have a better suggestion for it?

Imke Feldmann TheBIccountant.com

Friday, November 4, 2016 8:24 PM