Power Query is latest flag on multiple criteria

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.

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