How to create True False column

Question

Hi,I want to create a "Tracking Check" column based on the below criteria:FALSE if&nbsp;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 423) Tracking number equal to 123456 or 1234567 or 12345678 or 123456789 or 12345678904) Tracking number begins with letter (a,b,c, ....)&nbsp;TRUE if&nbsp;1) Tracking number begins with numbers but not the above FALSE criteria2) Tracking number begins with "tracking"&nbsp;Please help. Thanks.&nbsp;Tracking Number&nbsp;Tracking Check000000000FALSE0000000000000000FALSE1FALSE123456FALSE123456578FALSE123456789FALSE1234567890FALSE5FALSE42FALSE99999999FALSEN/AFALSEnoneFALSENone ProvidedFALSEnot availableFALSENot determined yetFALSEnot known at this timFALSENo tracking number as this is NOT a part.FALSENOT REQUIREDFALSEon tagFALSEon tag not shipped yetFALSEretunred as a groupFALSEsent as a groupFALSEsent back all togetherFALSEshipped all togetherFALSEstill have itFALSETBDFALSEThis a none NOT be returned.FALSETracking in Field NotesFALSEUnknownFALSEunkownFALSExxxFALSExxxxxFALSE399321422337TRUE3995-7802-2396 &amp; 3995-7802-2385TRUE771802183110TRUE4680 0181 2992 TAG 174764TRUE4680 0176 6669TRUE468001808588 / 468001808577TRUE771890528925, 771890509183TRUEtracking 3997 4594 8980, 443688 tracking 3997 4594 8979, and 443688 tracking 3997 4594 8968TRUE

alb · Accepted Answer

Hi Anonymous&nbsp;
This can be best done in PQ. Place the following M code in a blank query to see the steps.
&nbsp;
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"
&nbsp;
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&nbsp;
&nbsp;

&nbsp;

Forum Discussion

How to create True False column

2 Replies

Related content

Create columns group

DAX Involving TRUE/FALSE Column

Create New Column with True False outcome based on date and id

Creating a date column

Creating a custom column

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