Download Power BI Desktop
To get started, download the free Power BI Desktop report builder application from Microsoft at powerbi.microsoft.com.
Open Power BI Desktop and create a new, blank report.
Then, in the Home tab of ribbon menu, click on Get Data > Blank Query.
When the query editor opens, click Advanced Editor. In the text editing window that opens, delete the all of the default text in it, including the “Let…In…” stuff.
Code Snippet for Power BI / Education Management API
Copy the code below and paste it into the now blank Advanced Query Editor. Note the parts in orange.
let
ONLoginInfo = "{""username"": ""YOURUSERNAME"", ""password"": ""YOURPASSWORD""}",
Source = Json.Document(Web.Contents("https://YOURSCHOOLNAME.myschoolapp.com",
[Content = Text.ToBinary(ONLoginInfo), RelativePath="api/authentication/login", Headers=[#"Content-Type"="application/json"], IsRetry=true])),
T = Source[Token],
GetList = Json.Document(Web.Contents("https://YOURSCHOOLNAME.myschoolapp.com",[RelativePath="api/list/YOURLISTID",Query=[t=T, format="json"]])),
#"Converted to Table" = Table.FromList(GetList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"
Customize the Code for your List
Replace the strings that are highlighted in orange above. First replace the credentials with your Web Services API Manager user’s username and password. Then, replace the subdomain for your school at myschoolapp.com, and finally replace the List ID (“slid”) number for the list.
Visit the ON API “Getting Started” page to learn more about the Web Services API Manager role.
Visit the “How do I find a List ID for the ON API?” article in Blackbaud’s Knowledge Base for more information.
Click “Done” to load the list. Click the two arrows button beside the Column1 column name, then click “OK”, and finally click “Close and Apply”.
If a credential warning message displays, click “Edit Credentials” and choose “Anonymous” and click “Connect.”
If a privacy settings window pops up, check the “Ignore privacy level checks for this file” checkbox and click "Save.”
Your list’s data fields should not be loaded into Power BI and ready for you to use in tables and other visualizations. Now have fun!
Learn more about how to use Power BI
For those who are interested in digging into Power BI and learning about all the things it can do, visit Microsoft’s Power BI Learning Portal for tons of free articles and videos. We recommend the free edX online course, “Analyzing and Visualizing Data with Power BI,” for the most comprehensive training.