Power Query#

If you are an Excel or Power BI user, you can easily fetch data from the Energy API directly through Power Query. For Excel we provide four Power Query scripts that will connect Power Query to each of the Energy API endpoints. For Power BI we have a custom connector, available in the “Get-Data” menu after installation. With Power Query you can quickly create responsive and intricate models directly in Excel or Power BI with our Energy API.

New to Power Query? Read more about Power Query here.

Installation#

Below you will find a guide on how to set up the Energy API connection through Power Query for Excel and Power BI.

The first step is to download the Power Query scripts folder. Download the folder named “Power Query” from our Github repository. We recommend you put the scripts somewhere meaningful.

On GitHub, click on the green “Code” button and then “Download ZIP” to download the Github repository as a folder to your machine.

_images/github.png

Excel#

  1. In Excel go to the “Data” pane and click on “Existing Connections”.

_images/excel_guide_01.png

  1. Click on “Browse for More…” and navigate to the “Power Query” and then open the “Excel” folder.

_images/excel_guide_02.png

  1. Select one of the folder, each folder is a endpoint group.

_images/excel_guide_03.png

  1. Select one of the files starting with “Query -” in their name. click “Open”.

_images/excel_guide_07.png

  1. In the Import Data window pop-up, uncheck “Table” and select “Only Create Connection”. Press “OK”.

_images/excel_guide_04.png

  1. Repeat the same process with the other folders

  2. Go to the “Data” pane and select “Queries & Connections”.

_images/excel_guide_05.png

  1. You should now see the Power Query functions in the right pane. You are now set to download data.

_images/excel_guide_06.png

Data privacy settings#

The first time you try to fetch data from the Power Query functions you may see the following message:

_images/first_time_setup_01.png

You need to set the data privacy permissions for the workbook.

  1. Press Continue.

  2. In the following window, set your desired privacy settings. We recommend Public or Organizational. Private will likely not work due to firewall. You can also choose to ignore data privacy levels for the workbook. After choosing your level, press save. Your query should now work. If not, try to Refresh the query.

_images/first_time_setup_02.png

You can change the data privacy settings by:

  1. Going to “Data source settings”, choose “Global Permissions”.

  2. Select “https://energy-api.stormgeo.com/” and click on either “Edit Permissions”

_images/first_time_setup_03.png

Installations in future workbooks#

To add a Energy API Power Query connection in future Excel workbooks you don’t need to repeat the whole process detailed above.

  1. In a new workbook go to the “Data” tab and click on “Existing Connections”.

  2. Here you will see a list of the three Power Query scripts. Simply open each one and choose the option “Only Create Connection”.

It is also possible to copy and paste the queries from the “Queries & Connections” pane into another workbook. Simply right-click the function and choose “copy”, go to the other workbook’s Queries pane, right-click and choose “paste”.

Update Power Query scripts#

If you want to update the Power Query scripts to the latest version, you can simply download the latest version from the github, and replace the old files in the “Power Query” folder. You can also download the latest version of the scripts and add them as new connections in Excel. If you get a new version of the scripts, you can simply add them as new connections in Excel. You may have to clear the data load cache in Excel so the function scripts uses the new scripts.


  1. Go to “Get Data” and click on “Query Options”.

_images/excel_clear_cache_01.png

  1. In the “Query Options” window, click on “Data Load” and scroll down til you see “Clear Cache” button.

_images/excel_clear_cache_02.png

  1. Click on “Clear Cache” and then “OK”.

Optional Excel configuration#

If you don’t want to type your StormGeo username or password every time you use the Power Queries scripts, you can configure named ranges in an Excel sheet with your username and password.

  1. Create a new sheet in the Excel workbook and name it preferably “StormGeo Config.” or something similar.

  2. In the newly created sheet type your StormGeo username and password into any two cells.

  3. Click on the cell with your username and go to the “Formulas” tab and click on “Define Name”.

_images/excel_optional_01.png

  1. In the New Name dialogue box, in the “Name” field, replace your username with the word “STORMGEO_USERNAME”. Note that this should be written exactly like stated i.e. all capital letters and underscore. Click “OK”.

_images/excel_optional_02.png

  1. Now repeat the same process with the cell containing your StormGeo password. In the “Name” field write “STORMGEO_PASSWORD”. Click “OK”.

_images/excel_optional_03.png

  1. If you open the “Name Manager” in the toolbar menu you should now have two named ranges called “STORMGEO_USERNAME” and “STORMGEO_PASSWORD”. If you see these, you are done and can skip typing your StormGeo username and password in the Power Query function.

_images/excel_optional_04.png

User Guide#

Excel#

