Answered by:
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
Answers

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"
}) Proposed as answer by Curt Hagenlocher Wednesday, November 2, 2016 2:07 PM
 Unproposed as answer by Imke FeldmannMVP, Moderator Wednesday, November 2, 2016 2:56 PM
 Marked as answer by Imke FeldmannMVP, Moderator 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
 Marked as answer by Imke FeldmannMVP, Moderator 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"
}) Proposed as answer by Curt Hagenlocher Wednesday, November 2, 2016 2:07 PM
 Unproposed as answer by Imke FeldmannMVP, Moderator Wednesday, November 2, 2016 2:56 PM
 Marked as answer by Imke FeldmannMVP, Moderator Wednesday, November 2, 2016 7:16 PM

Sorry, my bad.
Forgot to say that the question is about how to combine this with the columnselection: 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

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
 Marked as answer by Imke FeldmannMVP, Moderator 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

thx Colin, but that was not me alone (besides you kicking it all off... , Curt & also: http://community.powerbi.com/t5/Desktop/quotRemoveDuplicatequotdoesntremoveallduplicate/mp/85262#M35743 )
Imke Feldmann TheBIccountant.com


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 :)

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
 Edited by Imke FeldmannMVP, Moderator Friday, November 4, 2016 8:49 PM