Retrieving Learning Plan evidence via OData

This article is about a feature that is part of the July 2025 release.

Overview

LearningPlanEvidences is a new OData table that contains a list of all learning plan evidence for every learner, learning plan component and programme. Each evidence has a unique Id which is the existing evidence id referenced in the Users (UserEvidences_Evidences) collection and in Messages when a learner receives feedback on a submission.

This document describes Power Query code (M code) for loading data from the LearningPlanEvidences feed.

The LearningPlanEvidences table is very large depending on the number of evidence items submitted against Learning Plan components by learners. Consequently:

• The LearningPlanEvidences feed is limited to returning 5000 records per query call.
• The OData feed should be paged using $skip and $top parameters to pull down batches of records.

Power Query Code for LearningPlanEvidences

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 to page the LearningPlanEvidences table

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 // max value is 5000
   
    tbl = "LearningPlanEvidences",

    fields = {"Id", "ComponentId", "LearnerId", "ProgramId", "SubProgramId", "SubmittedById", "SubmissionDate", "SpentTime",
               "SpentTimeType", "EvidenceName", "EvidenceKind", "ConfirmedStatus", "LatestStatus", "CompletedDate"},

    field_str =  Text.Combine( fields, ","),    // create string of required columns      
    select= "&$select=" & field_str  , 

    count_url = host_url  & "/" &  tbl  & "?" &  "&$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  & "?" &  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 the Power Query code

The above code does the following:

• Creates a baseline query listing the columns in the evidence table.
• Get the total number of records in the selected date range. This is achieved by constructing a $count query string (line 19) which is used to return the row count (line 22 to 27)
• Calculate the number of 'pages' (of pageSize, default = 5000, [line 8]) needed to get the record count in the selected date range.
• Loop through the pages to get the data (lines 44 to 49) using the getPage function (lines 35 to 42)
• Row Combine the tables into a single table (line 51) which is returned.

The resulting table of evidence can be joined with the LearningPlanComponents table (there may be more than one evidence per component), and also with the Messages table in order to access feedback comments recorded against each submitted evidence item.