This guide explains how to use the provided Energy API Power Query functions. It will help you find the necessary metaData to determine the correct parameters. If you encounter a data privacy warning when using any query functions, configure the settings as specified here.

Searching for MetaData#

Maintaining a list of relevant metaDatas as an Excel table in your workbook is helpful. This allows you to quickly find areas, issueTimes, etc., to load the desired data queries. For each endpoint, the naming convention for metaData queries follows the main query name. For example:

  • The query GetShortTermPowerPriceForecast has meta data under GetShortTermPowerPriceForecastAreas to find areas and GetShortTermPowerPriceForecastIssueTimes to find issueTimes.

    _images/searchForMetaData_01.png

There is an exception for the PowerBalanceForecast query:

  • Both GetShortTermPowerBalanceForecast and GetDayAheadPowerBalanceForecast use the same meta data, which can be found under GetPowerBalanceAreas and GetPowerBalanceIssueTimes.

    _images/searchForMetaData_02.png

If you are new to Power Query, it is strongly advised that you read the Power Query documentation before proceeding, as this guide assumes you are familiar with Power Query.

  1. Double-click on the GetShortTermPowerPriceForecastAreas function in the Queries & Connection pane in Excel. Since this function does not have any parameters, it will be invoked directly. The Power Query Editor will open, allowing you to manipulate the data into your preferred format. Rename the query by right-clicking the new query in the left pane to “List of short-term analyzed areas with codes”. Click on “Close and Load” to load the data to a new Excel sheet.

    _images/searchForMetaData_03.png

  2. You should now see a table with information on areas related to the Price model. You can filter the table to find specific areas. Try clicking the drop-down arrow on the “Description” column and write “Norway” to filter areas only related to Norway.

    _images/searchForMetaData_04.png

To find information on when the last model was run (i.e., StormGeo’s last prognosis), you can use the GetShortTermPowerPriceForecastIssueTimes function. This function will return a list of issueTimes for the short-term power price forecast based on area. To use this, double-click on the function in the Queries & Connection pane in Excel. You will be asked to type in an area. Use one of the codes from the last table you fetched. Here we use “NO1”. Rename the invoked function to “List of short-term model runs for NO1”. Click on “Close and Load” to load the data to a new Excel sheet.

Fetching data#

We will now use the tables we just retrieved to fetch data from the Energy API. For this example, we will use the GetShortTermPowerPriceForecast function to get the price forecast for NO1, issued on “2024-09-17” for a week ahead.

  1. Create a new Excel sheet and name it “NAPM”.

  2. In the newly created sheet, double-click on the Power Query function called fetchSeries.

  3. For the area field, write “NO1”. For the issueTime field, write “9/17/2024”. For the startDate field, write “9/18/2024”. For the endDate field, write “9/25/2024”. If you have defined your username and password as named ranges, you don’t need to type them. Click “OK” to open the Power Query Editor.

    _images/fetchData_01.png

  4. Rename the query table to “NO1 price forecast”. Click on “Close and Load To…”, select “Existing Workbook” and click on a cell in the sheet where you want the table. Click “OK”.

You should now have a table with the price forecast for NO1. You can repeat this process with additional areas.

  1. Rename the query tabel to “NO1 price forecast”. Click on “Close and Load To..””, select “Existing Workbook” and click on a cell in the sheet where you want the tabel. Click “OK”.

You should now have a tabel with the price forecast for NO1. You can repeat this process with additional areas.

_images/fetchData_02.png

Common errors#

If you are experiencing errors it is a good idea to read the error message returned by Power Query. Here are a few common problems and how to solve them.

Refreshing a query#

If you are having trouble, try refreshing the query. If this does not resolve your problem, see below.

_images/refresh_preview_01.png

Missing username and/or password#

You have not provided a username or password. This means that the stormgeo_username and stormgeo_password fields in the function are empty or that you have not defined the credentials as Excel named ranges.

_images/no_username_password_01.png

Incorrect username and/or password#

Power Query tells you that your username or password is incorrect or you do not have access to the Energy API. Check you username and password.

_images/incorrect_username_password_01.png

Invalid parameter#

This error message means that you have provided a parameter keyword that is not correct or is invalid for the time series. In this example, the series id is incorrect. It is also possible that you have set a resolution keyword that the series does not support.

_images/invalid_parameter_01.png

Empty cache#

Some times you will get the messes “We couldn’t authenticate with the credentials provided. Please try again.” This is due to the cache in Excel.

_images/invalid_credentials.png

If you have followed ours guid and have the correct credentials, you can try to clear the cache in Excel. Go to “Get Data” and click on “Query Options”. In the “Query Options” window, click on “Data Load” and scroll down til you see “Clear Cache” button. Click on “Clear Cache” and then “OK”.

On this page
Show Source