Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hnguyen76
Resolver II
Resolver II

Power Query Configurable / Dynamic If Statement

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:

 

HypothesisConditionConclusionResult
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:

IDVendorOrigOperatorIdSystem
1Sample2S3Man
2Sample1goGrR
3Sample3brgAP
4Sample4beeAP

 

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

 

IDVendorOrigOperatorIdSystemTest
1Sample2S3Man 
2Sample1goGrRSample
3Sample3brgAPResult1
4Sample4beeAPAPPO

 

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

1 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

 

 

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

19 REPLIES 19
v-xuding-msft
Community Support
Community Support

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

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

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/.

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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++

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?

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.

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:

 

image.png 

 

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

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

 

 

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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).

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!

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.

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...

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

@ImkeF, thank you very much! 

 

 

@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?

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?

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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
Super User
Super User

Hi @hnguyen76 ,

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.