Please note javascript is required for full website functionality.
MVP

Blog

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!

Newsletter