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:
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:
I will use the ‘Custom Column’ to create my new column.
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.
Today, I am interested in the M language generated behind the scenes. Here, I access the ‘Advanced Editor’ from the ‘Home’ tab:
I copy and paste this text to a text file on my PC.
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.
I need to enter the M language below in order to run the code in my text file:
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.
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!
