Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

Using a REST API as a data source

I'd like to be able to connect to a Blackbaud REST API to pull data from that system and use it in a Power BI Desktop report: http://on-api.developer.blackbaud.com/getting-started/   That said, I d...
Need Help
Tutorial Requests
  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    Anonymous

     

    I don't know the Blackbaud REST API in your case and it is not in the list of suppported "Online Service".

     

    But according to the content in the link that the API reponses in JSON, you can get the data by using "Get Data"-->"Other"-->"Web". And then extend JSON to a table in query editor.

     

    eg. http://jsonplaceholder.typicode.com/posts/1/comments

    You can replace it with a link like "http://[school].myschoolapp.com/api/academics/department/?t=[token returned from previous step]" in your link.

     

    In the query editor,

     

    Done

     

    Regarding refreshing, you can publish it to Power BI Service and schedule refresh.

ZappySys's avatar
ZappySys
Icon for Helper I rankHelper I
8 years ago

ZappySys has introduced powerful API Drivers to query virtually any JSON / XML API services in Power BI (e.g. Zendesk, Twitter, Google API, Amazon AWS)

Check this blog for more info:  https://zappysys.com/blog/howto-import-json-rest-api-power-bi/

 

Basically, Calling REST API in Power BI can be a challenging process because you have to deal with many complex issues. If its simple JSON / XML then fine but in the real world, many other issues arise such as pagination, flatten the hierarchy, error handling, security (OAuth, Basic, Certificate). 

 

Hope this helps!!

 

Step-1 : Create your ODBC DSN to call REST API or read JSON / XML dataStep-2 : Build / Test your REST API QueryStep-3: Import REST API data in Power BI using ODBC connection (Under Get Data >> More >> Other)

 

 

You can write queries like below (JSON Driver Example)

 

SELECT * FROM $
WITH(
src='https://service4.ultipro.com/services/EmployeePerson'
,Header='Authorization: Basic c29tZXVzZXI6c29tZXBhc3dvcmQ= || US-Customer-Api-Key: ABCDE'
,RequestMethod='GET'
)