Import Data from OECD website

[junaidfrq]

Hi,

I am trying to import country statistics data from OECD website into my power bi model. I also want to refresh the data with a query.

I need to connect each country's data to my Power BI model from below source.

https://stats.oecd.org/

Country data to be imported

I am using the following JSON API as a query to import the data and link to OECD.

JSON API Query 

I have used below function posted by user "ImkeF" to import the data

https://community.powerbi.com/t5/Desktop/Get-data-using-API-link-to-OED-data/td-p/242333

But the following error comes up...

Would anyone know what I am doing wrong?

Any help will be highly appreciated.

Kind Regards,

Junaid Farooq

[SnowerCX]

Hello all, I am trying to import data from newest OECD website whre all data is being migrated, OECD Data Explorer • Key short-term economic indicators into my power bi model. I also want to refresh the data with a query. 

I have used below function posted by user "ImkeF" to import the data

https://community.powerbi.com/t5/Desktop/Get-data-using-API-link-to-OED-data/td-p/242333

as API code has changes, this does not longer read the website data. Anyone has been able to update this / knows how to do it?  this is the error I am getting when pasting the new Flat data query structure.

Many thanks for your help,

Regards

[awuawu]

Hi @ImkeF , First thanks a lot for providing the solution for importing the OCED, it worked like a charm!! But I have a follow up question, since it is related to OECD data as well. If it is covinient for you to take a look at this query below. In the column "Sector" and "Donor", there is both disaggragated data and aggregated data. For example in the Sector column, the item "I. Social Infrastructure....." includes "I.1.", "I.2."...etc. But all these items are in the same column, which make it very difficul to analyse. 

Hope i'm making sense with the question, and thanks in advance for your help! 

OECD query 

[ImkeF]

Sorry, I'm super busy right now and cannot follow this up.

Please open a new post to raise the chance for awareness for other helpers in this forum.

[v-cherch-msft]

Hi @junaidfrq

You may try to use On-premises data gateway to refresh the data. Here is the document for you.

https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem

Regards,

Cherie

[junaidfrq]

Hi Cherie,

Thank you for a great workaround.

Unfortunately, the client requires the data to be refreshed directly from the website (https://stats.oecd.org/) and does not prefer using a bridge.

This is definitely an addition to my knowledge and will keep in mind for future use.

Thanks again,

Junaid

[ImkeF]

Hi Junaid,

unfortunately the API limits the length of the URL to max. 1000 characters: https://data.oecd.org/api/sdmx-json-documentation/ 

[ImkeF · Accepted solution]

Please try this URL instead: https://stats.oecd.org/SDMX-JSON/data/CSPCUBE/all?startTime=2011&endTime=2015&dimensionAtObservation...

 ... it looks that omitting all the filter criteria works.

[junaidfrq]

Hi ImkeF,

This worked perfectly.

Thank you so much for your help on this one.

Kind Regards,

Junaid Farooq