Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Loading Power Query M Code From Text Files

with 18 comments

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:

image

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:

image

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

image

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.

Written by Chris Webb

February 4, 2014 at 3:32 pm

Posted in Power Query

18 Responses

Subscribe to comments with RSS.

  1. 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.

    dch1dch2

    February 4, 2014 at 8:32 pm

    • 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.

      Chris Webb

      February 5, 2014 at 11:03 am

      • 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?

        Derik

        March 4, 2014 at 3:54 pm

      • 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?

        Chris Webb

        March 4, 2014 at 4:03 pm

      • 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

        Derik

        March 4, 2014 at 5:34 pm

      • 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.

        Chris Webb

        March 4, 2014 at 9:19 pm

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

      Derik

      March 4, 2014 at 5:16 pm

  2. 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.

    Bertrand

    February 5, 2014 at 8:35 am

    • 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.

      sr

      October 3, 2014 at 3:16 pm

  3. 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.

    Curt Hagenlocher

    February 5, 2014 at 9:22 pm

    • 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…

      Chris Webb

      February 5, 2014 at 9:26 pm

      • You could obviously store the entire M code in worksheet cells, if you had to.

        dch1dch2

        February 6, 2014 at 12:05 am

    • 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.

      George Qiao

      February 6, 2014 at 12:31 am

      • 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 :).

        Curt Hagenlocher

        February 6, 2014 at 1:01 am

  4. 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?

    Jimmy Glenn

    August 11, 2014 at 10:57 pm

    • 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.

      Chris Webb

      August 12, 2014 at 9:19 pm

      • Thanks Chris! I’ll have your book via Amazon Prime in two days.

        Jimmy Glenn

        August 14, 2014 at 3:32 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,301 other followers

%d bloggers like this: