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.
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
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.
User | Count |
---|---|
114 | |
105 | |
79 | |
68 | |
63 |
User | Count |
---|---|
148 | |
107 | |
106 | |
82 | |
70 |