Forum Discussion
How to create True False column
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 ...
Hi Anonymous
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
Hi Anonymous
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
AlB Thanks! It works!