Power Query is latest flag on multiple criteria

[Fro88er]

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

[v-yanjiang-msft · Accepted solution]

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 _ kalyj

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

[Fro88er]

You are amazing!  Thank you soo much for this assistance, and quick response.  

[v-yanjiang-msft · Accepted solution]

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 _ kalyj

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

[Fro88er]

This 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 · Accepted solution]

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]

[FollowUp] works like a charm except I can't load this additional column you created.  When I close and load, it takes forever, and must abandon it.  Thoughts?