How to Remove Duplicate Values from PowerBI Direct Query Table

[Anonymous]

Hi, 
I brought in a table from SQL database using DirectQuery (needed to solution a PowerApp inside the PBI visual).

The PBI visual is a table listing projects, each project has multiple attributes with each attribute appearing as a column in the table visual.

However, the data doesn't appear to be "clean". For example, I noticed that one project has 2 rows, every attribute about the project is identical for the 2 rows EXCEPT the Project Director name - there are 2 names, so the project appears twice (in 2 rows).

I understand that with DirectQuery you cannot create steps such as removing duplicates.
HOW can I remove duplicate records then when it is DirectQuery mode?

Thank you!

[mahoneypat · Accepted solution]

For long term, I would talk to your DBA to get the data in the format you need. In the short term, if you can't switch to import mode (recommended to get the flexibilty you need and improve performance), you can use the SUMMARIZE function in your measures to get the distinct combination of the fields you need (e.g., leave out the Director field).

Pat

[Anonymous]

Thanks Pat. I think I will go with the DBA route, it seems cleaner. Thanks again!

[danextian]

Hi @Anonymous ,

Please try the proposed solutions in this URL: https://community.powerbi.com/t5/Desktop/Remove-duplicates-in-direct-query/m-p/238430