Power query: lookup minimum date if criteria fulfilled

Question

Let's say I have two columns: ID and date. I want to add a third column Earliest Date. This column should look up the earliest or minimum date across all the data that matches the ID in that row. It would produce the below - see ID 501. I have a lot of data so need to use power query. How do I write the formula?

ID  Date    Earliest Date
501 01/01/2017  01/08/2015
203 08/06/2015  08/06/2015
304 01/04/2014  01/04/2014
501 01/01/2016  01/08/2015
201 01/02/2015  01/02/2015
501 01/08/2015  01/08/2015

Answer 1

If you don't care about the order of the ID columns, you can also do a Group By on ID with the All Rows aggregate and an aggregate for the minimum date in the Date column. The builder should look like the picture below:

You'll get another column called Rows which contains the original table. If you expand this column to display the Date column, you'll get the table you want.