One of the things that has intrigued me in the Power Query docs for a while now is the Expression.Evaluate() function, which takes some text and evaluates it as a Power Query expression (rather like good old StrToSet() in MDX). I can think of lots of fun things you can do with this, but here’s one very cool example: it allows you to store the M code for your Power Query query in a text file outside your Excel workbook.
Here’s an example. Consider the following Excel workbook, which has a table named FruitSales in it:
I can use this table as a source for the following simple Power Query query as follows:
let
Source = Excel.CurrentWorkbook(){[Name="FruitSales"]}[Content],
GroupedRows = Table.Group(Source, {}, {{"Sum of Sales", each List.Sum([Sales]), type number}})
in
GroupedRows
Here’s the output:
No surprises so far. Next, I copy the code for the Power Query query above and paste it into a text file; in my case I’ve saved my file at C:\PowerQueryQueries\DynamicQuery.txt
Back in Excel, I can now load the code stored in this text file in a new query using the following code:
let
//Load M code from text file
Source = Text.FromBinary(File.Contents("C:\PowerQueryQueries\DynamicQuery.txt")),
//Evaluate the code from the file as an M expression
EvaluatedExpression = Expression.Evaluate(Source, #shared)
in
EvaluatedExpression
The end result is exactly the same as in the previous example, except that in this case the code to read the data from the table and to aggregate it is loaded from the text file and is not stored inside the workbook.
The Source step here is fairly straightforward – it just loads text from a file into Power Query. It’s the next step where the magic takes place: as I said, Expression.Evaluate() takes the text and evaluates it as an expression, but it’s the second parameter which defines the environment that the expression evaluates in that seems to be the key to making this work. The chapter on “Sections” in the Power Query Formula Language specification document has a little bit more explanation of what environments are but I have to admit I’m not 100% clear on how all this ties in to M as it is implemented in Power Query today.
For those of you who can’t afford a Power BI subscription, this technique allows you to share queries between multiple workbooks without a Power BI site. In fact it has some advantages over sharing a query in Power BI because it always reads the definition of the query from the file, and so it will always use the latest version of your query – in Power BI, when you use a shared query you take a copy of the query and it is not updated even if the original author shares a newer version.
Discover more from Chris Webb's BI Blog
Subscribe to get the latest posts to your email.
This really is a revolutionary discovery. If it took (you) this long to find this, I’m betting that we would have never heard about it from Microsoft.
Well, I don’t think Microsoft understand (or should be expected to understand) why this is so important. That’s where the community comes in.
BTW if you think this is important, I’m getting help from MS on something in Power Query that is apparently there (though I can’t get it working yet) and which is even cooler than this.
Any progress on this other discovery?
I would like to ask if this type of method can be used with function invokation as well. I have attempted to post a date table function to a website as a text file, then call the function from there passing in local arguments to the web-saved function. I get the error that if cannot convert the text file value to type function. the function innards are wrapped in a let-in container, but i have tried with and without a containing let-in container around the declaration with the same effect.
Any thoughts or experience with invoking functions stored outside the excel file?
The other discovery turned out to be (in my opinion) a bit inconsistent in the way it’s implemented so I’m not going to blog about it until I think it’s ready. It is cool though, trust me!
Re your question, I suspect you would need a separate query in your workbook that read the text from the file and used it to create a function before you tried to call it in another query. Is this what you are doing?
I did end up getting the solution on that link very quickly. It turns out I should have also used expression.evaluate per your example here before attempting to invoke the function. I think that I deleted that critical step because originally I had formatting errors in the syntax of the function I posted which I misinterpreted.
Thanks again
Unrelated, but I wanted to point out that you can do looping in excel with recursive functions – you had stated that you cannot loop in PQ a presentation that you graciously linked to your blog – see my example here: http://youtu.be/XQLQYeSLfqs
Glad to hear you found a solution. Re loops, yes, I know about recursive functions in PQ (see this blog post for example: http://cwebbbi.wordpress.com/2013/06/22/flattening-a-parentchild-relationship-in-data-explorer/) and there are lots of ways to do things like loops, such as using List.Generate(); I think what I was trying to say was that strictly speaking there is no way to create a loop but there are other ways of achieving the same thing.
I have posed the same question here, (http://social.technet.microsoft.com/Forums/en-US/263ecffe-28ee-4e71-94e9-48529f7e30bd/convert-text-to-function-type?forum=powerquery). we will see where the solution comes first.
Basically I am finding that powerquery does not like casting text as a function (and hence functions stored as text cannot be invoked from text at this time if I have not made a mistake).
My same query is as follows:
[
let
Source = Text.FromBinary(Web.Contents(“www.com”)),
Eval = Function.Invoke((Source), {#date(2012,1,1), #date(2013,1,1)}), //FAIL
A = “(x as number) => (1 + x)”,
AA = ((x as number)=> 1+x) as function, //SUCCESS
InvokedAA = AA(6), //SUCCESS
fy=(y)=> 1+y, //SUCCESS
AEval = Function.Invoke(A,{4}), //FAIL
InvokedAA = A(5), //FAIL
B = Function.Invoke(InvokedAA,{4}), //SUCCESS
Y = fy(4), //SUCCESS
AAA = Function.Invoke(“(x)=>1+x”,{4}), //FAIL
AYY = Function.Invoke((x)=>1+x,{4}), //SUCCESS
AAY = ((x as number)=> 1+x) as function, //SUCCESS
Fail = A as function //FAIL
in
Fail
]
In short the following lines illustrate the point
Output = Function.Invoke(“(x)=>1+x”,{4}), //FAILS
Output = Function.Invoke((x)=>1+x,{4}), //SUCCEEDS
Output = ((x as number)=> 1+x) as function, //SUCCESS
Output = A as function //FAIL (A WAS TEXT COPY OF ABOVE)
Brilliant. We can now effectively share queries between workbooks. One PQ improvement that I am expecting is a vba API to export the query code. This would facilitate the creation of workbooks design documentation. Your technique offers an alternative: documentation and workbooks can both point to the same text file and remain in sync.
Don’t wish to be harsh but this all seems a bit backwards to me. Technology is just going full circle because we don’t know how to cope with the amount of data we have. We need better ideas not re-skinned old ideas. You can already share queries between workbooks using managed data sources and load code from flat files in a dozen other languages. Having worked on many projects automating actuarial and finance processes into distributed warehouses which involves trudging through hundreds of linked spread sheets with out date and inconsistent data and logic. There is already enough ways to hide logic and create horrifically over complicated file linked spread sheet systems that are totally unmanageable when you scale them up and out.
This has the obvious drawback that the spreadsheet is no longer as portable because the query doesn’t travel with it any more.
It also has the more subtle drawback that it doesn’t play very nicely with the static analysis that we sometimes do on M code. The M language, runtime and security model were designed for fully dynamic execution, but there are times that we try to optimize things with a static analysis.
It’s also very likely that the initial support for refreshing a Power Query from inside SharePoint will only succeed if we can fully analyze the query with static analysis.
You’re spoiling my fun 🙂
I think there are arguments both in favour and against bundling the spreadsheet and the query together, and in some scenarios it’s very useful to split the two. Bad news about the static analysis though…
You could obviously store the entire M code in worksheet cells, if you had to.
Externalizing the query logic is important. Not only it makes the code clean but also greatly improve the product capabilities. The reason why R is so popular is because it can easily import external packages which does all sort of analysis without writing your own. If power query is able to provide similar feature like “import package/functions” it will truly enable the self-service ETL. You can imagine 3rd party library or company’s function libraries developed by IT. The business user would be simply import the function and invoke it.
The Power Query product team is well aware of the importance of sharing and reuse. The official vehicle for this is, of course, Power BI. With a Power BI account, you can already write queries or functions and share them with your organization. But as Chris points out, there’s room for a lot of improvement over what we make available today.
Personally, I expect that in the future there will be both a much better experience around function libraries in Power BI as well as support for sharing them across organizations. You might then imagine that Chris has written a set of M functions that can load data from Salesforce.com and has shared them into a globally-accessible library. I would then be able to find these by doing a search, and reference them from my query. If a new version of the library was published, I could be notified that it is available and decide whether or not to use it.
Right now, these are all just ideas; we’re not even ready to engage in detailed internal discussion about implementing them. Certain aspects like security, data privacy and versioning have to be gotten just right. But we’re definitely thinking about these scenarios and very much want to enable them and others like them.
Disclaimer: I’m a developer, not a decision-maker, and nothing I say should be construed as an official plan or commitment to implement functionality in any timeframe whatsoever :).
Is there a way to use variables in a query?
For example, I’ve made the mistake of working on a PQ project in a folder on my desktop. I have my PQ workbook and all of my source files (.xlsx and .csv) in a folder on my desktop. My source for each PQ is like this:
Source = Excel.Workbook(File.Contents(“C:\Users\jimmy\Desktop\testingPQ\someReport.xlsx”)).
Now I want to move the PQ workbook somewhere other than my desktop, but doing so means that I have to change each PQ source to the new location. Occasionally, I want to show my awesome PQ setup to a co-worker. If I email everything over to Jack’s PC, the file contents will be saved in “C:\Users\Jack…” My PQ setup is not very impressive after spending thirty minutes fixing my source settings.
Is it possible to point a variable in PQ to a cell that’s set to =INFO(“DIRECTORY”), then use that variable to make my PQ workbook portable?
Yes, you can use the output of one query as a value in another. There are some examples of how to do this out there, for example this one:
http://blogs.msdn.com/b/powerbi/archive/2013/07/07/getting-started-with-pq-and-pm.aspx
Also my Power Query book covers this in some detail. Basically, you need to create a second query that uses the cell containing the directory name as its data source, and which outputs that directory name. You can then use that query as a variable in your main query.
Thanks Chris! I’ll have your book via Amazon Prime in two days.
Related to the accessibility of the M code is a use case for a find and replace function for development purposes.
Would be great to be able to iterate through M code in multiple Power Query’s in a workbook, to search eg to find variables, etc.
Would be even better to have a find and replace function for development purposes.
I’ve got Excel files with many Power Queries each with scores of steps. Would be great to be able search and find and replace!
I just would like to thank you for this post, it saved my day.
WOW
Thanks for the file SQL trick, it’s the best
This is a fantastic solution for query code management until Microsoft rolls out official support for Query sharing in Enterprise environments.
A thought and a concern:
1) If only there were a text editor that provided the Advanced Editor functionality + other syntax hand-holders (coloring, method-storing, etc), then we more easily develop in the same text files we call the queries from.
2) One problem with this solution is that it appears the Power BI cloud service doesn’t support the Expression.Evaluate method. I tried to load a PBI desktop file to my enterprise PBI service, and then sync the (.txt) code repository folder enterprise-wide through the gateway. The Desktop file will load, but you can’t refresh the Dataset in the cloud, as “this format is not currently supported.” Alas
I created an “idea” for this problem. Hopefully they can address this.
Please upvote:
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/18352027-support-refresh-from-the-web-when-running-code-scr
Great post!!! When loading in powerpivot a query created through this method (they are correctly created in PQ) I get several errors such as “HRESULT: 0x800A03EC” or “[Expression.Error] [7,83-7,110] The name ‘Xxxxxx” does not exist in current context”.that didn’t appear before (pasting all the M code in the query instead of “Expression.Evaluate()”). I’m not sure if “Expression.Evaluate() function” is the problem, but obviously it is affecting data loading.
Thank you Chris for sharing and educating.
I have the same problem
OLE DB or ODBC error: [Expression.Error] [5,42-5,56] The ‘RZBR File Path’ doesn’t exist in the current context..
The ‘RZBR File Path’ is a parameter which is found in the pbix file but is reffered also in the file text.
Do you know how to solve this one?
Thnak you!
Thank you Chris for sharing and educating.
I have the same problem
OLE DB or ODBC error: [Expression.Error] [5,42-5,56] The ‘RZBR File Path’ doesn’t exist in the current context..
The ‘RZBR File Path’ is a parameter which is found in the pbix file but is reffered also in the file text.
Do you know how to solve this one?
Thnak you!
I don’t think it’s possible to reference other queries (and parameters are queries too) in the code you use with Expression.Evaluate(). Curt’s answer here seems to explain why: https://social.technet.microsoft.com/Forums/en-US/aa6e667f-fa2b-4922-8d81-8b306a492395/restrictions-when-using-expressionevaluate?forum=powerquery
What would be feasible solution based on this if there are more than one query in a workbook?
Now I’m trying to do it like this:
– Export every query from “development workbook” one by one in separate files
– In “distributed workbook” load every single files as a separate query
would this work?
I want to have different files for different users (with multiple queries, pivot tables etc.) but I also want to have the same basic (and not so basic) queries from one source in all those files.
Hi Chris,
I picked up on this idea of PowerBI queries outside PBI, and incorporated into my previous work.
Have a look at how I solved this problem – https://www.datamart4excel.com/how-to-create-apps.html
Antonio
Although this post is old, I have tried this technique to hide M code from the end user successfully in Power BI, but when trying to refresh the data in the service it doesn’t recognize the origin and doesn’t allow to refresh. Is there a solution for this?
Hi Chris,
This has been extremely useful. However, my client requires a variation to the 4-4-5 whereby they want to incorporate a 53rd week. How do I go about making provision for it in the code
Hi Chris good post and very relevant still today after 7 years 😂
I come to you for advise. After few months failing, appreciate if you can lend me a hand. Using the Evaluate. Expression method I can maintain the development/version of my custom functions in Github and immediately working in excel and powerbi. However how I can suscesfully hide the M .pq file content from users since the users could copy the url and open it in any browser and see the content
Any idea, hint or lead is very much appreciated
Jon