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.

Quicky's avatar
Quicky
Advocate I
8 years ago

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.

GGetty's avatar
GGetty
Advocate II
7 years ago

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: 

 

let
    ONLoginInfo = "{""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"]]))
in
    GetList
 
Also 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/
  • GGetty's avatar
    GGetty
    Advocate II
    7 years ago

    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/