Forum Discussion
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.
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
Community Support
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
Helper 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
Community Support