Forum Discussion
Solved
Multiple if statement and concatenating a result.
I am trying to test 20 conditions on a table with different (tests such as [value] should not be less than 0, [rate] should not be more than 1, date should not be older than 2024 etc.). Is there an e...
Hi AJ2960710, check this:
Output
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc3JCQAxDAPAXvyOQT4S0ktI/22sDwj78WOE5HNIaBBGXoUaYzIW3XFoJjX7jwVVsE6SGVIJ7zBvRyCLlnsVVF9DGV6JVeL2vsR/660VttqFsWvrfg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [value = _t, rate = _t, date = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"value", Int64.Type}, {"rate", type number}, {"date", type date}}, "sk-SK"), Ad_ConditionsCheck = Table.AddColumn(ChangedType, "Check", each [ conditions = { [value] >= 0, [rate] <= 1, [date] >= #date(2024,1,1) }, b = List.Transform(List.Positions(conditions), (x)=> "Condition " & Text.From(x+1) & " " & {"not met", "met"}{Byte.From(conditions{x})}), c = Text.Combine(b, " | ") ][c], type text) in Ad_ConditionsCheck
Hi AJ2960710, check this:
Output
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc3JCQAxDAPAXvyOQT4S0ktI/22sDwj78WOE5HNIaBBGXoUaYzIW3XFoJjX7jwVVsE6SGVIJ7zBvRyCLlnsVVF9DGV6JVeL2vsR/660VttqFsWvrfg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [value = _t, rate = _t, date = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"value", Int64.Type}, {"rate", type number}, {"date", type date}}, "sk-SK"),
Ad_ConditionsCheck = Table.AddColumn(ChangedType, "Check", each
[ conditions = { [value] >= 0, [rate] <= 1, [date] >= #date(2024,1,1) },
b = List.Transform(List.Positions(conditions), (x)=> "Condition " & Text.From(x+1) & " " & {"not met", "met"}{Byte.From(conditions{x})}),
c = Text.Combine(b, " | ")
][c], type text)
in
Ad_ConditionsCheck
dufoq3 this looks superb! is there an easy way so I can make the text more dynamic, to say "Value<0 , rate>1" etc.? I feel the condition text is lightweight, but would be helpful to have the actual name of the condition. If not this looks great!Thanks!
It is possible, but useless in my opinion:
Define conditions in Conditions step this way:
Output
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc3JCQAxDAPAXvyOQT4S0ktI/22sDwj78WOE5HNIaBBGXoUaYzIW3XFoJjX7jwVVsE6SGVIJ7zBvRyCLlnsVVF9DGV6JVeL2vsR/660VttqFsWvrfg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [value = _t, rate = _t, date = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"value", Int64.Type}, {"rate", type number}, {"date", type date}}, "sk-SK"), Conditions = [ value = ">= 0", rate = "<= 1", date = ">= #date(2024,1,1)" ], StepBack = ChangedType, Ad_Check = Table.AddColumn(StepBack, "Check", each [ curCond = "value >= 0", a = Expression.Evaluate(Text.Replace("value >= 0", "value", Text.From(Record.Field(_, "value")))), b = List.Transform(Record.FieldNames(Conditions), (x)=> [ format1 = (f)=> if f is number then Text.Replace(Text.From(f), ",", ".") else if f is date then "#date(" & Date.ToText(f, [Format="yyyy,MM,dd"]) & ")" else Text.From(f), format2 = (f)=> if Text.Contains(f, "#date") then f else Text.Replace(f, ",", "."), b1 = format1(Record.FieldOrDefault(_, x, null)), b2 = format2(Record.Field(Conditions, x)), b3 = Expression.Evaluate(b1 & b2), b4 = x & " " & b2 & " is " & Text.Upper(Text.From(b3)) ][b4] ), c = Text.Combine(b, "#(lf)") ][c], type text) in Ad_Check