Remove duplicates in direct query

[SruthiSVedula]

Hi,

I am a PowerBI begginer and lot of this stuff is still new for me. Please suggest me a solution for the following issue. Thanks in Advance.

I initially had two tables that I merged into one in a direct query connection. This created a lot of duplicates for some reason and when I try to remove the duplicate rows ( option present in the Query Editor menu) it gives the following message,

"This step results in a query that is not Supported in DirectQuery mode"

How do I still stay in the DirectQuery mode and still remove the duplicate rows?

Thanks again

[JayHerrera · Accepted solution]

Are you using a "union" or "union all" in your query to combine the data from two tables into one? If so, the difference between UNION and UNION ALL is that UNION ALL will bring back all records regardless and a UNION effectively does a SELECT DISTINCT on the results set. If you are querying one table, then a SELECT DISTINCT will get unique records from this table.

Reference:

https://www.google.com/search?q=SQL+union+vs+union+all&spell=1&sa=X&ved=0ahUKEwj_pqDCqPHVAhWMmbQKHd1...

[v-sihou-msft · Accepted solution]

@SruthiSVedula

Since you are under Direct Query mode, any transform step which can modify the source table structure is not allowed in Query Editor. So for your requirement, please custom a query and use DISTINCT keyword  to remove the duplicate rows.

Regards,

[JayHerrera · Accepted solution]

Are you using a "union" or "union all" in your query to combine the data from two tables into one? If so, the difference between UNION and UNION ALL is that UNION ALL will bring back all records regardless and a UNION effectively does a SELECT DISTINCT on the results set. If you are querying one table, then a SELECT DISTINCT will get unique records from this table.

Reference:

https://www.google.com/search?q=SQL+union+vs+union+all&spell=1&sa=X&ved=0ahUKEwj_pqDCqPHVAhWMmbQKHd1...