How to create a Power BI Weather Report (Part 2) : Weather Data Part – Create Power Query Function

Posted in Power BI, by LauraGB View all Posts

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.

Create Power Query Function

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.

  1. From the Home ribbon click Manage Parameters.
  2. 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.Add Parameter dialog
  3. Click OK to save the parameter. It will now appear in the list of queries.parameter
  4. 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.Changes to the function
    The code is:
  5. = Csv.Document(Web.Contents("https://www.metoffice.gov.uk/pub/data/weather/uk/climate/stationdata/"&StationName&"data.txt"),<br />
    [Delimiter="=", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None])
  6. Check the query still works by clicking on the last step of the query and checking you still get data.
  7. 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.Create Function dialog
  8. The query and parameter will get moved into group with the new function in the queries panel.list of queries
  9. 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.invoke function
  10. A new query will appear populated with data based on the station you entered.invoked function
  11. 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.disable load

Conclusion

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.

Microsoft Teams Governance EBook

Note

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.

Get the PowerApps Ebook Bundle

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.

Check out some of my MicroJobs...

Download the Building a Hangman Game in PowerApps Companion Ebook Download the Building a Hangman Game in PowerApps Companion Ebook
Download the Building a Hangman Game in PowerApps Companion Ebook
0.0 (0)
10.00 USD
EBook Download: Beginners Guide to PowerApps EBook Download: Beginners Guide to PowerApps
EBook Download: Beginners Guide to PowerApps
4.8 (41)
10.00 USD
I will coach you for 2 hours in creating Approval Flows I will coach you for 2 hours in creating Approval Flows
I will coach you for 2 hours in creating Approval Flows
0.0 (0)
250.00 USD

Want to comment?

>