How to load a PQ function from a text file

Question

Just like in VBA where we can save functions macros in .xlam files and load them in another file. In power query I would like to store often-used functions in a central location and reference these locations when necessary.

I know that we can store power queries for later use by following this guide.

However, this tip does not work for functions.

Let's say I have a very simple function to get value of an Excel range:

(RangeName as text) =>
let
  RangeValue = Excel.CurrentWorkbook(){[Name=RangeName]}[Content]{0} [Column1]
in
  RangeValue

I have tried to link to this file as follows:

(RangeName as text) =>
  let
  GetFunction = Text.FromBinary(File.Contents(Excel.CurrentWorkbook(){[Name="PQMacrosFolder"]}[Content]{0}[Column1]&"RangeValue.txt")),
  EvaluateFunction = Expression.Evaluate(GetFunction, #shared)
in
  EvaluateFunction

(you see I am using the syntax of the function itself to get the folder path from the Excel named range "PQMacrosFolder".)

It shows the function is loaded into the Query Editor correctly.

But when I try to input some test values and invoke the function, it just shows the same function screen again.

user_10769002 · Answer

Here is the function to load a function from a text file and perform an action on a column. The function is loaded from a text file and the inputs are set in the same query. This assumes that the input variables are defined in two separate queries (also shown below).

Note, if you get a formula.firewall error, you will need to write the named range values into the same query or change your query options to ignore privacy settings. I prefer to keep them separate for readability.

let

    //Load M code from text file

    Source = Text.FromBinary(File.Contents(p_myTextFilePath)),

    //Evaluate the code from the file as an M expression
    EvaluatedExpression = Expression.Evaluate(Source, #shared),    

    //Invoke Function
    InvokeFunction = EvaluatedExpression(p_myTableName)

in

    InvokeFunction

Queries to get the values to use as the text file path and the table name (as named ranges).

p_myTableName Query

let
    Source = Excel.CurrentWorkbook(){[Name="nr_myTableName"]}[Content],
    Column1 = Source{0}[Column1]
in
    Column1

p_myTextFilePath Query

let
    Source = Excel.CurrentWorkbook(){[Name="nr_myTextFilePath"]}[Content],
    Column1 = Source{0}[Column1]
in
    Column1

REVISION #1

Below is the syntax for a function that loads a function from a text file. The table name is a parameter in the function that is in the text file, so when you invoke the function below, the function from the text file appears, ready for table specification.

(myTextFilePath) =>

let

    //Load M code from text file

    Source = Text.FromBinary(File.Contents(myTextFilePath)),

    //Evaluate the code from the file as an M expression

    EvaluatedExpression = Expression.Evaluate(Source, #shared)    

in

    EvaluatedExpression

This is a screen shot of the function above, before being invoked.

And here is another screen shot after the function is invoked, ready for the next parameter to be specified.

How to load a PQ function from a text file

1 Answers