Run Your Power Query M Code Procedures in #Excel Worksheet Cells by David Hager

 

The ability to reuse Power Query M procedures has been for the most part reserved for those capable of purchasing the full-blown Power BI package. Wouldn’t it be great for anyone owning Excel to benefit from a way to store and run M procedures? Well, I believe that you have come to the right place.

The inspiration behind this technique came from Chris Webb’s article for running M code from text files.

https://blog.crossjoin.co.uk/2014/02/04/loading-power-query-m-code-from-text-files/

In the comments section of that article, there was a discussion of the portability of the text files to other potential users. I then made the following comment: “You could obviously store the entire M code in worksheet cells, if you had to.” Well, nothing was done with this idea, and I had forgotten about it until now. I decided to use a named cell to hold the M code, as described in this article.

https://blog.crossjoin.co.uk/2014/07/22/working-with-excel-named-ranges-in-power-query/

Chris helped me to work through a few issues I had in making this. Thanks for your help, Chris!

Here is the M code that utilizes the M procedure stored in a named range Excel cell (MCode).

let

Source = Excel.CurrentWorkbook(){[Name=”MCode”]}[Content],

ChangeDataTypes = Table.TransformColumnTypes(Source,

{“Column1”, Text.Type}),

GetMCode = ChangeDataTypes{0}[Column1],

EvaluatedExpression = Expression.Evaluate(GetMCode, #shared)

In

EvaluatedExpression

I decided to use Matt Allington’s calendar table M code for testing (great M code, Matt!)

https://powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/

When I copied the code from the article, I found that there was no way to paste the entire code into 1 cell, mainly because multiple lines of text are viewed by Excel as one line per cell. So, I ended up with 25 lines of code in cells A1:A25. Thankfully, the TEXTJOIN function provides a way to assemble those lines of code so that they are readable by Power Query. The formula =TEXTJOIN(” “,,A1:A25) returns a single string with spaces between the lines of code, which appears to be necessary for the code to run correctly from a single cell. In this case, the named range cell is called MCode (cell C1). So, the main code pulls in the single cell table and transforms it into text. That M code text was run using Expression.Evaluate to return the Power Query query calendar table. The named cell MCode can either contain the TEXTJOIN formula associated with the lines of code desired or you can Copy, Paste Special Values to make a string that can be stored. In the example file, cell C4 contains the code string for the main M procedure, which you can copy/paste into the Power Query Advanced Editor to run. The stored M procedures (in this case only one – in cell C5) can be either placed in the MCode cell or a formula can be used to return the desired procedure.

I have removed the calendar table query result, but you can reproduce it if you like. I do not plan to make a storehouse of M procedures from this (at least, not one I am willing to share😊), so feel free to use this technique as you desire. IMHO, the ability to potentially store and use 1000’s of M procedures in a portable way is exciting.

The example file can be downloaded here.

MCode_RunFromNamedCell