Forum Discussion
Count duplicates across distinct records
Hi Anonymous ,
I have a solution with just the Power Query editor, although depending on the size of your dataset it might not be the best performance wise:- Duplicate your query.
- Select the ABN and Vendor ID columns and remove duplicate rows.
- Group by ABN and for the value use Count Rows. You should have a table that contains each ABN once and counts how many unique Vendor IDs they were listed for.
- Merge back to the original query on the ABN column.
Alternatively you could try referencing your original query instead of duplicating. This way you wont be able to merge it back, but you could load both tables and then use LOOKUPVALUES in a calculated column to get the count into your original table. I am honestly not sure which method would be better for performance.
I hope this helps, let me know if you have any questions.
Super UserHi Anonymous ,
I have a solution with just the Power Query editor, although depending on the size of your dataset it might not be the best performance wise:
- Duplicate your query.
- Select the ABN and Vendor ID columns and remove duplicate rows.
- Group by ABN and for the value use Count Rows. You should have a table that contains each ABN once and counts how many unique Vendor IDs they were listed for.
- Merge back to the original query on the ABN column.
Alternatively you could try referencing your original query instead of duplicating. This way you wont be able to merge it back, but you could load both tables and then use LOOKUPVALUES in a calculated column to get the count into your original table. I am honestly not sure which method would be better for performance.
I hope this helps, let me know if you have any questions.
Thanks very much dk_dk , it worked a treat 🙂