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

Question

Hi All,&nbsp;&nbsp;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.&nbsp;I would like&nbsp; 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.&nbsp; 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.&nbsp;&nbsp;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.&nbsp; And I'm sure its something that I'm just not understanding about the language that is trapping me here.&nbsp;&nbsp;For Eaxample I have a Table with fields:&nbsp;Para1, Para2, Equation where Para1 &amp; 2 columns contain string&nbsp;values and the Equation column contains an if statement&nbsp;in the form of a&nbsp; string.&nbsp; For example one record may have the following Equation stringexampleif [Para1]="DIRECTION" and [Para2]&lt;&gt;"DEV" then true else false&nbsp;I want to add a custom colum and process this if statement held in the Equation column for the record.&nbsp; 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?"&nbsp;Can someone confirm if what I am trying to do is possible, and how it is supposed to be done.&nbsp;Thanks heaps.&nbsp; &nbsp;

owenauger · Answer

Hi&nbsp;DBCooper&nbsp;It turns out I have had to do something very similar before.&nbsp;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/&nbsp;So your Expression.Evaluate call should end up looking like this:Expression.Evaluate( [Equation], Record.Combine({[_=_],#shared}) )&nbsp;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.&nbsp;&nbsp;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"&nbsp;Regards,Owen&nbsp;

dbcooper · Answer

Hi OwenAugerThanks 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.CheersDBC.

v-yulgu-msft · Answer

Hi&nbsp;DBCooper,
&nbsp;
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?
&nbsp;
Thanks,
Yuliana Gu

mb__ · Answer

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.&nbsp;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.&nbsp;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.&nbsp;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.&nbsp;Any comments or suggestions would be appreciated, especially for future models that will need to be created and maintained.

anonymous · Answer

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

Forum Discussion

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

5 Replies

Related content

OR Statement ?

SQL Statement opcion error

Support for MERGE statement

Expression.Evaluate with List.MatchesAny

Else if function displaying the max date