Forum Discussion

Dicken's avatar
Dicken
Post Prodigy
29 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 approaches, not neccessarily better  just different,

so group ; 

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"}
    }
  ),
  group = Table.Group(
    atable,
    Table.ColumnNames(atable),
    {{"n", each if Table.RowCount(_) > 1 then _ else null}}
  )[n],
  result = Table.Combine(List.RemoveNulls(group))
in
  result


or  Accumulate with range ; 

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"}
    }
  ),
  combi =
    let
      tr    = Table.ToRows(atable),
      combi = List.Transform(tr, (x) => Text.Combine(x))
    in
      combi,
  tf = List.Transform(combi, (x) => List.Count(List.PositionOf(combi, x, Occurrence.All)) > 1),
  pos = List.PositionOf(tf, true, Occurrence.All),
  Custom1 = List.Accumulate(pos, #table({}, {}), (s, c) => s & Table.Range(atable, c, 1))
in
  Custom1


I was wondering if i could use List.TransformMany to simplify but i don't think so, so any ( helpful ) suggestions welcome. 

Richard. 

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

8 Replies

  • 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
    • Dicken's avatar
      Dicken
      Post Prodigy

      Thanks, as i  said, looking for different ideas as much as anything, ai  ( and i know it can be wrong) 
      says table method about as effecient as it can get. 

  • 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

    • Dicken's avatar
      Dicken
      Post Prodigy

      Had not thought about list.difference. 

      • Dicken's avatar
        Dicken
        Post Prodigy

        Hi, think this very clever, I'm stiill not quite sure how it iterates, i did as add column, and 
        a manual,   list so ; 
        = Table.AddColumn( Source, "N", each
        List.Contains( {{"a","b","c","d"}},
        Record.FieldValues(_) ) )

        so Table.select. iterates row by row, and as   list contains is a nested list it is taken as a whole,

        = Table.AddColumn( Source, "N", each
        List.Contains( distinctlists,
        Record.FieldValues(_) ) )

        i could not understand why Listcontian ALL was not needed, given me lots to mess around with;