Forum Discussion

ghdunn's avatar
ghdunn
Icon for Helper III rankHelper III
7 years ago
Solved

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.

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

     

4 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support

    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's avatar
      ghdunn
      Icon for Helper III rankHelper III
      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's avatar
        v-chuncz-msft
        Icon for Community Support rankCommunity Support

        ghdunn,

         

        Change it as follows.

         

            Result = Expression.Evaluate(
        in
            Result