Dynamically Remove Duplicates

[mattabel5]

I have Power BI connected to my site's database.  I would like to have a table with two columns (AgentID and Office) that will stay current with a refresh.  I need this table to not contain any duplicate values in the AgentID column, but I am not sure how to do it.  I know I can go into Query Editor and remove duplicates, but I believe once I hit refresh, it will not dyanmically remove duplicates from the new data that is pulled in.  I have tried creating a calculated column using DISTINCT, VALUES, and SUMMARIZE and I always get the"A table of multiple values was supplied where a single value was expected." error message.  Does anyone have any idea if this is possible and if so, how to do it?

[camargos88]

Hi @mattabel5 ,

Can you have 1 AgentID with more than 1 office ? If yes, how are you gonna handle it ?

If no, just go to Query Editor and right click on AgentID column and remove duplicates.

Ricardo

[mattabel5]

It is only one office per AgentID.  To give more background on this, I work for a real estate company.  I am pulling in historical information on all of our agents.  For each house that an agent sells, there will be a new row added to the database. 

So let's say I refresh the data from the database and then remove duplicates in Query Editor.  Next week we get a new agent and they sell two houses, creating two new rows in the database and thus creating a new duplicate in the AgentID column.  WIll I have to manually go back in and remove duplicates again, or will it dynmically do it for me?

[camargos88 · Accepted solution]

@mattabel5 ,

It will apply for each refresh. However, the remove duplicates function should be applied for a key column or composite columns.

If you have more columns than AgentID and Office and you apply it only for AgentID...probably you are gonna get wrong values.

You can apply it for more than 1 column.

Ricardo