Power query: lookup minimum date if criteria fulfilled

1
votes

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
3
excelif-statementlookupminimumpowerquery

3 Answers

1
votes

The solution is to join the original data with itself, using a minimum aggregation to calculate Earliest Date.

From this starting data in a Query called Query1:

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

Join the data with: Combine > Merge Queries (aka "Join") > Merge Queries as New (to avoid modifying Query1)

Pick Query1 ID column each time, and leave the default Left Outer join Kind: Join

Next, choose Transform > Structured Column > Aggregate, and pick "Count of Date" (It would be better if the UI suggested a minimum aggregation, but we can fix that by touching the code directly).

Next, turn on the Formula Bar (View > Layout > Formula Bar) and edit the formula to replace List.Count with List.Min. Rename the new column, and you're done!

My full "M" formulas:

Query1: 

let
    Source = Table.PromoteHeaders(Csv.Document("ID,Date
501,01/01/2017
203,08/06/2015
304,01/04/2014
501,01/01/2016
201,01/02/2015
501,01/08/2015"))
in
    Source

Merge1:

let
    Source = Table.NestedJoin(Query1,{"ID"},Query1,{"ID"},"NewColumn",JoinKind.LeftOuter),
    #"Aggregated NewColumn" = Table.AggregateTableColumn(Source, "NewColumn", {{"Date", List.Min, "Earliest Date"}})
in
    #"Aggregated NewColumn"
1
votes

For each row you get the earliest date by filtering source table by thisrow[ID], and then selecting minimum from [Date] column of the resulting table.

let
    Source = #table(type table[ID=Int64.Type, Date=date], {
    {501,"01/01/2017"},
    {203,"08/06/2015"},
    {304,"01/04/2014"},
    {501,"01/01/2016"},
    {201,"01/02/2015"},
    {501,"01/08/2015"}
    }),
    Convert = Table.TransformColumnTypes(Source,{{"Date", type date}}),

    AddMinDateColumn = Table.AddColumn(Convert, "Earliest Date", (thisrow) => List.Min(Table.SelectRows(Convert, each [ID] = thisrow[ID])[Date]), type date)
in
    AddMinDateColumn
0
votes

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:

Group by builder

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.

Expand the rows column