Forum Discussion
Selecting Rows of table, based on duplicate or not so select all unique rows or not,
- 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 - 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
ResultStéphane
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
- Dicken24 days agoPost Prodigy
Had not thought about list.difference.
- Dicken23 days agoPost 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;- v-abhinavmu22 days agoCommunity Support
Hi Dicken,
Thanks for sharing your observations and thanks to slorin & jgeddes for sharing valuable insights. The following Microsoft Learn references describe the behavior of the functions used in the solution:
• Table.SelectRows returns the rows that match the specified selection condition.
• Record.FieldValues returns a list of the field values in a record.
• List.Contains indicates whether a list contains the specified value, returning true if the value is found and false otherwise.
• List.Difference returns the items in list1 that do not appear in list2. The documentation also notes that duplicate values are supported.
For more details, please refer to the below official Microsoft documentation:
Table.SelectRows - PowerQuery M | Microsoft Learn
Record.FieldValues - PowerQuery M | Microsoft Learn
List.Contains - PowerQuery M | Microsoft Learn
List.Difference - PowerQuery M | Microsoft Learn
I hope this helps. Please feel free to reach out if you have any further questions.
Thank you.