cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
junaidfrq
Frequent Visitor

Import Data from OECD website

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 importedCountry data to be imported

 

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

 

JSON API QueryJSON API Query 

I have used below function posted by user "

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

 

Query BI.JPG

 

But the following error comes up...

 

Error.JPG

 

Would anyone know what I am doing wrong?

 

Any help will be highly appreciated.

 

Kind Regards,

Junaid Farooq

1 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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

7 REPLIES 7
awuawu
Regular Visitor

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 

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

v-cherch-msft
Microsoft
Microsoft

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Hi Junaid,

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

junaidfrq
Frequent Visitor

Hi ImkeF,

 

This worked perfectly.

 

Thank you so much for your help on this one.

 

Kind Regards,

Junaid Farooq

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors