Power Query: Evaluating Stored Queries
31 January 2018
Welcome to our Power Query blog. This week, I take a look at a way to store queries in a text file.
In my recent article It’s Good to Share (a Query), I looked at a few ways to share queries between workbooks. This week, I take a look at a slightly different approach, and it relies on the following M function:
Expression.Evaluate(expression as text, optional environment as [...]) as any
It should be noted:
- This evaluates a text expression and returns the evaluated value.
- Expression is the expression to evaluate
- optional environment is the expression environment.
That’s a very short explanation for quite a powerful function! What it does is to treat the expression as M language and then runs it. The best way to understand it, is to see it in action. Below I have some of my product data and I am going to create a simple query:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/01-jan/pq-61-image1.png/152727a9cc7f1eedae857279c3b288fd.jpg)
In the ‘Get and Transform’ section, I choose to create a new query ‘From Table’. I call my new query ‘Apply_Tax’ and I am going to add a new column to my query in the ‘Add Column’ tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/01-jan/pq-61-image2.png/06b9b6da8105eb5bf454216e85f68762.jpg)
I will use the ‘Custom Column’ to create my new column.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/01-jan/pq-61-image3.png/c1a54b58ca73bdddc24fa597b4535eb5.jpg)
I choose to add 20%, and create my new column. I opt to change it to datatype ‘Currency’ for information; the formatting doesn’t matter here as I can deal with that in my Excel Worksheet.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/01-jan/pq-61-image4.png/066ca4a021993c62719499071b35434c.jpg)
Today, I am interested in the M language generated behind the scenes. Here, I access the ‘Advanced Editor’ from the ‘Home’ tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/01-jan/pq-61-image5.png/386f2d23320f7ed67ce2d8544b1913ef.jpg)
I copy and paste this text to a text file on my PC.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/01-jan/pq-61-image6.png/934ddb410e3b1ee4459f76cc07f509d1.jpg)
If I go back to my original data, I can now use the M language stored in my text file. I discard the ‘Apply_Tax’ query and create a new blank query by choosing ‘From Other Sources’ and then ‘Blank Query’ from the ‘New Query’ option in the ‘Get and Transform’ section.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/01-jan/pq-61-image7.png/78c2dd471989df597fdca7960bb9e9e7.jpg)
I need to enter the M language below in order to run the code in my text file:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/01-jan/pq-61-image8.png/39d3e28cde6a27df715f69e5ed5b27b8.jpg)
let
Source=Text.FromBinary(File.Contents("C:\Users\kathr\OneDrive\Documents\PQ_Applied_Tax.txt")), EvaluatedExpression = Expression.Evaluate(Source, #shared)
in
EvaluatedExpression
I create my new query and check the results.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/01-jan/pq-61-image9.png/a32adaaef9ffb35aba3c6ce87ecbc6da.jpg)
The query has been executed correctly and I can easily share the text on an accessible drive for other users.
Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!