How to find closest date after certain date in Power Query? (Example shown)

[whatisdata96]

Hi all -

I'm looking to create a conditional column in Power Query which would analyze 3 columns to output a 'looked up value' (date). This was probably not explained very well. But this is what I want:

Number    Type         Date                   WHAT I WANT

1234	I	2/10/2022	2/15/2022
1234	I	1/10/2022	1/15/2022
1234	I	1/5/2022	1/7/2022
1234	I	1/6/2022	1/7/2022
5678	I	1/23/2022	1/25/2022
5678	I	1/23/2022	1/25/2022
5678	I	4/25/2022	5/6/2022
5678	O	1/25/2022	?
1234	O	1/7/2022	?
1234	O	1/15/2022	?
1234	O	2/15/2022	?
5678	O	5/6/2022	?

I'm looking to return the NEXT nearest date for all rows with column 'type' = I. If it says, I, that means we want the next nearest date for where 'type' is O (matching with 'number'. For all rows with 'O' we can leave it as blank.

Is there a way to do this via condtional column? Or would I have to do a custom column with a bunch of M? Thank you in advance.

[v-yanjiang-msft · Accepted solution]

Hi @whatisdata96 ,

You should at least add a custom column.

if [Type]="I" then List.Min(Table.SelectRows(#"Changed Type", (x)=>x[Number]=[Number] and x[Type]="O"and x[Date]>[Date])[Date]) else ""

Result:

Here's the whole M syntax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUfIEYiN9QwN9IwMjI6VYHRQJQ9wSpjjEzRDipmbmFnBxI2PSJEz0jUwxJPwhOjDthkiY4xA3xK7BCEUCyQpTuC9iAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, Type = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Type", type text}, {"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Type]="I" then List.Min(Table.SelectRows(#"Changed Type", (x)=>x[Number]=[Number] and x[Type]="O"and x[Date]>[Date])[Date]) else "")
in
    #"Added Custom"

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[whatisdata96]

Table came out weird in original post - hope this is better

Number    Type         Date                   WHAT I WANT

1234	I	2/10/2022	2/15/2022
1234	I	1/10/2022	1/15/2022
1234	I	1/5/2022	1/7/2022
1234	I	1/6/2022	1/7/2022
5678	I	1/23/2022	1/25/2022
5678	I	1/23/2022	1/25/2022
5678	I	4/25/2022	5/6/2022
5678	O	1/25/2022	?
1234	O	1/7/2022	?
1234	O	1/15/2022	?
1234	O	2/15/2022	?
5678	O	5/6/2022	?