Parameterise Multiple Queries in Power Query

The purpose of this article is to demonstrate the simplistic power (excuse the pun) of Power Query and how you can configure numerous queries to run from one single parameter.  The concepts covered are similar to what was discussed in my last blog but instead of configuring iterated outputs, we will use one query to derive the data in a number of other queries.

Please Note:       Substantial ETL was required to prepare the data before any parameterisation could take place.  I will not be showing this in today’s blog but am happy to provide the full workbook upon request.  

Scenario

Continuing on with the football theme, I want to find out the Premier Leagues top goal scorer for any season, ranging from 1992/93 all the way up to the present day.  The aim is to pass a value into a workbook query, which will determine the data shown in our formatted, transformed query.

“This sounds exactly the same as you last blog post”, I hear you say.  Well, it is similar – but I will show you how to pass a given query value in as a parameter into another query.  In other words, we can use basic configuration to drive our output. 

Import Data

Firstly, to follow this example, you need to import the 15/16 premier league leading scorer web data into Power Query.  This can be found at:- http://www.transfermarkt.co.uk/premier-league/torschuetzenliste/wettbewerb/GB1/plus/?saison_id=2015

You will see that there are over 30 tables that could be imported.  Upon further inspection, each player is put into their own table and we will need to bring most of the tables in.  I decided to pick the top 20 players and any associated data we may need, which is Table 3 – Table 23.

Once the tables are opened as queries in Power Query, rename Table 3 to ‘Stats’, Table 4 to Rank1, Table 5 to Rank2, etc. – all the way up to Rank20.  The queries will now look like the below:

Config Table

If you haven’t already noticed in the above web address, ‘saison_id’ is the parameter we can pass different years into.  We will now set up a one value config query (using the Enter Data button) that will be used to derive ‘saison_id’.  Save the query and give your column a meaningful name – see below.

Referencing Config Value

The next step is to actually reference the config value and nest it into the web address connection of all our other queries but for this example, we will use the Rank1 query.  Use the Advanced Editor to edit the M code.  Here you will see the web connection but before we add our table value in as a parameter, we need to declare it – just like you would do in any other programming language.  The code looks as follows:

–          ‘YearParam’ calls the Config query, which I named ObtainYear.

–          ‘YearParamValue’ find the ‘{0}’ positioned record, which in Power Query, means the first row.  ‘[Year]’ is the name of the column and can be seen in a screenshot above.

–          Finally, ‘YearParamValue’ is called in the web connection string, replacing the hardcoded value of ‘2015’.

We can now close the Advanced Editor and test that the code works.  To do so, change the config query value to ‘2014’, by using the Replace Values option in the top menu ribbon. The value in Rank1 should now read as ‘Sergio Aguero’.

Apply the same code to all of the other queries so that all values will change when a different year is entered into the ‘Year’ column of ObtainYear.

Automating Config Query

To advance our workbook even further, we can turn the ObtainYear query into a function.   This will automate the result in all our other queries and enable the export of different datasets.  I have shown you how to create a function in a previous blog, but have still provided the syntax required at the beginning of the M code in Advanced Editor.

Finally, we can Invoke the function by entering a year.  In my example (not previously shown), I have invoked the year ‘1999’ in the ObtainYear function.  The below output is the result of a transformed query that brings all the data from the other queries together.

There it is, a fully dynamic import process that will connect to a web site and import data determined by a query parameter!  This is all achieved using Power Query’s intuitive UI, with the extra help of the more configurable and flexible M native language.

Other Uses

This article may use web scraping as the example, but you can adopt the same approach to looping through text files, database objects or blob storage.  I have now demonstrated how you can use both function and query values as parameters but with the potential to combine the two, creating ETL processes in Power Query could be very powerful.

I am just scratching the surface here and aim to explore more ETL automation very soon.  Rest assured I will share all my findings – so look out for future blogs.