Forum Discussion
Using a REST API as a data source
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.
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?
I think you can schedule a refresh on the dataset once you publish a report and dataset is created.
GGetty will this work for BB K-12 school APIs? I'm trying to be able to run this through Power BI or export using Power Automate.
Excellent. This is just what I was hoping to hear. Thank you for your excellent answer.
Hello Casey,
Were you able to successfully link Power BI to one of Blackbauds applications? My organization uses Financial Edge and have been tinkering with Power BI. Currently we are just dumping data into excel sheets and loading them into Power BI.
Thanks,
Anonymous, no luck connecting through Power BI yet.
I was able to get access to the SKY API beta and have played around with test calls with their developer tools. It looks very promising and exicting.
Though things are working inside the test sandbox on the SKY developer website, I haven't yet gotten a connection to work within Power BI Desktop. I suspect I'm just not authenticating correctly, but I have tried all sorts of credential combinations with no luck yet. I'll keep poking at it, and hopefully enlist a developer friend of mine for advice, and will post back when/if I figure out the magic formula.
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.
Hi
Please can you help me find a solution. I tried what you suggested above, but it does not work for me. Note that I am not very experienced in doing this and this is my first time.
The provider gave me the below details:
When you are ready to begin, use the following URL for the API: https://resellers.accounting.sageone.co.za/api/2.0.0.
We use basic auth for authentication API calls and you would use the same login that you created in the step above.
Your API key for the Sandbox environment is: (they gave me a key).
I entered the URL above into the Web connector box in PowerBi. Then it gave me several options to choose from: Anonymous, Windows Credentials, Basic, Web API and Organisational Account.
I tried, anonymous, Basic and Web API options, none of them worked. Each time I did it, the loading preview window opened, and did not load, even after waiting 15 minutes.
I then also closed the window and tried to look for the “to table” option in your solution, and I could not find this either or it was greyed out.
Please can you help me find out how to get connected. Anonymous , did you get this to work for you? – did this solution work?
Please let me know.
Thanks.
Hi,
I am getting below error::
Hi,
I am getting below error::
