Forum Discussion
Convert a String Expression into a Boolean Expression and "solve" it
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"
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".
Thank you for the response! I'm probably missing something. Am I correct that by "XX" you mean any excessive characters left after replacement (like for the opt_12 it converts it to true2)?
If yes, the replacement won't meet the initial evaluation criteria:
"Opt_12" should change to "false" in the conitional statement, because it's not on the list of options for "Lot_1".