Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
allen3just
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

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
v-juanli-msft
Community Support
Community Support

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

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?

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.

 

Thanks for your help! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.