Forum Discussion
Removing duplicates based on Date
I am facing an issue with how i am removing the duplicates from the below set of data in Power Query
What i am trying to do is pick the last price change transaction from a set of data. In the below set, i want the "10004-7/26/2021 2:31:23 PM" to be picked. I tried to sort on key data & time and then removed duplicates on Goods ID and Date. But, for some reason i always ended up with the transaction with an earlier time stamp "10004-7/26/2021 2:12:49 PM"
Any better way to resolve this? 1 goodsId can have multiple price change in a day. need to pick the latest price change for that day.
This works fine when i try to take this below 4 into a table and sort and remove duplicates. But with a bigger dataset behaves different.
| ChangeId | Goods Id | dtDate | Goods ID and Date | Key date & time |
| 326330 | 10004 | 7/26/2021 14:12 | 10004-2021-07-26 | 10004-7/26/2021 2:12:49 PM |
| 326345 | 10004 | 7/26/2021 14:31 | 10004-2021-07-26 | 10004-7/26/2021 2:31:23 PM |
| 328262 | 10004 | 7/30/2021 14:07 | 10004-2021-07-30 | 10004-7/30/2021 2:07:38 PM |
| 331467 | 10004 | 8/10/2021 15:26 | 10004-2021-08-10 | 10004-8/10/2021 3:26:56 PM |
Anonymous , Create s separate data column
date only = DateTime.Date([Date])
And the use group by and take max on of the columns that are not needed in group by
2 Replies
Anonymous , Create s separate data column
date only = DateTime.Date([Date])
And the use group by and take max on of the columns that are not needed in group by
