PowerQuery Parameter in Excel

I share in this post how you can define Parameters for Power Query in Excel.

Problem description

In Excel/ PowerQuery it makes sense to use Parameters.

PowerQuery parameters are not so accessible from the Excel user. You need to open the query editor.

For a basic Excel user, the Power Query part behind can be invisible and he might have no knowledge about how to use it and configure parameters at all.

A convenient way for such users is to offer the possibility to configure the parameter in an Excel sheet directly. 

Solution / How-To

The trick to make PowerQuery Parameter accessible is an Excel sheet is to use a Configuration Sheet and store the parameter in a named Cell.

You can then import this parameter Value in Power Query with following one-liner M-code

let

    ParamName = Excel.CurrentWorkbook(){[Name = "ParamName"]}[Content]{0}[Column1]

in

   ParamName

Update ParamName here to your need.

Configure this query step to "Connection only..." so it isn't loaded and duplicated in Excel.