Forum Discussion
Remove or Hide Duplicate Values
There is a Remove Duplicates step when editing your query, it is in the Reduce Rows section of the ribbon, the M code is:
#"Removed Duplicates" = Table.Distinct(#"[Previous Step]", {"[Column Name]"})
There is a Remove Duplicates step when editing your query, it is in the Reduce Rows section of the ribbon, the M code is:
#"Removed Duplicates" = Table.Distinct(#"[Previous Step]", {"[Column Name]"})
Thanks everyone. Greg_Deckler I think I'm going to go with this solution for the moment as it worked right away. My question is will it stick? For example is it a one time thing or will it remove duplicates every time the SQL data is refreshed?
Yes, query steps stick. When you run a query, it is really just executing a series of steps. You can see the steps recorded on the righthand side. of the query editing window.
Handling this in the source is great, but not always possible. As an end-user focused tool, it is not reasonable to expect that users will have access to the backend to make changes, write stored procedures, expert knowledge to write SQL, etc.
jjmauras Best practice would be to handle this at the source. Per Anonymous solution.
I have done this on a table but then when I try to join to another table on that field I get the error that I can't create the relationship as one of the columns must have unique values. Isn't applying that step getting me to where it's unique or am I missing a step?
Using Table.Distinct, which rows are kept (not removed)?
For instance, if I try to remove duplicates based on a "user_id" column, the first row with each user_id will be kept, and the following rows with that user_id will be removed? (wondering if I need to sort before to make sure I keep the rows I want to keep)
