Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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}))
Change it as follows.
Result = Expression.Evaluate( in Result
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}))
Change it as follows.
Result = Expression.Evaluate( in Result
Great job...thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |