Power Query Configurable / Dynamic If Statement

[hnguyen76]

Hi All.

I'm attempting to create a configuration list that I can convert into a single statement and then have a custom column ingest that logic and evaluate if possible. The objective behind this is to allow conditions to be set outside of power bi as the rules and logics will change over time without requiring to touch the solution and make it more dynamic. I have a sample table below that holds some key information:

Hypothesis	Condition	Conclusion	Result
if	[Vendor] = "Sample1"	then	"Sample"
if	[OrigOperatorId] = "brg" and [System] = "AP"	then	"Result1"
if	[System] = "AP" or [System] = "PO"	then	"APPO"

I want to convert that into something like this:

if [Vendor] = "Sample1" then "Sample" else if [OrigOperatorId] = "brg" and [System] = "AP" then "Result1" else if [System] = "AP" or [System] = "PO" then "APPO" else ""

Finally passing that into a custom column that evaluates the entire string. So let's say if I have a sample table such as this:

ID	Vendor	OrigOperatorId	System
1	Sample2	S3	Man
2	Sample1	go	GrR
3	Sample3	brg	AP
4	Sample4	bee	AP

The new conditional column should evaluate and return the expected result of:

ID	Vendor	OrigOperatorId	System	Test
1	Sample2	S3	Man
2	Sample1	go	GrR	Sample
3	Sample3	brg	AP	Result1
4	Sample4	bee	AP	APPO

Is this even possible? I think it should be. Any help would be greatly appreciated!
@ImkeF 

[v-xuding-msft]

Hi @hnguyen76 ,

You could create a conditional column. When you want to change If Statement, you just need to click that step rather than change your code manually in Advanced Editor.

For more informations, you could reference the blog to have a try.

https://radacad.com/conditional-column-in-power-bi-using-power-query-you-can-do-anything

[hnguyen76]

Hi @v-xuding-msft ,

The idea is to develop a solution and make it "touchless". The condition changes overtime and we would like to allow a product owner to create his/her own hypotheses/conclusions to be generated without altering anything within Power BI. You can assume that the users will not have the ability to may any modifications either large or small to a production model.

[v-xuding-msft]

Hi @hnguyen76 ,

Unfortunately, as I know, it is not supported. If you really need this feature implemented, you could submit an idea on https://ideas.powerbi.com/ideas/.

[hnguyen76]

@v-xuding-msft ,
I would have to disagree. I believe we have all the pieces required. We currently have the ability to add variables and functions within a custom column. I fully acknowledge that my current power query knowledge is not sufficient enough to build a solution as such but my intuition says it's possible.

the anticipated function logic would be something of this sort:

1. cntrows = count rows in configuration table
2. for i=1 to cntrows
x = x{i} // This would be my hypothesis value (if or else ending)
y= y{i} // This would be my condition to set
z = z{i} // This would be my Result
3. Do comparative logic for each row. Return result of first true
4. Iterative loop: i=i++

[ImkeF]

Hi @hnguyen76 ,

I agree, the logic should work.
The function "Expression.Evaluate" is able to evaluate string expressions as M-code.

So syntactically, this should work:

