How to create True False column

[PBI_newuser]

Hi,

I want to create a "Tracking Check" column based on the below criteria:

FALSE if 

1) Tracking number begins with "000" or "999"

2) Tracking number equal to 1 or 2 or 3 or 4 or 5 or 6 or 7 or 8 or 9 or 0 or 42

3) Tracking number equal to 123456 or 1234567 or 12345678 or 123456789 or 1234567890

4) Tracking number begins with letter (a,b,c, ....)

TRUE if 

1) Tracking number begins with numbers but not the above FALSE criteria

2) Tracking number begins with "tracking"

Please help. Thanks.

Tracking Number	Tracking Check
000000000	FALSE
0000000000000000	FALSE
1	FALSE
123456	FALSE
123456578	FALSE
123456789	FALSE
1234567890	FALSE
5	FALSE
42	FALSE
99999999	FALSE
N/A	FALSE
none	FALSE
None Provided	FALSE
not available	FALSE
Not determined yet	FALSE
not known at this tim	FALSE
No tracking number as this is NOT a part.	FALSE
NOT REQUIRED	FALSE
on tag	FALSE
on tag not shipped yet	FALSE
retunred as a group	FALSE
sent as a group	FALSE
sent back all together	FALSE
shipped all together	FALSE
still have it	FALSE
TBD	FALSE
This a none NOT be returned.	FALSE
Tracking in Field Notes	FALSE
Unknown	FALSE
unkown	FALSE
xxx	FALSE
xxxxx	FALSE
399321422337	TRUE
3995-7802-2396 & 3995-7802-2385	TRUE
771802183110	TRUE
4680 0181 2992 TAG 174764	TRUE
4680 0176 6669	TRUE
468001808588 / 468001808577	TRUE
771890528925, 771890509183	TRUE
tracking 3997 4594 8980, 443688 tracking 3997 4594 8979, and 443688 tracking 3997 4594 8968	TRUE

[AlB · Accepted solution]

Hi @PBI_newuser 

This can be best done in PQ. Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVJdb9sgFP0rR31OW8CYj8dO66a+tFvlPnV9IDNKUBwcYZxm/34E7MzTqiEE95xz4X7A6+sVmcfV22qBliQtK6t4LRZmLdUCSaX/RuVsnVfO8qankcHj7V3efe9tIZKBb6E/uta2kxRhjsZ1Zt3NPhGtjTbsnbctftl4cdz5/t3DRMStGxDdfjqAGMzPnfMb+HG/tgFmKC5pPj41MDiYEG+Kd8LP999fHp7vP2ei94hmszBxDjVs3eGwCB9sHH1IRLraYBP68ZD5wfr4IbdOGcF0HWK/sXFrQ5Gma/8VokvU1hwtXInYfCr5NedCDM49zMWsLc7JhNScUlEzF+88vjjbtUgttEPWXnzuWbZHv5vN0+k075NVaV0xyhmrKjkT9bVUhF2zSgv8GAlhAktWlYeXkiZMVUVp+Q9cKAJCFQXTmqG5+woquRR8qUoBIYS+UMmfqFop3OIPlPISQZOaKc3qFSZEdAqZ5cvjp+QkeK05lFZkBc4rkW78UJd6BePb//qI9PnffgM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Tracking Number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Tracking Number", type text}}),


    #"Added Custom" = Table.AddColumn(#"Changed Type", "Tracking Check", each if Text.Start([#"Tracking Number"],8) = "tracking" then true else if  
   let 
        val_ = [#"Tracking Number"],
        c1_ = List.Contains({"000","999"},Text.Start(val_,3)),
        c2_ = List.Contains({"0".."9","42", "123456","1234567", "12345678", "123456789","1234567890"}, val_),
        c3_ = List.Contains({"A".."Z","a".."z"},Text.Start(val_,1)),
        res_ = c1_ or c2_ or c3_ 
    in 
        res_  
 then false else true, type logical)
in
    #"Added Custom"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[PBI_newuser]

@AlB Thanks! It works!