In this post we will load a list of stations with their longitude and latitude and create two queries, list of stations and list of station data. The station data will be generated by invoking the power query function written in the previous posts.This post continues on from the previous 2 posts and looks at how to invoke power query function to fetch data from multiple files using a list of stations as an initial data source. The details of how to create the initial query can be found in post 1 and creating a function based off that query can be found in post 2. Post 2 finished with a function that could be invoked with a parameter of a station name.
The first task is to load and prepare the list of stations. I have prepared a list of the stations including their latitude, longitude and a region.
The file for this can be found here
We now need to duplicate the StationList, make a few minor changes and then invoke the function to fetch all the stations’ data. NOTE : We are not using reference to use the stationlist as this causes some problems with data security that I have not yet found an answer to.
The weather data includes extra indicators on the numbers to highlight various things. This means that some of the numbers are followed by a non-numeric character. So when the column type is changed to numeric these values cause an error. On the Close and Apply it will show you the number of errors caused and give a link to view the errors. Click the link.
This will create a new group in the Query Editor showing you the errors. If you select the cell containing the error, NOT clicking the text, just the cell, you will see the error details at the bottom of the screen.
If you do click the text by accident it will add an extra step which can be removed easily.
After working through the errors that happen in the weather data I modified the query to replace a variety of characters with spaces and then to filter out any rows that had a null year. My final query used by the function looks like this.
In these three posts we have taken a single query and made it into a custom function that uses a parameter and then called that function multiple times to create one table of data from files in multiple locations. The possibilities with this type of data combining are endless. If you have problems or suggestions please leave a comment.
Although this is the last post in this series, I will be using this data for future posts.
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.