cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
allen3just Frequent Visitor
Frequent Visitor

Remove Duplicates in One Column in Direct Query

Hello all,

 

I need to filter out the duplicates from one column in a direct query in power query so that my DAX measures for another column doesn't take duplicate values. How would I go about doing this. 

 

The other post that had a solution, I think does not solve my problem since I am not merging tables together. 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Remove Duplicates in One Column in Direct Query

Hi @allen3just 

If you have "today" filter in Power Query to limit the data loaded into Data model, then have filters in data model like (visual level filter, report level filter,, ect),

In this scenario, doing "today" filter before others is useful.

 

If  "today" filter and your other filters are all in Power Query, there is no difference to doing it before or after.

 

If your requirement is to limit only today's data showing on Power BI, 

For a better performance, you can limit data when connecting with SQL Server,

you could use SQL Statement in Connection window/code.

https://community.powerbi.com/t5/Desktop/How-do-I-pass-parameters-to-my-SQL-statement/td-p/118716

https://community.powerbi.com/t5/Desktop/Direct-Query-Filters-in-the-report/td-p/512860

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: Remove Duplicates in One Column in Direct Query

Hi @allen3just 

When "remove duplicates" in Power Query, it doesn't support in direct query mode.

Capture9.JPG

According to your last post,

Capture10.JPG

Do you want to sum the "duration" dax column which is created as suggested here?

It is wrong because its type is text and can't convert to date/time format.

Could you tell me what final result you wanted?

For example

name  seconds     duration 

a          100           00:01:40

b          120           00:02:00

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

a

allen3just Frequent Visitor
Frequent Visitor

Re: Remove Duplicates in One Column in Direct Query

Hello,

 

I have already figured out the solution thank you for checking up on this. One minor question and I'm not sure if this is the place to ask. If I have a direct query and I'm doing the filter by today in power query, I notice that the query takes a long time to load. Is it better to do all my filters before doing the filter by today or start with filter by today before doing the other filters?

Community Support Team
Community Support Team

Re: Remove Duplicates in One Column in Direct Query

Hi @allen3just 

If you have "today" filter in Power Query to limit the data loaded into Data model, then have filters in data model like (visual level filter, report level filter,, ect),

In this scenario, doing "today" filter before others is useful.

 

If  "today" filter and your other filters are all in Power Query, there is no difference to doing it before or after.

 

If your requirement is to limit only today's data showing on Power BI, 

For a better performance, you can limit data when connecting with SQL Server,

you could use SQL Statement in Connection window/code.

https://community.powerbi.com/t5/Desktop/How-do-I-pass-parameters-to-my-SQL-statement/td-p/118716

https://community.powerbi.com/t5/Desktop/Direct-Query-Filters-in-the-report/td-p/512860

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

allen3just Frequent Visitor
Frequent Visitor

Re: Remove Duplicates in One Column in Direct Query

Thanks for your help! 

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 393 members 3,752 guests
Please welcome our newest community members: