===========
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.
.. image:: _static/images/github.png
:align: center
:scale: 50%
|
Excel
-----
1. In Excel go to the "Data" pane and click on "Existing Connections".
.. image:: _static/images/excel/excel_guide_01.png
:scale: 50%
:align: center
|
2. Click on "Browse for More..." and navigate to the "Power Query" and then open the "Excel" folder.
.. image:: _static/images/excel/excel_guide_02.png
:scale: 50%
:align: center
|
3. Select one of the folder, each folder is a endpoint group.
.. image:: _static/images/excel/excel_guide_03.png
:align: center
:scale: 50%
|
4. Select one of the files starting with "Query -" in their name. click "Open".
.. image:: _static/images/excel/excel_guide_07.png
:align: center
:scale: 50%
|
5. In the Import Data window pop-up, uncheck "Table" and select "Only Create Connection". Press "OK".
.. image:: _static/images/excel/excel_guide_04.png
:align: center
:scale: 50%
|
6. Repeat the same process with the other folders
7. Go to the "Data" pane and select "Queries & Connections".
.. image:: _static/images/excel/excel_guide_05.png
:scale: 50%
:align: center
|
7. You should now see the Power Query functions in the right pane. You are now set to download data.
.. image:: _static/images/excel/excel_guide_06.png
:align: center
:scale: 50%
.. _data privacy settings:
Data privacy settings
**********************
The first time you try to fetch data from the Power Query functions you may see the following message:
.. image:: _static/images/excel/ExcelHowTo/first_time_setup_01.png
:align: center
:scale: 50%
|
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.
.. image:: _static/images/excel/ExcelHowTo/first_time_setup_02.png
:align: center
:scale: 50%
|
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"
.. image:: _static/images/excel/ExcelHowTo/first_time_setup_03.png
:align: center
:scale: 50%
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".
.. _optional excel config:
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".
.. image:: _static/images/excel/excel_clear_cache_01.png
:align: center
:scale: 50%
|
2. In the "Query Options" window, click on "Data Load" and scroll down til you see "Clear Cache" button.
.. image:: _static/images/excel/excel_clear_cache_02.png
:align: center
:scale: 50%
|
3. 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".
.. image:: _static/images/excel/excel_optional_01.png
:align: center
:scale: 50%
|
4. 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".
.. image:: _static/images/excel/excel_optional_02.png
:align: center
:scale: 50%
|
5. Now repeat the same process with the cell containing your StormGeo password. In the "Name" field write "**STORMGEO_PASSWORD**". Click "OK".
.. image:: _static/images/excel/excel_optional_03.png
:align: center
:scale: 50%
|
6. 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.
.. image:: _static/images/excel/excel_optional_04.png
:align: center
:scale: 50%
|
----------
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 :ref:`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.
.. image:: _static/images/excel/ExcelHowTo/searchForMetaData_01.png
:align: center
:scale: 50%
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``.
.. image:: _static/images/excel/ExcelHowTo/searchForMetaData_02.png
:align: center
:scale: 50%
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.
.. image:: _static/images/excel/ExcelHowTo/searchForMetaData_03.png
:align: center
:scale: 50%
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.
.. image:: _static/images/excel/ExcelHowTo/searchForMetaData_04.png
:align: center
:scale: 50%
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.
.. image:: _static/images/excel/ExcelHowTo/searchForMetaData_05.png
:align: center
:scale: 50%
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.
.. image:: _static/images/excel/ExcelHowTo/fetchData_01.png
:align: center
:scale: 50%
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.
.. image:: _static/images/excel/ExcelHowTo/fetchData_02.png
:align: center
:scale: 50%
5. 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.
.. image:: _static/images/excel/ExcelHowTo/fetchData_02.png
:align: center
:scale: 50%
-------------
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.
.. image:: _static/images/excel/Errors/refresh_preview_01.png
:align: center
:scale: 50%
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 :ref:`Excel named ranges `.
.. image:: _static/images/excel/Errors/no_username_password_01.png
:align: center
:scale: 50%
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.
.. image:: _static/images/excel/Errors/incorrect_username_password_01.png
:align: center
:scale: 50%
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.
.. image:: _static/images/excel/Errors/invalid_parameter_01.png
:align: center
:scale: 50%
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.
.. image:: _static/images/excel/Errors/invalid_credentials.png
:align: center
:scale: 50%
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".