Forum Discussion
Removing rows with unique values
- 1 year ago
There'll probably be many ways but there are 2 in the attached Excel workbook below.
In the Power Query editor after loading the data:
Version 1
1. Add an index column
2. Group the rows on the fields you want to include as duplicates (not the index column!) creating All Rows and Count
3. Filter out the 1s form the Count column
4. Expand the Tables column but only the index column
5. Optional: Sort on the Index column to retain original source data row order
6. Remove the Count and Index columns.
Version 2
1. Group the rows on the fields you want to include as duplicates creating a Count column
2. Filter the Count column to keep the 1s
3. Remove the Count column (leaves you a table with only rows that occur once)
4. Merge the Source table with the table in step 3
5. Expand this new column choosing any one of the fields (the field chosen must have no nulls)
6. Filter this expanded column for only nulls
7. Remove this expanded column.
There'll probably be many ways but there are 2 in the attached Excel workbook below.
In the Power Query editor after loading the data:
Version 1
1. Add an index column
2. Group the rows on the fields you want to include as duplicates (not the index column!) creating All Rows and Count
3. Filter out the 1s form the Count column
4. Expand the Tables column but only the index column
5. Optional: Sort on the Index column to retain original source data row order
6. Remove the Count and Index columns.
Version 2
1. Group the rows on the fields you want to include as duplicates creating a Count column
2. Filter the Count column to keep the 1s
3. Remove the Count column (leaves you a table with only rows that occur once)
4. Merge the Source table with the table in step 3
5. Expand this new column choosing any one of the fields (the field chosen must have no nulls)
6. Filter this expanded column for only nulls
7. Remove this expanded column.