In this post I take a single query that transforms one text file containing weather station data and convert it into a power query function, that can be invoked and with a parameter can transform any weather data file. This is a step by step blog aimed at the non-coder and only requires a minor formula change.
This post is part of a series working with data files from UK Met Office stations. The other parts in this series can be found using the links below.
We start in Query Editor with the query built in the previous post. The first step of the query, Source, opens the text file from a specified location. For the weather stations the file is always at the same location and called the station name followed by data.txt. So in this example the station name will be the parameter to specify the file being loaded.
From the Home ribbon click Manage Parameters.
Click New in the dialog box.
Change the name to StationName and add a description for the parameter.
Change the Type to text and the Current Value to cambridge.
Click OK to save the parameter. It will now appear in the list of queries.
After the parameter has been created, we can now use that parameter in the query. Open the query and look at the first step, Source. The Web.Contents function has a single parameter of the path to the file. The last part is the file name which needs to now become the parameter StationName plus “data.txt”.Edit the formula using & to add strings together.
The code is:
Check the query still works by clicking on the last step of the query and checking you still get data.
Now the query refers to the parameter we can use it to create a power query function. Right click on the query in the queries list and from the menu select Create Function. Enter in a function name and click OK.
The query and parameter will get moved into group with the new function in the queries panel.
The next step is to test the function works. Click on the function in the group. Enter in a station name, e.g. lerwick and click Invoke.
A new query will appear populated with data based on the station you entered.
If you press close and apply you will get 2 tables of data into Power Bi. You need to stop the original query loading into the report in Power Bi.Right click on the original query in the function group. Un-tick Enable Load and when the warning appears that this removes the table from the report, click Continue. You will now only get the invoked queries in the report.
This post has taken a query and shown how to make it into a function with a passed parameter. If the query is edited in anyway the function will immediately reflect the change. This has made the original query reusable for different files of the same structure.
The next post will examine how to use the function for multiple stations and combine the data into a single table.
Some of the weather station files cause some errors. These will get fixed in the next post when we load all the stations’ text files.
Are you looking to hire a Power BI professional like LauraGB?
Post a job below (even if it's just a 15-minute Skype call), enter a few details and we will get to work on finding you some online help. Alternatively, you can also browse some existing MicroJobs.
If you're just beginning (or completely new) to PowerApps then my co-authored 51-page book is a must-read. The book is split into 3 sections: Introduction to PowerApps Tutorials Tips In the book, we...