Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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.
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.
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 | ? |