Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mattabel5
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

@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
camargos88
Community Champion
Community Champion

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!



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?

@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!



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.