Dicken
29 days agoPost Prodigy
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_rowsHi
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
ResultStéphane