Forum Discussion

Dicken's avatar
Dicken
Post Prodigy
27 days ago
Solved

Selecting Rows of table, based on duplicate or not so select all unique rows or not,

Hi,  I want to select rows of a table  where the rows are unique or not , I have a couple of ways of doing this,  group is probably best,   but i would be interested in anyone can suggest other a...
  • jgeddes's avatar
    27 days ago

    This isn't a List.TransformMany solution but it something that should work too...

    let
        atable = #table(
        type table [One = text, Two = text, Three = text],
        {
          {"a", "b", "c"},
          {"b", "c", "d"},
          {"c", "d", "e"},
          {"d", "e", "f"},
          {"b", "c", "d"},
          {"a", "b", "c"},
          {"e", "f", "g"}
        }
      ),
        select_same_rows = 
        Table.SelectRows(
          atable, 
          (r)=> 
          List.Count(
            List.PositionOf(
              Table.ToRows(atable),
              Record.FieldValues(r), 
              Occurrence.All
            )
          ) > 1
        )
    in
        select_same_rows
  • slorin's avatar
    24 days ago

    Hi

     

    let
    atable = Your_Table,
     ToRows = List.Buffer(Table.ToRows(atable)),
    Not_unique = List.Difference(ToRows, List.Distinct(ToRows)),
    Result = Table.SelectRows(
    atable,
    each List.Contains(Not_unique, Record.ToList(_)))
    in
    Result

    Stéphane