Test serial numbers to check to see if conditions are met

[Anonymous]

I need help testing my table of serial numbers 

For each serial #

        if the Order Type = 60 AND 64 then Status = nOK

        if the Order Type = 60 then Status = OK

[dufoq3]

Hi @Anonymous, 

different approach:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnYxsDQwMzU2NVbSUTIEYjMDpVgdNHFdsIQJWMIpxNjCwNDFAKgORQNc3BBV3AAMDE1NLPCII1sAlTAxMsaqASIO1xALAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Serial Number" = _t, Qty = _t, #"Order Type" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Serial Number", type text}, {"Qty", Int64.Type}, {"Order Type", Int64.Type}}),
    GroupedRows = Table.Group(ChangedType, {"Serial Number"}, {{"All", each 
        if List.ContainsAll([Order Type], {60,64}) then Table.FromColumns(Table.ToColumns(_) & {{"nOK", null}}, Table.ColumnNames(_) & {"Status"})  else Table.AddColumn(_, "Status", each "OK") , type table}}),
    All = Table.Combine(GroupedRows[All])
in
    All

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Download example PBIX file with the code below

Try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnYxsDQwMzU2NVbSUTIEYjMDpVgdNHFdsIQJWMIpxNjCwNDFAKgORQNc3BBV3AAMDE1NLPCII1sAlTAxMsaqASIO1xALAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Serial Number" = _t, Qty = _t, #"Order Type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Serial Number", type text}, {"Qty", Int64.Type}, {"Order Type", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Status", each if [Order Type] = 64 then "" else if List.Contains((let x = [Serial Number] in Table.SelectRows(Source, each [Serial Number] = x)[Order Type]), "64") then "nOK" else "OK")
in
    #"Added Custom"

regards

Phil