Forum Discussion
Remove or Hide Duplicate Values
- 10 years ago
There is a Remove Duplicates step when editing your query, it is in the Reduce Rows section of the ribbon, the M code is:
#"Removed Duplicates" = Table.Distinct(#"[Previous Step]", {"[Column Name]"})
I am having the same issue where I am pulling information from a SQL table but can not force the query to remove duplicates based on newest or most recent. I am attempting to remove duplicates out of an account number column, and wanting to keep only the most recent record for each account number.
I connect to the SQL table.
Edit Queires,
Sort the Table by Entry Date
Then remove duplicates from Account Number
When i check the output, I am still seeing records that are not the most recent for specific account numbers.
I was told it is because itr was not sorted at the source. I do not have access to the source to change the sort order.
What can I add in the language to force the sort in the imported data?
let
Source = Sql.Databases("123esco.cloudapp.net"),
123_Esco = Source{[Name="123_Esco"]}[Data],
dbo_Meters = CLOUDDATA{[Schema="dbo",Item="Meters"]}[Data],
#"Sorted Rows" = Table.Sort(dbo_Meters,{{"EntryDate", Order.Descending}}),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"UtilityAccountNumber"})
in
#"Removed Duplicates"
Anonymous, I ran into this issue in Power Query as well. I followed the problem in this article by Ken Puls, and it fixed the problem on the first shot.
- Anonymous9 years agoNot applicable
Thanks I will take a look