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 about 4 years ago and it was last updated almost 3 years ago.
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
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) |
let
Source = Excel.CurrentWorkbook(){[Name="params"]}[Content],
SetType = Table.TransformColumnTypes(Source,{{"name", type text}, {"value", type text}})
in
SetType
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
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