Forum Discussion
Helper IVPower Query is latest flag on multiple criteria
I am trying to identify the MIN record dateBegin AND appointmentID for each jobOrderID in power query. I was able to do this as a calculated column using allExcept and works great however I need it in the dataset (power query). Below are two examples JO 324942 min dateBegin it's the apt 889245 this is the earliest record. Example JO 324967 there are multiple records with the same dateBegin and therefore the MIN appointmentID comes into play (because they are sequential) the apt 889265 is the record to flag. I tried grouping, list.Min, etc. in an If statement to get the below result without success.
| appointmentID | dateBegin | jobOrderID | Flag (result I am looking for) |
| 889244 | 1/2/2019 9:00:00 AM | 324942 | |
| 889245 | 1/2/2019 8:30:00 AM | 324942 | 1 |
| 889246 | 1/2/2019 9:30:00 AM | 324942 | |
| 889247 | 1/2/2019 10:00:00 AM | 324942 | |
| 889248 | 1/2/2019 10:30:00 AM | 324942 | |
| 889250 | 1/2/2019 11:15:00 AM | 324942 | |
| 889253 | 1/2/2019 12:00:00 PM | 324942 | |
| 925310 | 2/8/2019 3:30:00 PM | 324942 | |
| 889265 | 1/2/2019 10:45:00 AM | 324967 | 1 |
| 889266 | 1/2/2019 10:45:00 AM | 324967 | |
| 889270 | 1/2/2019 10:45:00 AM | 324967 | |
| 889272 | 1/2/2019 10:45:00 AM | 324967 | |
| 894171 | 1/8/2019 3:45:00 PM | 324967 | |
| 907072 | 1/22/2019 3:45:00 PM | 324967 | |
| 912797 | 1/28/2019 5:15:00 PM | 324967 | |
| 947464 | 3/6/2019 3:45:00 PM | 324967 | |
| 951541 | 3/11/2019 4:30:00 PM | 324967 | |
| 967969 | 3/28/2019 4:30:00 PM | 324967 |
Hi Fro88er ,
According to your description, here's my solution.
Add a custom column.
if [dateBegin]=List.Min(Table.SelectRows(#"Changed Type",(x)=>x[jobOrderID]=[jobOrderID])[dateBegin]) and [appointmentID]=List.Min(Table.SelectRows(#"Changed Type",(x)=>x[dateBegin]=[dateBegin])[appointmentID]) then 1 else ""Get the result:
Here's the whole M syntax:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdJLDsMgDATQq1SsI2EbY+PseoBK2Ue5/zUaEtIG2nwkVuhpxsiMo0vJiNl1Dj15ArSH9QDzeTxf820gNiY3dUXGvUx9OJZSZ55I3UuEs/rU0OPUCBXFHuMhDRWlMsDQ0Awxp5JPKw2lv5U5VGIzKtf9ol8qt6nCfUr3qDEqLvTzqlUOjTRQ2ELpgiKplbWW1FgW8ENZWfL/C14uQiNGxkUirpTbBWxU1MQWuvX/pdMb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [appointmentID = _t, dateBegin = _t, jobOrderID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"appointmentID", Int64.Type}, {"dateBegin", type datetime}, {"jobOrderID", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Flag", each if [dateBegin]=List.Min(Table.SelectRows(#"Changed Type",(x)=>x[jobOrderID]=[jobOrderID])[dateBegin]) and [appointmentID]=List.Min(Table.SelectRows(#"Changed Type",(x)=>x[dateBegin]=[dateBegin])[appointmentID]) then 1 else "") in #"Added Custom"I also 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.
Hi Fro88er ,
You can try to add a condition in the code.
if [dateBegin]=List.Min(Table.SelectRows(#"Changed Type",(x)=>x[jobOrderID]=[jobOrderID])[dateBegin]) and [appointmentID]=List.Min(Table.SelectRows(#"Changed Type",(x)=>x[dateBegin]=[dateBegin])[appointmentID])and [type]="interview" then 1 else ""Best Regards,
Community Support Team _ kalyj
5 Replies
v-yanjiang-msftCommunity Support
Hi Fro88er ,
According to your description, here's my solution.
Add a custom column.
if [dateBegin]=List.Min(Table.SelectRows(#"Changed Type",(x)=>x[jobOrderID]=[jobOrderID])[dateBegin]) and [appointmentID]=List.Min(Table.SelectRows(#"Changed Type",(x)=>x[dateBegin]=[dateBegin])[appointmentID]) then 1 else ""Get the result:
Here's the whole M syntax:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdJLDsMgDATQq1SsI2EbY+PseoBK2Ue5/zUaEtIG2nwkVuhpxsiMo0vJiNl1Dj15ArSH9QDzeTxf820gNiY3dUXGvUx9OJZSZ55I3UuEs/rU0OPUCBXFHuMhDRWlMsDQ0Awxp5JPKw2lv5U5VGIzKtf9ol8qt6nCfUr3qDEqLvTzqlUOjTRQ2ELpgiKplbWW1FgW8ENZWfL/C14uQiNGxkUirpTbBWxU1MQWuvX/pdMb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [appointmentID = _t, dateBegin = _t, jobOrderID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"appointmentID", Int64.Type}, {"dateBegin", type datetime}, {"jobOrderID", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Flag", each if [dateBegin]=List.Min(Table.SelectRows(#"Changed Type",(x)=>x[jobOrderID]=[jobOrderID])[dateBegin]) and [appointmentID]=List.Min(Table.SelectRows(#"Changed Type",(x)=>x[dateBegin]=[dateBegin])[appointmentID]) then 1 else "") in #"Added Custom"I also 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.
Fro88erThis is amazing, I was now aware I can run variables in M-code (mind blown)!! One last favor, if there was an additional column called [type] with records like "interview", 2nd interview" ect. and I only wanted your logic with [type] = "interview" how / where do I also add that filter logic?
- v-yanjiang-msft
Hi Fro88er ,
You can try to add a condition in the code.
if [dateBegin]=List.Min(Table.SelectRows(#"Changed Type",(x)=>x[jobOrderID]=[jobOrderID])[dateBegin]) and [appointmentID]=List.Min(Table.SelectRows(#"Changed Type",(x)=>x[dateBegin]=[dateBegin])[appointmentID])and [type]="interview" then 1 else ""Best Regards,
Community Support Team _ kalyj
- Fro88er
You are amazing! Thank you soo much for this assistance, and quick response.