Forum Discussion
Convert a String Expression into a Boolean Expression and "solve" it
Dear Friends,
Could you please help me to figure out how to automatically convert a string into a Boolean expression and “solve” it?
Here’s an example:
There are two tables: one shows options selected per lot. Another one includes lots, items, quantities and conditions that need to be “solved”.
Let’s say, if “Option_1” and “Option_3” were selected for “Lot_1” in the first table, then in the second table in the conditional statement for “Lot_1”, “Option_1” and “Option_3” should convert into “TRUE”.
“Option_2” wasn’t selected for “Lot_1” in the first table, so in the second table in the conditional statement for “Lot_1”, “Option_2” should convert to “FALSE”.
So, that’s how the first conditional statement from the second table should be converted into a Boolean expression:
“Option_1 and Option_3 and not Option_2” = “TRUE and TRUE and not FALSE” = “TRUE and TRUE and TRUE” = “TRUE”
In case the conditional statement is blank in the second table, it automatically reads as TRUE.
The list of options per lot can be very extensive (it might be thousands of lots and hundreds of options per lot).
Same thing applies to the second table, but also conditional statements can vary by length.
Thank you in advance!
let Option = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8skviTdU0lHyLwDRsTqoIsZwESMMNTAREwwRU7iIMVTECEPETCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Lot = _t, Option = _t]), #"Grouped Option" = Table.Group(Option, "Lot", {"Opt", each [Option]}), Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8skviTdU0lHyLwDSCol5KQogljGYlZdfAuYZKcXqIFSicFCljZCljaDSJgr5RWBjTDGkjOAWmsHljIFyGhDXQPUZaYKVaUAcBhU00UTRgZsD84SxUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Lot = _t, Condition = _t]), #"Logical Expression" = Table.AddColumn( Source, "Logical", each let opt = #"Grouped Option"{[Lot=[Lot]]}[Opt], cond = List.Select(List.Transform(Text.Split([Condition], " "),each Text.Remove(_ , {"(",")"})), each Text.StartsWith(_, "Opt", Comparer.OrdinalIgnoreCase)), #"T/F" = List.Transform(cond, each Text.From(List.Contains(opt, _))), expr = List.Accumulate({0..List.Count(cond)-1}, [Condition], (s,c) => Text.Replace(s, cond{c}, #"T/F"{c})) in if Text.Length([Condition]) = 0 then true else Expression.Evaluate(expr, [true=true, false=false]) ) in #"Logical Expression"
5 Replies
let Option = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8skviTdU0lHyLwDRsTqoIsZwESMMNTAREwwRU7iIMVTECEPETCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Lot = _t, Option = _t]), #"Grouped Option" = Table.Group(Option, "Lot", {"Opt", each [Option]}), Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8skviTdU0lHyLwDSCol5KQogljGYlZdfAuYZKcXqIFSicFCljZCljaDSJgr5RWBjTDGkjOAWmsHljIFyGhDXQPUZaYKVaUAcBhU00UTRgZsD84SxUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Lot = _t, Condition = _t]), #"Logical Expression" = Table.AddColumn( Source, "Logical", each let opt = #"Grouped Option"{[Lot=[Lot]]}[Opt], cond = List.Select(List.Transform(Text.Split([Condition], " "),each Text.Remove(_ , {"(",")"})), each Text.StartsWith(_, "Opt", Comparer.OrdinalIgnoreCase)), #"T/F" = List.Transform(cond, each Text.From(List.Contains(opt, _))), expr = List.Accumulate({0..List.Count(cond)-1}, [Condition], (s,c) => Text.Replace(s, cond{c}, #"T/F"{c})) in if Text.Length([Condition]) = 0 then true else Expression.Evaluate(expr, [true=true, false=false]) ) in #"Logical Expression"CNENFRNL, sorry to bother you again, but I need more help:
Your proposed M query works great, but I’m having issues with scenarios when within one conditional statement the beginning part of one option name exactly matches another option name:
Let’s say I have the following conditional statement:
Opt_1 and Opt_12 and not Opt_3
In my understanding, the replacement happens as follows:
true and true2 and not false
That’s the error that I got:
In my understanding, I need to change “Text.StartWith” with some other function, but I’m not sure which one needs to be used instead:
Text.StartsWith(_, "Opt", Comparer.OrdinalIgnoreCase))
Your help is greatly appreciated!
You may want to change all "trueXX"/"falseXX" to "true"/"false".