Forum Discussion
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
| 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.
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
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 ? 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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
