Use Expression.Evaluate to apply a custom If..the..else statement stored as a string??

[DBCooper]

Hi All,

I'm new to Power Queries and have only been playing with them for a few days, and I have a problem that I am trying to solve that I hope someone can help out with.

I would like  like to add a custom column to my query that processes an varying if statement that has been built and stored as a string in another field.  Note that this particular if statement will have varying IF conditons and depth based on the information contained in another field in the record which is decoded. 

In the past in Excel using VBA I had been able to achieve a similar resulte using the Evaluate function, and I have seen in M that there is a Expression.Evaluate function that I think should do what I am after but it throws an error.  And I'm sure its something that I'm just not understanding about the language that is trapping me here.

For Eaxample I have a Table with fields: Para1, Para2, Equation where Para1 & 2 columns contain string values and the Equation column contains an if statement in the form of a  string.  For example one record may have the following Equation stringexample

if [Para1]="DIRECTION" and [Para2]<>"DEV" then true else false

I want to add a custom colum and process this if statement held in the Equation column for the record.  I have tried Expression.Evaluate([Equation]) but it doesn't work and I get the following error "Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?"

Can someone confirm if what I am trying to do is possible, and how it is supposed to be done.

Thanks heaps.

[OwenAuger]

Hi @DBCooper

It turns out I have had to do something very similar before.

To make this work, a second argument is needed in Expression.Evaluate, namely:

Record.Combine({[ _=_ ],#shared})

Now, I can't explain why this works, but I picked it up from a blog post somewhere.

This looks like a relevant post but not the exact one where I found this pattern:

https://blog.crossjoin.co.uk/2015/02/06/expression-evaluate-in-power-querym/

So your Expression.Evaluate call should end up looking like this:

Expression.Evaluate( [Equation], Record.Combine({[_=_],#shared}) )

Here is a sample query - paste this into a blank query in the Advanced Editor, and you will be able to see it in action and adapt to your situation.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvEMcnUO8fT3U9JR8vMPUXBxDQOyMtMUogMSixINY21jEEpilBQS81IgMkaxMaUGBsbJYDIVqMg1DChdkpGap1BSVJqqkJpTnKqQlggklWJ1opVCXINDgOa6hPr6RlLd/FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Para1 = _t, Para2 = _t, Equation = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Para1", type text}, {"Para2", type text}, {"Equation", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Result", each Expression.Evaluate( [Equation], Record.Combine({[_=_],#shared}) ), type logical)
in
    #"Added Custom"

Regards,

Owen

[DBCooper]

Hi @OwenAuger
Thanks for the response. No way known that I would have been able to guess that syntax.
I'll try your code out tomorrow and let you know how I go.

Cheers
DBC.

[v-yulgu-msft]

Hi @DBCooper,

If you have resolved this porblem, would you please kindly mark the helpful reply as an answer or sharing your solution so that it can benefit more community members?

Thanks,

Yuliana Gu

[MB__]

Have any other solutions been identified for this?

I have a number of custom columns with formulas to add as steps to a Power Query.

I have a table In excel (imported into Power Query) with information about the new columns.

In simple terms, the table has 3 columns (NewColumnName, NewColumnType and NewColumnFormula) where NewColumnFormula holds the expression for each new column as a text string.

I have the same problem that Table.AddColumn enters the formulas into every row of the new columns as a text value and I cannot find a way to convert the text so that it is used as an expression.

The following suggestion did work and the results of the formulas appear in every row of the new column however I understand that this is not always stable and I would prefer to see the expressions in the columns rather than just the evaluated values.

Expression.Evaluate( [Equation], Record.Combine({[_=_],#shared}) )

This is the only thread that I have found the breaks my problem down to the fundamental issue of using a text string as an expression within Power Query.

I will carry on adding every custom column as a separate step by entering the expression into the custom column formula box however this just feels inefficient when all the information is already imported into a table.

Any comments or suggestions would be appreciated, especially for future models that will need to be created and maintained.

[Anonymous]

Hello, did you find a solution to your problem somewhere else? thanks