cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Support
Community Support

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

@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}))

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Community Support
Community Support

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

@ghdunn,

 

Change it as follows.

 

    Result = Expression.Evaluate(
in
    Result

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Highlighted
Community Support
Community Support

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

@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}))

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper III
Helper III

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

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

______________________________________________________________________
Highlighted
Community Support
Community Support

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

@ghdunn,

 

Change it as follows.

 

    Result = Expression.Evaluate(
in
    Result

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper III
Helper III

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

Great job...thanks

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors