Multiple max dates in power query - keeping just one of them with a specific logic

[Berl21]

Hi,

I have been struggling with the same issues for a few days in different forms. I can't wrap my head around it 😕

In my data set there is an issue with a specific column that shows a time stamp. I need to show the max date and the corresponding milestone associated with it, but it happens that the same milestones can be associated with twice the same timestamp. This generates mistakes when I build measures for other values. I need to get unique values. 

For example, our customer service might push one entry in "Documents reviewed" on the 6th of January and then push it in "Application closed" on the same day. This generates some issues on the side of data quality. 

In the following example I have two ids for which the problem happens. It should show a total of 2 rows (where Index Milestones =6), not a total of 6 rows. However it is impossible to get rid of duplicates by working on the id column alone, since it would take away some other rows which don't have this issue. What I need on this case is the latest entry on the 10.09 and 27.09, knowing that the second way of filtering should take the max of "Index Milestones" if the date stays the same. 

To get to this result I used the function group by id+ added max date in the same way, then filtered on max date= last change date.

I thought I could work in a similar way for the max of the index milestones column...not the case 😕

There must be a way to get rid of these duplicates in Power Query. Does anyone have an input on how to fix this?

Thanks,

Pauline.

[Berl21]

Thank you @Pat ! That worked wonder. 

[mahoneypat · Accepted solution]

You can do this with grouping your data on the first column, keeping all rows, and then using the Table.Max or Table.MaxN functions. Your data were shared as an image and I could not easily extract them, or I would done it and shared the M code needed. If you get stuck, please @ mention me in your reply and include the data in a copy-paste-able format.

Pat