Forum Discussion
Remove duplicates with direct query in power query
Hi all,
I am a beginner in Power BI.
When I was testing a table with direct query, I encountered an error message when trying to 【Remove duplicates】.
"This step results in a query that is not supported in DirectQuery mode."
Is it possible to use the 【Remove duplicates】 in this situation(table with direct query mode in Power query)? or it's a limitation?
Also, I understand that I can achieve the same functionality by using the distinct keyword in SQL statement when inputting data. However, is there an alternative way to solve this issue without using SQL in Power query?
Thank you in advance. Best regards.
Hi,
Thanks for the solutions dufoq3 and mahenkj2 provided, and i want to offer some more information for user to refer to.
hello chchen0202 , the direct query mode is connect to the data source directly, so if you use the step like remove the duplicate rows, it will change the data structure of the data source directly , it can not support, it is better that if you want to use full operations in power query, it is better that you change your connection mode from direct query to import, the import mode will temporarily store the data in Power BI Desktop, where the data is not associated with the data source, you can use the full Power Query functionality, if you don't want to change the import mode, then you better remove the duplicate values in SQL. and you can remove the following link
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
Hi chchen0202
There are such limitation in Direct query. You can qrite SQL statement in Advanced options while connecting to server. If you are going to use this table to create relationships then I am afraid that removing duplicates will be a challenge at PQ level, yet if just want to remove to see in the visuals, there are ways to use Measures or filters.
By now, you may have reach upto here, but just incase:
https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about
Hope it helps.
Thank you for your reply.
I also think it's a limitation in Direct query. I just want to filter the duplicate record befoe other transfer step in PQ, just like merge other table.
For some reason, I need to let this step in PQ, insted of measures or filters in visual level. Thanks!
@Hi chchen0202, I have zero experience with Direct Query mode, but have you tried Group By?
Thank you for your reply.
You mean this group by in PQ?
If yes, I have tried it before, but it necessary to do some aggregate function.
I think it's not the correct data I expect. Thanks!
Select All Rows from Operation menu.
