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?
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?