none
Case insensitive Table.Distinct RRS feed

  • 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
    Moderator

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"
    })

    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
    Moderator

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
  • Sorry, my bad.

    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
    Moderator
  • 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
    Moderator
  • 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
    Moderator
  • 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
    Moderator