A Simple Worksheet Parameter Pattern for Power Query

How cool would it be to call a value from an Excel Worksheet from within a Power Query using a simple variable definition?

me@jaykilleen.com wrote this over 2 years ago and it was last updated about 1 year ago.


← Back to the Posts

Almost every Excel file I open these days starts with the creation of an Excel table called params that I then pass through to Power Query to be used in some other power query data extraction/transformation.

After a few years, this is the pattern I have settled on and I love it!

Clearly stealing from smarter people than me with the likes of:
Ken Puls
Matt Allington

params

Create a table in an Excel worksheet (I simply call the worksheet 'params'). It has two columns name and value that represents the variable name and its value. I think of this like a json key value pair.

name value
account_id 12345678
material_id 12345678
username =MID(INFO("DIRECTORY"),10,LEN(INFO("DIRECTORY"))-LEN(MID(INFO("DIRECTORY"),FIND("",INFO("DIRECTORY"),10),1000))-LEN("C:\Users"))
path =LEFT(CELL("filename",B6),FIND("[",CELL("filename",B6),1)-1)
## params.m Now pull that table data into Power Query using the following m query.
let
    Source = Excel.CurrentWorkbook(){[Name="params"]}[Content],
    SetType = Table.TransformColumnTypes(Source,{{"name", type text}, {"value", type text}})
in
    SetType 

Underscore (_)

The underscore _ function now allows you to call your params within other queries like _("path") (where path is the name of the parameter). I use underscore just to keep things super lean.

let 
    Source = (name) => let
        Source = params,
        FilterRows = Table.SelectRows(Source, each ([name] = name)),
        value = FilterRows{0}[value]
    in
        value
in
    Source

customer_id

You can take this one step further by defining queries that are named after your params names that then pluck the values. In this example, I have a param called customer_id with the value 12345678. With this simple query below I can drop customer_id in any other query instead of _("customer_id"). It could be a meaningless step and the extra work might not be much payoff (this is why I use _ as the function name as opposed to get_params or something that is long).

let
    Source = _("customer_sid")
in
    Source

Similar