Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
whatisdata96
Helper I
Helper I

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

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

1234I2/10/20222/15/2022
1234I1/10/20221/15/2022
1234I1/5/20221/7/2022
1234I1/6/20221/7/2022
5678I1/23/20221/25/2022
5678I1/23/20221/25/2022
5678I4/25/20225/6/2022
5678O1/25/2022?
1234O1/7/2022?
1234O1/15/2022?
1234O2/15/2022?
5678O5/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.

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

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:

vkalyjmsft_0-1665717165835.png

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.

View solution in original post

2 REPLIES 2
v-yanjiang-msft
Community Support
Community Support

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:

vkalyjmsft_0-1665717165835.png

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
Helper I
Helper I

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

 

Number    Type         Date                   WHAT I WANT

1234I2/10/2022           2/15/2022
1234I1/10/2022           1/15/2022
1234I1/5/2022            1/7/2022
1234I1/6/2022             1/7/2022
5678I1/23/2022           1/25/2022
5678I1/23/2022           1/25/2022
5678I4/25/2022      5/6/2022
5678O1/25/2022?
1234O1/7/2022?
1234O1/15/2022?
1234O2/15/2022?
5678O5/6/2022?

 

 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.