Forum Discussion
Remove duplicates with direct query in power query
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.
Select All Rows from Operation menu.
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?
You can bring back only top row for each table or you can filter each table with your own conditions - so no duplicates...
I tried to use group by and expand all the columns, but it still show that "it's not support Direct Query" .
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.
