Using Microsoft Power Query, we can retrieve API data directly into an Excel document.
The following example shows retrieving a PowerGen Solar Magma Forecast:
Login to the API Documentation website. Create your API request and confirm its working as expected. In the following example the API request is for PowerGen -> Latest Endpoint
Parameters for this query are:
Scroll down the Responses and copy the Request URL to the clipboard:
And paste the URL into Notepad or similar text editor.
We need to add some extra information before it can be used.
Prefix the URL with the following text:
= Json.Document(Web.Contents("
Your document should look like this now:
Now create another Notepad and append the following into a blank document:
", [Headers=[Authorization="jwt your-api-key"]]))
Next, replace the text ‘jwt your-api-key' with your actual API Key i.e.
Finally, copy all of that and append it to the end of the first document you created. So, you should end up with something similar to this:
= Json.Document(Web.Contents("https://api.metdesk.com/get/metdesk/powergen/v2/latest?model=magma&element=wind&location=DE&location_type=country",[Headers=[Authorization="jwteyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiaWF0IjoxNTE2MjM5MDIyfQ.SflKxwRJSMeKKF2QT4fwpMeJf36POk6yJV_adQssw5c"]]))
Paste this whole line from the last step into the Formula field shown below and press Enter:
The following fields will appear below the Formula field:
Right-click “Record” and select “Into Table”, the formula will now look like this:
Left click ‘List’:
The “To Table” dialog box appears, click OK to continue:
The new Formula is now Table.FromList:
Left click on the Expand Column button:
A dialog box will appear. Optionally, disable any fields you don’t need and click the OK button to continue:
The new Formula is now Table.ExpandRecordColumn:
Finally, click “Close & Load” from toolbar:
Click OK to accept the defaults and the data should appear in your Workbook: