Forum Discussion
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 don't have experience using REST APIs.
Is that's possible, either for a live data connection or for a scheduled refresh? If it's not possible in Power BI Desktop, would it be possible if I managed the report on the Power BI Service?
Further, if that's possible, could someone give me a quick and dirty of how to accomplish that and perhaps point to toward the materials I need to read to figure out how to do it? Part of my misunderstanding here is how the Blackbaud and Power BI REST APIs interact (if at all).
I have an E5 license that allows me access to Power BI Pro features.
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.
56 Replies
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.
Eric_Zhang and Anonymous Here's some sample code from the Advanced Query editor in Power BI desktop that would work for making an API call to Blackbaud's ON products, get and store the token from Blackbaud API as variable (NOTE: these are fakes URLs, usernames, passwords, and SLI id for list, but I promise it works in Power BI desktop just be sure your user has access to the Web Services API Manager role and that the list you're pulling from also gives access to the Web Services API Manager role and/or your username).
let
Source = Json.Document(Web.Contents("https://2018036.myschooltraining.com/api/authentication/login?username=jdoe&password=openthedoor&format=json")),
T = Source[Token],
GetList = Json.Document(Web.Contents("https://2018036.myschooltraining.com/api/list/99999/?t=" & T & "&format=json"))
in
GetListEric_Zhang can you tell me why this query can't refresh in power bi service - only desktop? I get error message saying this data source can't be refreshed in Power BI online.
I had rest API Call working in Power BI Desktop but its give me error when I push it on power BI Service that it can't be refreshed. Anybody refreshed succesfully REST API DataSource in Power BI Service?
Eric_Zhang i was looking at using a 3rd party api provider as a data source which is not one of your native PBI connectors (or online Service SaaS connectors as you cal them) so i followed this post with interest.
Obviosuly just a simple api call using http is the answer. Thanks.
I was just reading the API deveoloper doc of the provider and it states
"All access to the API occurs over an TLS-encrypted secure channel, so your API client must support TLS. To ensure data privacy, unencrypted HTTP is not supported."
Does the PBI client satisfy TLS ?
Thnaks for your timely feedback.
David
DavidMoss wrote:
Eric_Zhang i was looking at using a 3rd party api provider as a data source which is not one of your native PBI connectors (or online Service SaaS connectors as you cal them) so i followed this post with interest.
Obviosuly just a simple api call using http is the answer. Thanks.
I was just reading the API deveoloper doc of the provider and it states
"All access to the API occurs over an TLS-encrypted secure channel, so your API client must support TLS. To ensure data privacy, unencrypted HTTP is not supported."
Does the PBI client satisfy TLS ?
Thnaks for your timely feedback.
David
I have no idea about that. Just try and test.
I am new to BI. I am using REST API as data source, I am trying to get list of sensors readings to BI but the API only allow to call 1 week readings period if i try to call the readings for 1 month period the API will not return any result,
Question:
Can I create a dataset which will keep all historical readings so that I can call the API on daily basis and it saves all new readings permenantly?
let
Source = Sql.Database("localhost", "AdventureWorksDW2012"),
dbo_DimEmployee = Source{[Schema="dbo",Item="DimEmployee"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_DimEmployee, each [HireDate] >= #date(2000, 1, 1) and [HireDate] <= #date(2002, 1, 1))
in
#"Filtered Rows"You can then change hard-coded values to parameters. More info here:
http://blog.pragmaticworks.com/power-query-controlling-m-query-functions-with-user-driven-parameters
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' )
Many years ago, but this is an actual/functional solution:
https://pbi-guy.com/2023/05/21/create-a-post-request-with-power-bi/
