expression.evaluate - import a snippet .not the whole script

[ghdunn]

Hi

I have a dataset transformation that is a stable/unchanging set of query steps except for a last conditional column step which is driven by a reasonably volatile set of business rules that I want to bring in from a 'rules engine'.

I have seen a number of posts that allow me to bring in a script from a text file using Expression.Evalutate:

let Source = Text.FromBinary(Web.Contents("Source.txt")),

Evaluate = Expression.Evaluate(Source, #shared) in Evaluate

However, I can only get that working if the external .txt file contains the entire power query script.  Instead, I would like to define the marjority of the script in Power BI itself, and then just call out to the file for the last step.

So ...my basic Power Query script might look like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvVW0lEyUorVATKDHYFsY6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [country = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"country", type text}, {"Column2", Int64.Type}})
in
    #"Changed Type"

But then I call out to my external file for the last row of the  script, using something like:

let Source2 = Text.FromBinary(Web.Contents("Source2.txt")),

Evaluate = Expression.Evaluate(Source2, #shared) in Evaluate

The Source2.txt file would simply contain the final Power Query step: 

#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [country] = "USA" then 1 else if [country] = "UK" then 2 else 3)

I have tried a number of combinations but just cant seem to get the syntax to work.

[v-chuncz-msft · Accepted solution]

@ghdunn,

Add Let expression and see code below.

let #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [country] = "USA" then 1 else if [country] = "UK" then 2 else 3) in #"Added Conditional Column"

Expression.Evaluate(Source2, Record.Combine({[#"Changed Type"=#"Changed Type"], #shared}))

[v-chuncz-msft · Accepted solution]

@ghdunn,

Add Let expression and see code below.

let #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [country] = "USA" then 1 else if [country] = "UK" then 2 else 3) in #"Added Conditional Column"

Expression.Evaluate(Source2, Record.Combine({[#"Changed Type"=#"Changed Type"], #shared}))

[ghdunn]

Sam,



Thanks for reply. Still can't get that to work though. Script now looks like this:



let
Source = Excel.Workbook(File.Contents("H:\MyBook.xlsx"), null, true),
Source2 = Text.FromBinary(File.Contents("H:\MyScript.txt")),
MyTable_Table = Source{[Item="MyTable",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(MyTable_Table,{{"Name", type text}, {"Address", type text}, {"Children", Int64.Type}}),

Expression.Evaluate(Source2, Record.Combine({[#"Changed Type"=#"Changed Type"], #shared}))

in ????



With this script I am getting 'Token Equal Expected'



what should the 'in' point to.



and text file looks like:



let #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [country] = "USA" then 1 else if [country] = "UK" then 2 else 3)

in #"Added Conditional Column"



Your support is very much appreciated

Gerald

______________________________________________________________________

[v-chuncz-msft · Accepted solution]

@ghdunn,

Change it as follows.

    Result = Expression.Evaluate(
in
    Result

[ghdunn]

Great job...thanks