Remove duplicates with direct query in power query

[chchen0202]

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.

[dufoq3]

@Hi @chchen0202, I have zero experience with Direct Query mode, but have you tried Group By?

[chchen0202]

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!

[dufoq3]

Select All Rows from Operation menu.

[mahenkj2]

Hi @chchen0202 ,

@dufoq3 All rows will bring the table in the grouped column and as soon as we expand it, all the rows of table will re-appear and I understand, we will be having duplicates back.

Just incase situation permits, @chchen0202 can use group by with using aggregates, such as Average, if column of duplicate cell have corresponding same values in duplicated rows.

By the way, whats the limitation in using sql query?

[dufoq3]

You can bring back only top row for each table or you can filter each table with your own conditions - so no duplicates...

[chchen0202]

I tried to use group by and expand all the columns, but it still show that "it's not support Direct Query" .

[v-xinruzhu-msft · Accepted solution]

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

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about#data-transformatio....

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.

[mahenkj2]

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.

[chchen0202]

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!