Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

Need Help
Tips and Tricks
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Anonymous 

     

    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

     

3 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi Anonymous 

     

    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

     

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello Anonymous 

     

    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