Lowball M Code Question

[jcawley]

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!

[AdamVero · Accepted 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.

[jcawley]

Thank you! This worked!

[bcdobbs]

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