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.
Introduction to Data Wrangler in Microsoft Fabric
What is Data Wrangler? A key selling point of Microsoft Fabric is the Data Science
Jul
Autogen Power BI Model in Tabular Editor
In the realm of business intelligence, Power BI has emerged as a powerful tool for
Jul
Microsoft Healthcare Accelerator for Fabric
Microsoft released the Healthcare Data Solutions in Microsoft Fabric in Q1 2024. It was introduced
Jul
Unlock the Power of Colour: Make Your Power BI Reports Pop
Colour is a powerful visual tool that can enhance the appeal and readability of your
Jul
Python vs. PySpark: Navigating Data Analytics in Databricks – Part 2
Part 2: Exploring Advanced Functionalities in Databricks Welcome back to our Databricks journey! In this
May
GPT-4 with Vision vs Custom Vision in Anomaly Detection
Businesses today are generating data at an unprecedented rate. Automated processing of data is essential
May
Exploring DALL·E Capabilities
What is DALL·E? DALL·E is text-to-image generation system developed by OpenAI using deep learning methodologies.
May
Using Copilot Studio to Develop a HR Policy Bot
The next addition to Microsoft’s generative AI and large language model tools is Microsoft Copilot
Apr