This article is about a feature that is part of the May 2024 release.
ReviewResponses is a new OData table that contains question and answer responses for every custom review question, indexed by Learner Id, Review Id, Review Section and date. Each review response has a unique row Id.
This document describes Power Query code (M code) for loading data from the ReviewResponses feed.
The ReviewResponses table is very large depending on the number of reviews and learners. Consequently:
- The ReviewResponses feed is limited to returning 5000 records per query call.
- To GET data the query to the feed must be filtered by date.
- An error will be returned if the query call is not filtered by date.
- OData feeds can be paged using $skip and $top parameters to pull down batches of records
By default, the sample query below returns all responses dated between yesterday and the day before yesterday (using the current date as a reference point).
The query can be edited to change the date period:
By default this is from 1 day before yesterday to yesterday using current date. If you want data for two days then change num_days (line 9) to 2, if you want to filter data up one week ago then change offset (line 11) to 7 (i.e. 7 days ago).
Power Query Code for ReviewResponses
In MS Excel or Power BI add a new blank query and paste in the code below.
Remember to edit the APIKey and host_url fields to match your API key and Aptem tenant web address.
// Power Query / M code
let
APIKey = "YOUR_API_KEY",
host_url = "https://YOUR_TENANT_NAME.aptem.co.uk/odata/1.0" ,
pageSize = 5000, // Number of rows to fetch per page
num_days = 1 , // number of days data required – should be a positive number
offset = 1, // assume 1 starting from yesterday, but could be further in the past – should be a positive number
Today = DateTimeZone.UtcNow(),
yesterday = Date.ToText( DateTime.Date(
Date.StartOfDay(Date.AddDays(Today, - offset ))),
[Format="yyyy-MM-dd'Z'"]
),
firstday = Date.ToText( DateTime.Date(
Date.StartOfDay(Date.AddDays(Today, - (offset+ num_days) ))),
[Format="yyyy-MM-dd'Z'"]
),
tbl = "ReviewResponses",
fields = { "Date" , "Id", "ReviewId", "LearnerId", "Section", "Question", "Answer" },
field_str = Text.Combine( fields, ","), // create string of required columns
select= "&$select=" & field_str ,
count_url = host_url & "/" & tbl & "?$filter=Date gt " & firstday & " and Date lt " & yesterday & "&$count=true" &
select & "&$top=0",
countrows =
let
web = Web.Contents(count_url , [ Headers = [#"X-API-Token"=#"APIKey"] ]),
result = Json.Document(web),
totalRows = result[#"@odata.count"]
in
totalRows, // returns total rows
pages = Number.RoundUp(countrows / pageSize), // Calculate the total number of pages
// pages = 4, // for testing purposes (first 4 pages) can comment out above line and replace
pageNumber= 1, // start the loop
data_base_url = host_url & "/" & tbl & "?$filter=Date gt " & firstday & " and Date lt " & yesterday & select ,
getPage = (pageNumber) =>
let
skip = (pageNumber - 1) * pageSize, // Calculate the number of rows to skip for the current page
url = data_base_url & "&$skip=" & Text.From(skip) & "&$top=" & Text.From(pageSize) ,
// construct url for page number then load the data
atable = OData.Feed(url, [#"X-API-Token"= #"APIKey" ])
in
atable,
pageList = List.Generate(
() => 1, // Start with the first page
each _ <= pages, // Continue while the page number is less than or equal to the total number of pages
each _ + 1, // Increment the page number
each getPage(_) // Fetch the data for the current page
),
combinedTable = Table.Combine(pageList)
in
combinedTable
Explanation of code
The following explanation of the above code is provided for reference only.
Less technical readers should be able to copy-paste the code into a new query and edit lines 4 and 5 to match your API key and Aptem tenant web address.
The code does the following:
- Sets a date range to filter the table. By default this is from 1 day before yesterday to yesterday using the current date. If you want data for two days then change num_days (line 9) to 2, if you want to filter data up to one week ago then change offset (line 11) to 7 (ie 7 days ago). Lines 13 to 21 convert the selected date range into strings that are used in the query.
- Get the total number of records in the selected date range. This is achieved by constructing a $count query string (line 29) which is used to return the row count (line 32 to 38)
- Calculate the number of 'pages' (of pageSize, default = 5000, [line 7]) needed to get the record count in the selected date range.
- Loop through the pages to get the data (lines 56 to 61) using the getPage function (lines 47 to 54)
- Row Combine the tables into a single table (line 63) which is returned.
Counting records
Queries to the ReviewResponses OData feed need to be filtered by date. Here is an example of a query string which counts the number of records between the dates from 12/02/2024 (00:00am) and less than 13/02/2024 (00:00am):
https://demo.aptem.co.uk/odata/1.0/ReviewResponses?$filter=Date gt 2024-02-12Z and Date lt 2024-02-13Z&$count=true&$select=Id,ReviewId&$top=0
Retrieving records
The above code exports batches of 5000 records
Example of a query string to export the first 5000 records between the dates 12/02/2024 (00:00am) and less than 13/02/2024 (00:00am):
https://demo.aptem.co.uk/odata/1.0/ReviewResponses?$filter=Date gt 2024-02-12Z and Date lt 2024-02-13Z&$select=Date,Id,ReviewId,LearnerId,Section,Question,Answer&$top=5000&$skip=0
Example of a query string to export records 5001 to 10,000 between the dates 12/02/2024 (00:00am) and less than 13/02/2024 (00:00am):
https://demo.aptem.co.uk/odata/1.0/ReviewResponses?$filter=Date gt 2024-02-12Z and Date lt 2024-02-13Z&$select=Date,Id,ReviewId,LearnerId,Section,Question,Answer&$top=5000&$skip=5000
Example of a query string to export records 10,001 to 15,000 between the dates 12/02/2024 (00:00am) and less than 13/02/2024 (00:00am):
https://demo.aptem.co.uk/odata/1.0/ReviewResponses?$filter=Date gt 2024-02-12Z and Date lt 2024-02-13Z&$select=Date,Id,ReviewId,LearnerId,Section,Question,Answer&$top=5000&$skip=10000