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

Power query Remove Duplicates with two sorting tables

Hi,

I have a problem with remove duplicates.
I want to remove duplicates, but sorting should be on two tables:
with one table it is working, but with two not.
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Created_Bugs.key"}) - works!
but

#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {{"Created_Bugs.key", Order.Ascending}, {"task key", Order.Ascending}}) - doesnt work.
Is there possibility to remove with two sorting?


Thanks for help!
elwira

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Power query Remove Duplicates with two sorting tables

Hi @elwiraa 

 

You can use Group By and adjust the code like below.

let
    Source = Table,
    #"Grouped Rows" = Table.Group(Source, {"Case"}, {{"tbl", 
        ( rows ) => let  
            sort = Table.Sort( rows ,{{ "key 1", Order.Ascending}, {"Key 2", Order.Ascending}}),
            topRow = Table.FirstN( sort , 1)
        in 
            topRow, type table [key 1=number, Key 2=number]}}),
    #"Expanded tbl" = Table.ExpandTableColumn(#"Grouped Rows", "tbl", {"key 1", "Key 2"}, {"key 1", "Key 2"})
in
    #"Expanded tbl"

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

View solution in original post

3 REPLIES 3
Highlighted
Super User I
Super User I

Re: Power query Remove Duplicates with two sorting tables

Hello @elwiraa 

 

Sorting has no impact on a distinct-function. 

The distinct funciton only takes 2 arguments, the table to get distinct values of and a optional equationcriteria. In the second parameter however you can specify how the different rows are compared. If you specifiy a column like {"YourColumn"} (as in your first example), the distinct is checked only in this columns. You can also apply a custom-function to check whether a value should be set as distinct or not.

In your case simple sort the table first and in an addition step  apply a Table.Distinct without second parameter

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Highlighted
Super User III
Super User III

Re: Power query Remove Duplicates with two sorting tables

Hi @elwiraa  

there is a really nasty trap in the sort-function, especially when applied before removing duplicates: https://community.powerbi.com/t5/Community-Blog/Bug-warning-for-Table-Sort-and-removing-duplicates-i...

So you have to apply a buffer on your sort-result before applying the distinct.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Super User IV
Super User IV

Re: Power query Remove Duplicates with two sorting tables

Hi @elwiraa 

 

You can use Group By and adjust the code like below.

let
    Source = Table,
    #"Grouped Rows" = Table.Group(Source, {"Case"}, {{"tbl", 
        ( rows ) => let  
            sort = Table.Sort( rows ,{{ "key 1", Order.Ascending}, {"Key 2", Order.Ascending}}),
            topRow = Table.FirstN( sort , 1)
        in 
            topRow, type table [key 1=number, Key 2=number]}}),
    #"Expanded tbl" = Table.ExpandTableColumn(#"Grouped Rows", "tbl", {"key 1", "Key 2"}, {"key 1", "Key 2"})
in
    #"Expanded tbl"

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Kudoed Authors