let
    Conditions = let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykxT0lGKDkvNS8kvilWwVYhRCk7MLchJNYxRAkqUZKTmASmIkFKsDky9f1Fmun9BalFiSX6RZwpEX1JReoySQmJeikJ0cGVxSWouRNgxANmkoNTi0pwSQySj0NUq5BehGhDgj2yAY0CAP0J3SGpFiZ5zfl5JYmZesQbMGzoKcG8YxShpYvojFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Hypothesis = _t, Condition = _t, Conclusion = _t, Result = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hypothesis", type text}, {"Condition", type text}, {"Conclusion", type text}, {"Result", type text}})
    in
        #"Changed Type",
    ListOfConditions = Table.ToRows(Conditions),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpOzC3ISTUCsYyBhG9inlKsTrSSEVwKpCg9H0i4FwWBpYzhUiBWUlE6kHQMAEuZwKVArKTUVKhULAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Vendor = _t, OrigOperatorId = _t, System = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Vendor", type text}, {"OrigOperatorId", type text}, {"System", type text}}),
    Evaluate = Table.AddColumn(
        #"Changed Type", 
        "Custom", 
        each List.First(
                List.Transform(
                    ListOfConditions, 
                    (l) =>  Expression.Evaluate(
                                " if " & l{1} & " then """ & l{3} & """ else null", 
                                [Text.Contains = Text.Contains, _=_]
                            )
                    )
                )
            )
in
    Evaluate

However, this is throwing an exception error.
Maybe one would have to use other Expression-functions here. 
Maybe @artemus  has an idea how to get this working?

[artemus]

The limitation of using Expression.Evaluate is that it is, by default run without any access to variables or library functions like Table.SelectRows. The second parameter holds all of these dependicies, and you need to explicitly declare them. You can temporarly use #shared for everything, but Power Bi will refuse to load any query using this into the model.

Generally you would need to define all your dependicies like:

let
   Environment = 
   [
      Text.StartsWith = Text.StartsWith,
      List.Contains = List.Contains,
      ...//Add more as needed
   ]

And use it like:

Table.AddColumn("Custom code", each Expression.Evaluate([CodeColumn], Environment & [_ = _])

The [_ = _] adds the row context for access to the query.

[ImkeF]

Thank you @artemus for quick reply.

In my code I've used 

[Text.Contains = Text.Contains, _=_]

, which looks to fullfill your requirements.

Still, I'm getting an exception error:

 

Do you have any idea what could cause this?
Please try my code and check if you get this error as well.

[artemus]

Here is the sample fixed:

let
    Conditions = let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykxT0lGKDkvNS8kvilWwVYhRCk7MLchJNYxRAkqUZKTmASmIkFKsDky9f1Fmun9BalFiSX6RZwpEX1JReoySQmJeikJ0cGVxSWouRNgxANmkoNTi0pwSQySj0NUq5BehGhDgj2yAY0CAP0J3SGpFiZ5zfl5JYmZesQbMGzoKcG8YxShpYvojFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Hypothesis = _t, Condition = _t, Conclusion = _t, Result = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hypothesis", type text}, {"Condition", type text}, {"Conclusion", type text}, {"Result", type text}}),
        GenerateFunctions = Table.AddColumn(#"Changed Type", "Function", each Expression.Evaluate("each if " & [Condition] & " then """ & [Result] & """ else null", [Text.Contains = Text.Contains]))
    in
        GenerateFunctions,
    ListOfConditions = Table.ToRows(Conditions),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpOzC3ISTUCsYyBhG9inlKsTrSSEVwKpCg9H0i4FwWBpYzhUiBWUlE6kHQMAEuZwKVArKTUVKhULAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Vendor = _t, OrigOperatorId = _t, System = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Vendor", type text}, {"OrigOperatorId", type text}, {"System", type text}}),
    Evaluate = Table.AddColumn(
        #"Changed Type", 
        "Custom", 
        each List.First(List.Transform(Conditions[Function], (fn) => fn(_)), each _ <> null)
        )
in
    Evaluate

[ImkeF · Accepted solution]

Thanks @artemus ,

that circumvents the combination of the forumula dependencies with the row context nicely.

Is this a general limitation when using Expression.Evaluate or do you think that the error is due to some specific settings within this sample?

@hnguyen76 ,

I've rewritten it slightly to make it easier to swap the sample code against your actual data.
Also, I've included a "List.Select"-statement to grab the correct value from evaluated conditions:

let
    ConditionsFromExcelTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykxT0lGKDkvNS8kvilWwVYhRCk7MLchJNYxRAkqUZKTmASmIkFKsDky9f1Fmun9BalFiSX6RZwpEX1JReoySQmJeikJ0cGVxSWouRNgxANmkoNTi0pwSQySj0NUq5BehGhDgj2yAY0CAP0J3SGpFiZ5zfl5JYmZesQbMGzoKcG8YxShpYvojFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Hypothesis = _t, Condition = _t, Conclusion = _t, Result = _t]),
    
    AddFunctionColumToConditions = let
        Source = ConditionsFromExcelTable,
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hypothesis", type text}, {"Condition", type text}, {"Conclusion", type text}, {"Result", type text}}),
        GenerateFunctions = Table.AddColumn(#"Changed Type", "Function", each Expression.Evaluate("each if " & [Condition] & " then """ & [Result] & """ else null", [Text.Contains = Text.Contains, Comparer.OrdinalIgnoreCase = Comparer.OrdinalIgnoreCase]))
    in
        GenerateFunctions,

    SourceData = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpOzC3ISTUCsYyBhG9inlKsTrSSEVwKpCg9H0i4FwWBpYzhUiBWUlE6kHQMAEuZwKVArKTUVKhULAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Vendor = _t, OrigOperatorId = _t, System = _t]),
    #"Changed Type" = Table.TransformColumnTypes(SourceData,{{"Vendor", type text}, {"OrigOperatorId", type text}, {"System", type text}}),

    Evaluate = Table.AddColumn(
        #"Changed Type", 
        "Custom", 
        each List.First(List.Select(List.Transform(AddFunctionColumToConditions[Function], (fn) => fn(_)), each _ <> null))
        )
in
    Evaluate

[artemus]

It is a bug, clear and simple, you can post a request to have it fixed a the ideas page. As much as I'd like this stuff fixed faster, I have to go though the same channels as everyone else to request the Power Bi team to work on stuff... unless it is a bug with Azure Data explorer connector, as the Power Bi team doesn't own that part of the code, and I can make fixes there (although I'm not on that team either).

[hnguyen76]

Hi @ImkeF  and @artemus ,
Just reading your exchanges while helping to solve this issue on the weekend is truly remarkable. I want to thank you both!! 

I have tested the sample provided and the results are as expected! I believe this is 100% a game changer which allows outside configurations to be made without actually touching the solution.  

At the applied evaluate step, I included a try-otherwise scope just in case the input condition is invalid. If so, default to null. 

Thank you both again!

PS, ImkeF, do you want me to create a post about the bug? I honestly am not sure what the bug is about but I can start it and link it to this thread. Let me know!

[artemus]

You will also want a try otherwise at the GenerateFunctions step, as this can fail if the syntax is bad. The Evalulation step will only fail if the code looks good, but there is a reference to a missing column or a wrong data type is used.

For the GenerateFunctions step, your code should look like:

..., each try Expressoin.Evaluate("each ...", ...) otherwise each null

In that if the function text is not valid, you want to return a function which always returns null.

[ImkeF]

Great to hear @hnguyen76 !

I've reported the bug here already: https://community.powerbi.com/t5/Issues/Exception-error-through-environment-in-Expression-Evaluate/i...

[hnguyen76]

@artemus, I've added the second try-otherwise on the GenerateFunctions based on your suggestion. Thank you!

@ImkeF, thank you very much! 

[hnguyen76]

@ImkeF ,

Question: I've noticed that the function calls / evaluates my conditions query again after every file within source folder. I've attempted to add a List.Buffer or Table.Buffer but have been unsuccessful. So, is it possible to load the conditions once and reference that per row?

[ImkeF]

Hi @hnguyen76 ,

not sure I understand.

Your conditions have to be evaluated on a row-by-row-basis.

Where do you see potential for skipping any evaluation?

[hnguyen76]

Hi @ImkeF ,

Sorry my previous post may have sounded confusing, hehehe. I didn't want to skip any evaluation but rather I wanted to find a way to only load the condition query just once in memory.

So to outline it a bit, I have about 100 files within a folder that I'm ingesting and the way it's currently evaulating / loading is as such:
1. ConditionsQuery.xlsx (3kb)
2. FileA (1kb)
3. ConditionsQuery.xlsx (1.2mb)
4. FileB (1kb)
5. ConditionsQuery.xlsx (2.89 mb)
6. FileC (1kb)
7. ConditionsQuery.xlsx (4.3 mb)
...
etc.
8. ConditionsQuery.xlsx (189 mb)

As you can see, as each new file is being loaded, my conditions query gets called over and over again starting from 3kb and just keeps climbing up and up. So, the way I want it to ideally load would be:

1. ConditionsQuery.xlsx (3 kb)
2. FileA
3. FileB
4. FileC
5. FileD

...
etc.

In any case, after tinkering with it a bit further, I kind of knew that I wanted to use some kind of buffer. After attempting to use List.Buffer() it didn't work out the way I had planned and Table.Buffer() at the EVALUATE step didn't work either. So I think by adding Table.Buffer() at the step before the EVALUATE applied step is working (i think) hehehe. I no longer see that it's continually loading the conditions query more than once.

[ImkeF]

Hi @hnguyen76 ,

will the conditions always be equalities ("=") ?

[hnguyen76]

Hi @ImkeF ,
Thank you for the support. There may be a one-off where it's Text.Contains([SampleField], "ABC", Comparer.OrdinalIgnoreCase)