Power BI - unable to fetch data using API

[LordWiader]

Greetings Everyone,

As it is my first post I would like to thank you for opportunity to join this community.

My name is Piotr and recently I have been asked to download data from transparency platform using API and Power Query.

I have never seen or used Power Query or API therefore I kindly ask for your help with modyfying below code I wrote.



Code:

let
Source =
Xml.Tables(
Web.Contents(
"https://web-api.tp.entsoe.eu/api?",
[
RelativePath="/api",
Query=
[
securityToken=#"0320cd33-0a34-49d8-9edc-3a31fc102d2a",
DocumentType="A80",
BusinessType="A16",
BiddingZone_Domain="10YCB-POLAND---Z",
periodStart="202301010100",
periodEnd="202301060100",
Headers=[#"Content-Type"="application/xml"]
]
)
)
in
Source

Website I need to accquire data from (including filters) :

Data view



API Guide: https://transparency.entsoe.eu/content/static_content/Static content/web api/Guide.html

Data Extraction Process Implementation Guide : https://transparency.entsoe.eu/content/static_content/download?path=/Static content/web api/RestfulAPI_IG.pdf

Much appreciate your help in this matter.

 

[smozgur]

Welcome to the MrExcel Message Board.

Note: I recommend not sharing valid API tokens or any kind of credentials on public forums.

The code above has some mistakes as far as I can see.

1. The base URL shouldn't contain the "/api?" fragment at the end. The relative path is already provided as a parameter (/api).
2. Security token identifier value also looks like an identifier. Need to remove the # at the beginning.
3. The Headers record field is not a Query field. It should be defined as a separate record field.
4. In the documentation, the BusinessType query parameter is actually processType. Not sure about this, so you have to check that.

If the web service allows authorization with a token via GET request, then the correct syntax should be like the following code. You need to test. the following code by using a valid API token. I suggest testing the API connectivity first by simply using a browser with the base URL, endpoint (/api) and all the queries listed in your sample code (Query record below). Once you confirm it is working without problems in the browser, then it should work in Power Query as well.

let
  Source = Xml.Tables(
            Web.Contents("https://web-api.tp.entsoe.eu",
                [
                  RelativePath = "/api",
                  Query = [
                    securityToken = "api---security---token---here",
                    documentType = "A80",
                    processType = "A16",
                    BiddingZone_Domain = "10YCB-POLAND---Z",
                    periodStart = "202301010100",
                    periodEnd = "202301060100"
                  ],
                  Headers = [
                    #"Content-Type"="application/xml"
                  ]    
                ]
              )
          )
in
  Source

 

[LordWiader]

Note: I recommend not sharing valid API tokens or any kind of credentials on public forums.

Thanks. I knew it, however data I need is widely available and I wanted to share the key until I find the solution . Now I have changed it.

I suggest testing the API connectivity first by simply using a browser with the base URL, endpoint (/api) and all the queries listed in your sample code (Query record below). Once you confirm it is working without problems in the browser, then it should work in Power Query as well.

:O Sorry . I do not know/understand how to do it.

When I run your version of the code code I get the following message (translated as I use polish version of the excel) : The web API interface key can be specified only when the name of the web API interface key is entered.
Then I get presented with a window to enter API Key and choose some level where I want to apply those settings .

 

[smozgur]

If you look closely at the API guide you posted above, you will see that it says that you can query data via a web browser (sections 1.7 and 2.1). Every fragment starting with & in the URL is a query parameter pair (except the first one begins with "?"), such as documentType=A65, processType=A16. The same parameters are in your code. Basically, your URL should be something like below (you need to change the MYTOKEN with a valid security token).

https://web-api.tp.entsoe.eu/api?securityToken=MYTOKEN&documentType=A80&processType=A16&outBiddingZone_Domain=10YCB-POLAND---Z&periodStart=202301010100&periodEnd=202301060100

When I tested this URL with your API token above, it say "Invalid security token". So, the code I fixed and posted should be also receiving the same HTTP response code (401), that's why it is asking for credentials. You really need a valid security token to test it.
(I also tested it by using POST method instead of GET and providing header key SECURITY_TOKEN by using the same token, not valid)

 

[LordWiader]

https://web-api.tp.entsoe.eu/api?securityToken=MYTOKEN&documentType=A80&processType=A16&outBiddingZone_Domain=10YCB-POLAND---Z&periodStart=202301010100&periodEnd=202301060100

When I tested this URL with your API token above, it say "Invalid security token".

I have tested it with valid security token and was able to get further one step. Unfortunately it looks like the syntax is invalid.
Despite bidding zone being there , and being 16 characters long i get the following message :

<code>999</code>
<text>Mandatory parameter BiddingZone_Domain is missing or is empty.</text>

Token this time is : d9677a27-bf16-43cf-9a26-fe6a87192e75 ( again , nothing secret )

I tried going directly to Data view and manually filter results to show outages in Poland , to study the link, however it does not contain BiddingZone_Domain.
I tried copying link generated by the website after filter being applied and then swapping Data view with https://web-api.tp.entsoe.eu/api?securityToken=d9677a27-bf16-43cf-9a26-fe6a87192e75 , however then I am getting another error .

Would you be able to take a look again at the above?

BTW - I really appreciate your help and effort. It is really nice to receive support without any snotty comments about the lack of knowledge

 

[smozgur]

Two mistakes to fix.

1. outBiddingZone_Domain in their documentation should be BiddingZone_Domain (as in your initial post actually).
2. 10YCB-POLAND---Z is not a valid value.

Please try the following URL with your API token. I used 10YCZ-CEPS-----N as the BiddingZone_Domain value.
https://web-api.tp.entsoe.eu/api?securityToken=CHANGE_WITH_API_TOKEN&documentType=A80&processType=A16&BiddingZone_Domain=10YCZ-CEPS-----N&periodStart=202301010100&periodEnd=202301060100

It works without a problem. So, you need to change the parameter name by removing the "out" at the beginning and find the correct value for that.
However, now there is another major issue: the API doesn't return an XML file as it claims but a zip file contains multiple XML files.

Is it possible to extract a zip file in Power Query? Yes, but it requires some work as you can see here. Perhaps you might want to contact the data provider and see if they also provide pure XML files instead of zipped folders.