Forum Discussion

chchen0202's avatar
chchen0202
Frequent Visitor
2 years ago
Solved

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 no...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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-transformation-limitations.

     

    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.

dufoq3's avatar
dufoq3
Community Champion
2 years ago

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

chchen0202's avatar
chchen0202
Frequent Visitor
2 years ago

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

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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-transformation-limitations.

     

    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.