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

Posted in Power BI, by LauraGB View all Posts

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.

Weather Data Series

Loading Station List

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.

Excel table of Stations
The file for this can be found here

  1. Edit the queries from the previous posts.
  2. Click on New Source on the Home ribbon and select Excel. Select the Excel file containing the stations data and click Open.
    When the Navigator dialog appears, tick StationList table and click OK.Navigator dialog
  3. A new query is added that loads the station list. In order to build a relationship easily we are going to add an index column to the station list.
    On the Add Column ribbon tab, select Index Column and then From 1.Add index column
    This adds an Index Column.
  4. For tidiness and good practice click and drag the Index column to the left to make it the first column in the table. This add a reordered step to the query. The StationList is now ready.

Station List complete

Invoke Power Query Function

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.

Free PowerApps Virtual Summit Pass: Save your place at our online PowerApps Virtual Summit (March 27th) and learn PowerApps. (Free places are limited so be quick!)

  1. Right click on StationList and select Duplicate. Rename the new query to StationData.
  2. We need to add a column that is the station name with no spaces. We could write a replace function but it will be quicker to use Column from Examples.Select the Station column, and then select Column from Examples – From Selection.
    Enter in enough values for it to understand to remove the space, I also lower cased it. With my data it only took 2 values.
    Double click on the column name to rename it StationName.Add column from example
  3. Remove the columns for Station, Region, Latitude and Longitude, just leaving Index and StationName.
  4. From the Add Columns ribbon tab, select Invoke Custom Function.In the Invoke Custom Function dialog, select the function in the drop down.
    Make sure the StationName parameter is refering to the StationName column. As they are called the same think Power Query usually guesses it.
    Then click OK.
  5. This creates a column named after the function that contains Table.Click the button in the top right of the column to show the expand options.
    Un-tick the Use original column name as prefix and click OK.Expand table column
    The column should then expand into all the data from the station data files. Last job is to remove the StationName column as it is no longer required.

    final data

  6. Click Close and Apply to load your data.

Handling Errors

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.

upload with errors

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.

Final Query

Conclusion

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.

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...

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
I will create a PowerPoint template branded for your company or event. I will create a PowerPoint template branded for your company or event.
I will create a PowerPoint template branded for your company or...
0.0 (0)
150.00 USD

Want to comment?

>