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.
Cheers GGetty. The Blackbaud Sky API appears to have a different authentication, and instead of returning a Json, the access token is returned in the URL itself when using Implicit Flow: https://apidocs.sky.blackbaud.com/docs/authorization/implicit-flow/ although it does return a Json containing the token when using Authorisation Code Flow https://apidocs.sky.blackbaud.com/docs/authorization/auth-code-flow/ but only after you've processed the access code in the initial call to the authorisation service, which returns an access code in the return URL again. Both methods require the user to authorise the access via clicking an Authorise button.
My Power Query knowledge isn't great; do you kow of a way to automate that? Send a request to the Blackbaud authorisation page, wait for the user to authorise, and grab and process the returned URL? It's annoying that the authentication methods differ between the APIs!
At the moment I'm using a PowerShell script to perform the actions and return the token, which I'm then manually copying and pasting into the authorisation parameter in Power BI. The script also optionally queries the api and exports the returned data into a JSON file which I'm half tempted to use as the Power BI data source rather than querying the API directly since there's so many hoops to jump through.
I reckon I could modify the PowerShell to run on a schedule and programmtically "click" the link to get the access token. It's a shame though because the Power Query script I'm using at the moment nicely handles the API pagination of the endpoints (I modified this: https://medium.com/@marktiedemann/how-to-do-pagination-in-power-query-430460c17c78) and it would save having to have a separate datasource. That said though, if I did do that, it would likely solve the problem of the datasource not being able to be refreshed in the Power BI Service.
Quicky you would want to write an advanced query in the M language. The token is part of the URL in K12 API as well. Try something like this:
let
Source = Json.Document(Web.Contents("https://YOURURLHERE/api/authentication/login?username=QUICKEYDOE&password=12345&format=json")), //SWITCH FROM JSON TO WHATEVER YOUR API RETURNS//
T = Source[Token],
GetList = Json.Document(Web.Contents("https://YOURURL2HERE?",[Query = [t=T, format="json"]])),
#"Converted to Table" = Table.FromList(GetList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"FirstName", "LastName", "EMail", "NickName", "BadEMail", "PushpageUnsubscribe", "ChildFirstName", "ChildLastName", "ChildGradYear", "ChildGender", "ChildGradeLevelDescription"}, {"FirstName", "LastName", "EMail", "NickName", "BadEMail", "PushpageUnsubscribe", "ChildFirstName", "ChildLastName", "ChildGradYear", "ChildGender", "ChildGradeLevelDescription"})
in
#"Expanded Column1"
I think separating out the login / authentication from the target request to pull data will get you what you need.
This leads to two queries listed as sources in your Power BI desktop file Data Sour Settings that look like this outside of advanced query editor: