powerbi

Use of API in Power BI retrieves no data just metadata


It's the first time that I work with APIs in Power BI. I am trying to retrieve data from this API Data Europa EU but so far I just retrieve the metadata and not the data.

This API is available in the link The European Commission's Oil Bulletin and I'm interested in get data related to Price developments 2005 onwards.

In Power BI I clicked on Get Data, then selected Web and inserted the URL for the API. Afterwards, I expanded all records and instead of the expected data I just got the Url for the page. I don't have the API documentation and could't find it.

I'm doing something wrong or it's supposed to just get the metadata and with the link to download the file ? It would be great if I could find a way to get the data via API instead of downloading the file.

Thank you for your help.


Solution

  • Situation

    When you use the "From Web" connector in Power BI it's effectively like using a web browser to look at the site provided, since the https://data.europa.eu/data/datasets/eu-oil-bulletin?locale=en URL does not have any tables Power BI can read, there was nothing for Power BI to find.

    Answer

    to fetch the data, what you need to do is find the link that will give you the data in a table format, in this instance we have to:

    1. Follow the "Access URL" link
    2. Find the URL that provides access to the data. In this case most of the "Download" buttons can be used to download an excel copy of the data.
    3. Select the "Copy link address" of the dataset you want to import. In this instance the address will be something similar to this: https://energy.ec.europa.eu/document/download/ccdc6e96-6792-40cb-b0b4-b6609f1e30d0_en?filename=Oil_Bulletin_Duties_and_taxes.xlsx
    4. Paste this value into the dialogue box for the "From Web" connector of Power BI to have the data automatically be pulled from the web every time someone clicks refresh.