Adding Run Time Parameters to Excel Power Query

The Benefit of Parameters

Take the scenario where you have a workbook with generic data connections that needs to be distributed to several different users, each with their own unique set of run time requirements meaning that the generic connections need tweaking for each one.  That is fine if there are only one or two connections – the queries can be edited, one by one, in each copy of the workbook, before sharing them.

However, what if the parameters could be declared in the workbooks and by choosing the Data | Refresh All option the entire workbook would then be good to go?  No problem!

A Simplistic Example

For the example, here is a data query that gets a simple data set (a subset of the American Trends Panel, courtesy of http://www.pewsocialtrends.org):

If you look at the steps that went into getting this data in the Power Query Advanced Editor, you would see something familiar like this:

let
    Source = Csv.Document(File.Contents("C:\Documents\American Trends Panel Wave 28\ATPW28.dat"),[Delimiter=",", Columns=139, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Use First Row as Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Removed Other Columns" = Table.SelectColumns(#"Use First Row as Headers",{"QKEY", "Device_Type_W28", "LANGUAGE_W28", "FORM_W28", "EXPECTLIFE_W28"}),
    #"Change Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"QKEY", type text}, {"Device_Type_W28", Int64.Type}, {"LANGUAGE_W28", Int64.Type}, {"FORM_W28", Int64.Type}, {"EXPECTLIFE_W28", Int64.Type}}),
in
    #"Change Type"

Now, let’s say we need two users – each to work on the data for one of the languages in the “LANGUAGE_W28” column (which could be “9” = English or “10” = Spanish) so we need to share only the relevant language data sets with them.  To update the query prior to sharing the workbook, of course, the following step could be added:

= Table.SelectRows(#"Change Type", each ([LANGUAGE_W28] = 9))

It can be seen, as the example stands, that the English speakers data set would be selected.  It would be necessary to modify the value being compared in the query to 10 to get the Spanish speakers data set.

Take a look at this more generic filter:

= Table.SelectRows(#"Change Type", each ([LANGUAGE_W28] = language_parameter))

All we need now is a way to get language_parameter to be set – on the fly – to  the value we would like to be compared to and upon which to filter the data set.  First of all, create a lookup table in the workbook:

Next step, is to allow the user to select the language they would like to filter on – by name rather than code is my preference, being much more user-friendly and less prone to error.  Create a new table – Table_Parameters – in the workbook for the run-time parameters):

The second column in the table uses a VLOOKUP to fetch the code from our languages table.  That way the user chooses the language in the drop down and the code is updated.   The drop down is enabled through Data Validation options in Excel – I’ll be covering that in more detail in a future post.

The final step then is to get Power Query to grab the language code from the table during execution.  In the Advanced Editor for the query, create a reference to the table:

Workbook_Parameters = Excel.CurrentWorkbook(){[Name="Table_Parameters"]}[Content],

Make sure that the Language_Parameter is going to be the same data type as the column in the original data set:

parameters = Table.TransformColumnTypes(Source,{ {"Language_Parameter", Int64.Type}})

And – finally – set the language_parameter variable that is going to be used in the filtering later on:

language_parameter = parameters{0}[Language_Parameter],

in the line above, language_parameter is being set to the single value that is in the Language_Parameter column (part in square brackets) of the first record (part in curly braces) in the parameters table.  Records in tables start from record zero, so the {0} seen above means the first record.

Now, the desired final data set is easily obtained by choosing the language in the drop down and then pressing the Data | Refresh All menu option.  No need to edit the Power Query ever again – even if additional language options are added in the future.

Here is the final Power Query in full:

let

Workbook_Parameters = Excel.CurrentWorkbook(){[Name="Table_Parameters"]}[Content],
parameters = Table.TransformColumnTypes(Workbook_Parameters,{{"Language_Parameter", Int64.Type}}),
language_parameter = parameters{0}[Language_Parameter],

Source = Csv.Document(File.Contents("C:\American Trends Panel Wave 28\ATPW28.dat"),[Delimiter=",", Columns=139, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Use First Row as Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Removed Other Columns" = Table.SelectColumns(#"Use First Row as Headers",{"QKEY", "Device_Type_W28", "LANGUAGE_W28", "FORM_W28", "EXPECTLIFE_W28"}),
#"Change Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"QKEY", type text}, {"Device_Type_W28", Int64.Type}, {"LANGUAGE_W28", Int64.Type}, {"FORM_W28", Int64.Type}, {"EXPECTLIFE_W28", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Change Type", each ([LANGUAGE_W28] = language_parameter))
in
#"Filtered Rows"

Another thing that can be achieved with parameters like this is to add a form of dynamism, within Power Query scripts, to column names in tables – something that isn’t usually possible with Power Query.  I will cover this, with examples, in a future post.