Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello everyone,
So, I have a list of customer IDs with associated purchase dates:
Purchase Date | Customer ID | Current Equipment |
1/1/2022 | 555 | Current |
1/1/2022 | 555 | Current |
1/1/1999 | 555 | Imported |
What I am aiming to do is have a de-duplicated table that has unique Customer IDs, with the most recent Purchase Date and Current Equipment status.
In the above example, it should read "Current".
Initially I attempted to sort from newest to oldest and then de-duplicate thinking it would keep the words "Current", but it did not.
I'm assuming now what I need is some sort of M code fix the situation.
Any help would be greatly appreciated!
Solved! Go to Solution.
If you add a Table.Buffer( ) around your sort step that might be enough to do the job. Make sure your remove duplicates only looks at the relevant columns (might be just customer number) and it will keep the first row and remove others, so depending on the sort order it would keep the latest record.
When you do a Sort, there is some internal optimisation that basically means it does not completely sort every row. Table.Buffer forces it to complete the sort properly in order to then hold it in memory in a buffer.
If you add a Table.Buffer( ) around your sort step that might be enough to do the job. Make sure your remove duplicates only looks at the relevant columns (might be just customer number) and it will keep the first row and remove others, so depending on the sort order it would keep the latest record.
When you do a Sort, there is some internal optimisation that basically means it does not completely sort every row. Table.Buffer forces it to complete the sort properly in order to then hold it in memory in a buffer.
Thank you! This worked!
Hi,
You want to use a group by on the id and then extract the latest record.
This should give you a good guide:
https://radacad.com/grouping-in-power-query-getting-the-last-item-in-each-group