cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SruthiSVedula
New Member

Remove duplicates in direct query

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

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
JayHerrera
Frequent Visitor

Re: Remove duplicates in direct query

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

View solution in original post

Microsoft
Microsoft

Re: Remove duplicates in direct query

@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,

View solution in original post

2 REPLIES 2
JayHerrera
Frequent Visitor

Re: Remove duplicates in direct query

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

View solution in original post

Microsoft
Microsoft

Re: Remove duplicates in direct query

@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,

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors