cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Dynamically Remove Duplicates

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

@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



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

3 REPLIES 3
Highlighted
Super User III
Super User III

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



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Highlighted

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?

Highlighted

@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



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors