Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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"
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"
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
Hi @Anonymous
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-in-Power/ba-p/810390
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.