Return minimum date that is >= specified date across columns

Question

Using PowerQuery I'm looking to return the minimum date that is greater or equal to a specified date.

For example, the table below is filtered so each row in NewDiagnosis contains a date value.
The first row would return 07/03/2019 as that's equal to NewDiagnosis. The second would return 17/04/2019, the third 05/08/2019 and the fourth would return null.

| NewDiagnosis | NewDisposition | PriorityDisposition | RMARequested | AwaitingCustoms |
|--------------|----------------|---------------------|--------------|-----------------|
| 07/03/2019   | 07/03/2019     |                     | 06/03/2019   | 08/03/2019      |
| 15/04/2019   |                | 17/04/2019          |              | 18/04/2019      |
| 11/04/2019   |                |                     | 05/08/2019   |                 |
| 14/05/2019   |                |                     |              |                 |
| 13/06/2019   |                |                     | 17/06/2019   |                 |
| 17/06/2019   |                |                     | 18/06/2019   |                 |
| 18/06/2019   |                |                     | 17/07/2019   |                 |
| 12/06/2019   |                |                     |              | 14/06/2019      |
| 20/06/2019   | 25/06/2019     |                     |              |                 |
| 03/06/2019   |                |                     |              |                 |
| 10/06/2019   |                |                     |              |                 |
| 24/06/2019   | 02/07/2019     |                     | 02/07/2019   |                 |

I can return the overall minimum value using (with the full list of columns I'm looking at), but this doesn't take into account the value in NewDiagnosis.

#"Inserted Earliest" = Table.AddColumn(#"Filtered Rows", "Earliest", 
                        each List.Min({[#"New#(lf)Disposition"], [#"Priority#(lf)Disposition"], 
                                       [#"RMA#(lf)Requested"], [#"Awaiting#(lf)Customs"], 
                                       [#"Awaiting#(lf)Verification"], [#"New Cards#(lf)Awaiting Process"], 
                                       [Initial Email], [#"Day 4#(lf)Reminder#(lf)incl. SCC"], 
                                       [#"Escalation#(lf)SQE"], [#"Escalation#(lf)Clinic Manager"], 
                                       [#"Escalation#(lf)HOQ"], [#"Section B#(lf)Email#(lf)Received"], 
                                       [#"Day 7#(lf)Reminder Email"], [#"Day 14#(lf)Reminder#(lf)incl. SCC"], 
                                       [#"Day 21#(lf)Escalation#(lf)SQE"], [#"Day 28#(lf)Escalation Clinic Manager"], 
                                       [#"Day 42#(lf)Escalation#(lf)HOQ"], [#"Section C#(lf)Email#(lf)Received"], 
                                       [#"Technical#(lf)Review"], [#"1st#(lf)Supplier Turnback"], 
                                       [#"2nd#(lf)Supplier Turnback"], [#"Internal#(lf)Review"], 
                                       [#"1st#(lf)Internal Turnback"], [#"2nd#(lf)Internal Turnback"], 
                                       [#"Awaiting#(lf)Customer Closure"], [Closed], 
                                       [Back to Stock], [Sent to SCRAP], [Internal]}), type date)

I've had a look to see if I could figure it out, but haven't found anything yet. M Function Reference, M Primer, PowerQuery Countif previous date

Any help would be greatly appreciated.

Following the suggestion given by @AlekseiZhigulin my query is:

let
    Source = #"Source_Data",
    #"Removed Columns" = Table.RemoveColumns(Source,
               {"SUPPLIER", "Vendor_Supplier", "Customer", 
                "SCC", "SCM", "DATE", "Phase", "Liability", 
                "Agreed#(lf)Start Date", "Date of Acceptable Response", 
                "Owner", "Created#(lf)Date", "Created#(lf)By", 
                "Modified#(lf)Date", "Modified#(lf)By", "VENDOR CODE", 
                "Section B Required#(lf)Receipt", "Section B#(lf)Email#(lf)Days Late", 
                "Part Returned to Supplier", "Section C Required#(lf)Receipt", 
                "Section C#(lf)Email#(lf)Days Late", "Comments"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([#"Cancelled?"] = null) and ([#"New#(lf)Diagnosis"] <> null)),
    dateCols = {"New#(lf)Disposition", "Priority#(lf)Disposition", 
                "RMA#(lf)Requested", "Awaiting#(lf)Customs", 
                "Awaiting#(lf)Verification", "New Cards#(lf)Awaiting Process", 
                "Initial Email", "Day 4#(lf)Reminder#(lf)incl. SCC", 
                "Escalation#(lf)SQE", "Escalation#(lf)Clinic Manager", 
                "Escalation#(lf)HOQ", "Section B#(lf)Email#(lf)Received", 
                "Day 7#(lf)Reminder Email", "Day 14#(lf)Reminder#(lf)incl. SCC", 
                "Day 21#(lf)Escalation#(lf)SQE", "Day 28#(lf)Escalation Clinic Manager", 
                "Day 42#(lf)Escalation#(lf)HOQ", "Section C#(lf)Email#(lf)Received", 
                "Technical#(lf)Review", "1st#(lf)Supplier Turnback", 
                "2nd#(lf)Supplier Turnback", "Internal#(lf)Review", 
                "1st#(lf)Internal Turnback", "2nd#(lf)Internal Turnback", 
                "Awaiting#(lf)Customer Closure", "Closed", 
                "Back to Stock", "Sent to SCRAP", "Internal"},
    minDate = Table.AddColumn(dateCols, "minDate", 
                (z)=> List.Min(List.Select(Record.ToList(Record.SelectFields(z, dateCols)), 
                each _ >= z[NewDiagnosis])), type date)
in
    minDate

but this returns We cannot convert a value of type List to type Table.

Aleksei Zhigulin Aleksei Zhigulin · Accepted Answer · 2019-08-08T10:43:06

You may use following approach:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    dateCols = {"NewDisposition",  "PriorityDisposition", "RMARequested", "AwaitingCustoms"},
    types = Table.TransformColumnTypes(Source, List.Transform({"NewDiagnosis"}&dateCols, each {_, type date})),
    minDate = Table.AddColumn(types, "minDate", (z)=> List.Min(List.Select(Record.ToList(Record.SelectFields(z, dateCols)), each _ >= z[NewDiagnosis])), type date)
in
    minDate

Of course, if you have already applied types in your table, step types is not needed.

Return minimum date that is >= specified date across columns

1 Answers