Psoda data can be used in Microsoft BI to create interactive visualizations and business intelligence capabilities with their interface.
To get data into Microsoft BI, first we need the API key of a user who has access to the data you want.
To do this navigate to that user and select the three-dot options menu. In there, click on “API key” as shown in Figure 1.
Next, we need the object ID of the organisation/programme/project that you want to use the data from. Navigate to the organisation/programme/project that you want to use and go to the details tab. There you will find the object ID as shown in Figure 3.
Copy or note down this object ID.
Click the “Get data” dropdown and select “Web” as shown in Figure 4.
First, we need to create the URL of the data we want from Psoda. Use this URL as a guide:
https://www.psoda.com/rest_api/v1/objects/INSERT_OBJECT_ID/descendants.json?classes=lesson&fields=obj_id,description,category
Copy and paste the above URL into “URL parts” on the Microsoft BI popup as shown in Figure 5.
In the URL, where it says “INSERT_OBJECT_ID”, change that text to the object ID noted down earlier.
In the URL, where it says “lesson”, change that text to whichever type of data you would like to get. For example, this could be “action” and that would return all the actions on this organisation/programme/project.
In the URL after it says “fields”, there is a list of fields that will be included for each of the data. Currently it will get all the lessons and each of their object IDs, descriptions, and categories. You can change these to whatever you like. For example, if you want the title of the lesson you can add “,title” to the URL.
Once you are happy with what data you want, the last step is to include the API key noted down earlier. Under the option “HTTP request header parameters” in the Microsoft BI popup, enter the text “API-Key” as shown in Figure 5. In the field next to that, paste the API key noted down earlier.
Click “Ok” and you will now be able to use your Psoda data in Microsoft BI!
To use version 2 of our API a little more configuration is required.
To start, create a new blank query.
Paste the text below into the blank query, replace YOUR_API_REQUEST_HERE with your API request URI. Replace YOUR_API_KEY_HERE with your API key. Name this function something related to the query, in this case we will use fGetDescendants.
= (Pagesize as number)=>
let
Source = Json.Document(Web.Contents("YOUR_API_REQUEST_HERE"&"&pageSize="&Number.ToText(Pagesize), [Headers=[#"API-Key"="YOUR_API_KEY_HERE"]]))
in
Source
Create another blank query, and paste the text below in. Replace YOUR_API_KEY_HERE with your API key. Set the name of this query to fNextPage.
= (Link as text)=>
let
Source = Json.Document(Web.Contents(Link, [Headers=[#"API-Key"="YOUR_API_KEY_HERE"]]))
in
Source
Now create one more blank query, this will be the one that retrieves all of your data from Psoda. Paste the text below in, replace FIRST_FUNCTION_HERE with the first function you have created. In this case it will be fGetDescendants
= List.Generate(()=>
[Result = if List.IsEmpty(FIRST_FUNCTION_HERE(50)[data]) then null else FIRST_FUNCTION_HERE(50)[data], Next= FIRST_FUNCTION_HERE(50)[links][next]],
each [Result]<>null,
each [Result = if [Next]=null then null else if List.IsEmpty(fNextPage([Next])[data]) then null else fNextPage([Next])[data], Next= if [Next]=null then null else if fNextPage([Next])[links] is list then null else fNextPage([Next])[links][next]],
each [Result])
You should now be getting all of your data from our API.
To get data into Microsoft BI, first we need the API key of a user who has access to the data you want.
To do this navigate to that user and select the three-dot options menu. In there, click on “API key” as shown in Figure 1.
Figure 1 – User options menu API key
Figure 2 – User API key popup
Copy or note down the API key found in the popup (keep this API key safe. It could be used by a malicious user to access all of your data).Next, we need the object ID of the organisation/programme/project that you want to use the data from. Navigate to the organisation/programme/project that you want to use and go to the details tab. There you will find the object ID as shown in Figure 3.
Copy or note down this object ID.
Figure 3 – Organisation details tab object ID
Open Microsoft BI on your computer.Click the “Get data” dropdown and select “Web” as shown in Figure 4.
Figure 4 – Microsoft BI “get data” dropdown
Click on “Advanced” in the popup.
Figure 5 – Microsoft BI “get data” popup
Figure 5 shows what we need to enter in this popup to get Psoda data into Microsoft BI.First, we need to create the URL of the data we want from Psoda. Use this URL as a guide:
https://www.psoda.com/rest_api/v1/objects/INSERT_OBJECT_ID/descendants.json?classes=lesson&fields=obj_id,description,category
Copy and paste the above URL into “URL parts” on the Microsoft BI popup as shown in Figure 5.
In the URL, where it says “INSERT_OBJECT_ID”, change that text to the object ID noted down earlier.
In the URL, where it says “lesson”, change that text to whichever type of data you would like to get. For example, this could be “action” and that would return all the actions on this organisation/programme/project.
In the URL after it says “fields”, there is a list of fields that will be included for each of the data. Currently it will get all the lessons and each of their object IDs, descriptions, and categories. You can change these to whatever you like. For example, if you want the title of the lesson you can add “,title” to the URL.
Once you are happy with what data you want, the last step is to include the API key noted down earlier. Under the option “HTTP request header parameters” in the Microsoft BI popup, enter the text “API-Key” as shown in Figure 5. In the field next to that, paste the API key noted down earlier.
Click “Ok” and you will now be able to use your Psoda data in Microsoft BI!
Pagination using API V2
To use version 2 of our API a little more configuration is required.
To start, create a new blank query.
Figure 6 – Blank Query button in PowerBI
Paste the text below into the blank query, replace YOUR_API_REQUEST_HERE with your API request URI. Replace YOUR_API_KEY_HERE with your API key. Name this function something related to the query, in this case we will use fGetDescendants.
= (Pagesize as number)=>
let
Source = Json.Document(Web.Contents("YOUR_API_REQUEST_HERE"&"&pageSize="&Number.ToText(Pagesize), [Headers=[#"API-Key"="YOUR_API_KEY_HERE"]]))
in
Source
Figure 7 – Standard request query
Create another blank query, and paste the text below in. Replace YOUR_API_KEY_HERE with your API key. Set the name of this query to fNextPage.
= (Link as text)=>
let
Source = Json.Document(Web.Contents(Link, [Headers=[#"API-Key"="YOUR_API_KEY_HERE"]]))
in
Source
Figure 8 – Blank Query button inside query editor
Figure 9 – Next Page Query with correct function name
Now create one more blank query, this will be the one that retrieves all of your data from Psoda. Paste the text below in, replace FIRST_FUNCTION_HERE with the first function you have created. In this case it will be fGetDescendants
= List.Generate(()=>
[Result = if List.IsEmpty(FIRST_FUNCTION_HERE(50)[data]) then null else FIRST_FUNCTION_HERE(50)[data], Next= FIRST_FUNCTION_HERE(50)[links][next]],
each [Result]<>null,
each [Result = if [Next]=null then null else if List.IsEmpty(fNextPage([Next])[data]) then null else fNextPage([Next])[data], Next= if [Next]=null then null else if fNextPage([Next])[links] is list then null else fNextPage([Next])[links][next]],
each [Result])
Figure 10 – Final request and all data returned from API
You should now be getting all of your data from our API.