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.
GGettyNo worries, thanks for the feedback and the localhost tip. I've updated the git readme to include a note about setting the correct Redirect URL when creating your app.
For info, the app creation part of the tutorial is purely for the purposes of generating the necessary IDs. Blackbaud employ rate limiting and quotas on the API, meaning that if I were to share the application ID for the app I made, the limiting/throttling would be hit very quickly as the number of API calls would be the sum of all calls made by all organisations using the app. Creating your own app means that any limiting is only based on calls you have made locally in your organisation.
I'll add in as many list endpoints as I can in the coming days, before I start looking at the endpoints that take parameters (those that require a specific constituent ID to be passed in the header, for example). They're likely to add a fair bit of complexity to the connector!
GGettyI've updated the connector to include the gifts endpoint now, if you want to download the new mez file and give it a go.
I won't keep flooding the thread here with every minor update, but being able to report on gifts is fairly significant for us!
Quicky Anonymous williamdasilva Just wanted to contribute here albeit for Blackbaud's K12 ON Api instead of RE NXT Api. However, if you want to connect to Blackbaud's K12 API with only eight lines of M Code using the preferred POST calls, all you have to do is insert your YOURUSERNAMEHERE, YOURPASSWORDHERE, YOURSCHOOLNAMEHERE, and YOURSLID#HERE (for your Advanced List) in the sample code below. I'm going to put together a blog post and screencast on this, but wanted to share / giveback here:
letONLoginInfo = "{""username"": ""YOURUSERNAMEHERE"", ""password"": ""YOURPASSWORDHERE""}",Source = Json.Document(Web.Contents("https://YOURSCHOOLNAMEHERE.myschoolapp.com/api/authentication/login",[Content = Text.ToBinary(ONLoginInfo), Headers=[#"Content-Type"="application/json"], IsRetry=true])),T = Source[Token],GetList = Json.Document(Web.Contents("https://YOURSCHOOLNAMEHERE.myschoolapp.com/api/list/YOURSLID#HERE/?t=", [Query=[t=T, format="json"]]))inGetListAlso on Quicky SKY API connector, now that Microsoft has announced general availability of custom connectors, could this be opened up to anyone? Everyone seems excited about the general availability of custom connectors but if you have to login and API calls are metered I'm not sure what that means for the SKY API. https://powerbi.microsoft.com/en-us/blog/announcing-general-availability-of-custom-and-certified-connectors-for-power-bi/GGettyNo worries - the connector doesn't really do antything with regards to building a data model for you as yet, beyond pulling back all the data and setting the correct data types. The gift amounts are there, but because they are records themselves, you would need to tell the data model how you want them to be handled. In the case of the gift amounts, you would just need to expand the record. To do this, click Edit Queries, and you'll see all the data pulled back. You can then click the expand button in the corner of a column's header to surface the data.
You may want to expand some records into separate tables. You can follow this blog post to do that: https://powerbi.microsoft.com/en-us/blog/how-to-expand-a-list-of-records-in-a-query/
Most of the endpoints have record-type fields, so I'd recommend editing the queries for each endpoint you connect to in order to see if any additional data can be exposed. At some point I'll handle this in the connector directly, but the initial focus is to bring back all possible data.
I should get the chance to update the connector today with a solution to the issue of rate limiting on larger datasets.
Anonymous Quicky williamdasilva Just wanted to say that if you've got Grant Quick's custom connector working, you can get a jump start on data modelling and reporting with RE NXT received revenue with a free Power BI template (PBIT) here: https://resolvedllc.com/grab-the-butter/
Quicky this is great. I got the new mez working and can see the gift endpoints but, oddly, gift amount is no where to be found. Did I mess something up? I can see funds, gift dates, dates added, post date, but no gift amount anywhere.
Screenshots here:
Here are the values from the gift table that I'm seeing:
headers values
id 4859
lookup_id 4463
date 4/1/2014 0:00
type Donation
constituent_id 9759
is_anonymous FALSE
post_status NotPosted
post_date 5/27/2011 0:00
gift_status Active
batch_number
date_added 5/27/2011 11:27
date_modified 11/22/2017 13:30Quicky Ah-ha! Here I was thinking the custom connector handled all the query editing, else. I've got it now. All I had to do was click the [-] and rename to amount. I'm good now. Of course, now I'm going to poke around on the other queries to see what else is "hidden". Thanks again for all this!
