Building and reviews and actions report using Odata in Excel.

James Lett

Updated 11 August 2026 15:07

This guide walks you through building an Excel report that combines review details, learner information, and actions into a single table. The report shows each learner's reviews alongside their programme details, case owner, and any actions created during those reviews.

What you need before you start

Microsoft Excel (desktop version with Power Query, included in Excel 2016 and later)
Your Aptem tenant URL, for example https://yourtenant.aptem.co.uk
Your OData API key (contact your Aptem administrator if you do not have one)
Access to the OData v1 and v2 feeds on your tenant

What this report includes

Column	Description
Learner name	The learner's full name
Programme	The learner's current programme
Programme start	Programme start date
Programme end	Planned programme end date
Case owner	The assessor or tutor assigned as case owner
Review name	The name of the review
Review type	The type of review
Review status	Current status of the review
Review completed date	Date the review was completed
Review owner	The administrator who owns the review
Action text	The action description
Action status	Current status of the action (for example Open, Closed)
Action due date	The date the action is due
Action created date	The date the action was created

Understanding Power Query

Power Query is a data connection tool built into Excel. It lets you pull data from external sources (such as the Aptem OData feeds), combine tables together, and load the results into a worksheet. You access it from the Data tab in Excel.

You do not need to write code to use Power Query. However, this guide includes the query code (called M code) for each step, which you paste into the Advanced Editor. This is the most reliable way to set up OData connections.

Step 1: create the Reviews query

This query connects to the Aptem OData v1 feed and pulls review data.

In Excel, select Data > Get Data > From Other Sources > Blank Query.
In the Power Query Editor, select Advanced Editor from the toolbar.
Delete any existing text and paste the following code, replacing yourtenant with your tenant name and YOUR_API_KEY with your API key:

let myTenant = "https://yourtenant.aptem.co.uk/odata/1.0", Source = OData.Feed(myTenant, [#"X-API-Token"="YOUR_API_KEY"], [Implementation="2.0"]), Reviews_table = Source{[Name="Reviews",Signature="table"]}[Data], #"Select Columns" = Table.SelectColumns(Reviews_table, { "Id", "LearnerId", "ProgramId", "Name", "Type", "Status", "CompletedDate", "OwnerName" }) in #"Select Columns"

Select Done.
In the Query Settings pane on the right, rename the query to Reviews. This name is referenced in the report query in step 4, so it must match exactly.
Select Close & Load To > Only Create Connection.

This saves the query as a data source without loading it to a worksheet. You will use it in the final report query.

Step 2: create the Users query

This query pulls learner details, programme information, and case owner data.

Select Data > Get Data > From Other Sources > Blank Query.
Select Advanced Editor and paste the following code:

let myTenant = "https://yourtenant.aptem.co.uk/odata/1.0", Source = OData.Feed(myTenant, [#"X-API-Token"="YOUR_API_KEY"], [Implementation="2.0"]), Users_table = Source{[Name="Users",Signature="table"]}[Data], #"Select Columns" = Table.SelectColumns(Users_table, { "Id", "FullName", "Type", "UserProgram_CurrentProgramme", "UserProgram_ProgramId", "UserProgram_StartDate", "UserProgram_PlannedEndDate", "UserPersonalDetails_OwnerFullName" }), #"Filter Learners" = Table.SelectRows(#"Select Columns", each [Type] = "User") in #"Filter Learners"

Select Done.
Rename the query to Users. This name is referenced in the report query in step 4, so it must match exactly.
Select Close & Load To > Only Create Connection.

The filter on Type = "User" ensures only learner records are included, excluding administrators and employers.

Step 3: create the Actions query

This query connects to the OData v2 feed to pull action data. The v2 feed uses a direct URL rather than a table reference.

Select Data > Get Data > From Other Sources > Blank Query.
Select Advanced Editor and paste the following code:

let Source = OData.Feed( "https://yourtenant.aptem.co.uk/odata/2.0/actions?$select=Id,Action,Status,DueDate,LearnerId,ProgrammeId,CreatedDate,CreatedBy,UpdatedDate", [#"X-API-Token"="YOUR_API_KEY"], [Implementation="2.0"] ) in Source

Select Done.
Rename the query to Actions. This name is referenced in the report query in step 4, so it must match exactly.
Select Close & Load To > Only Create Connection.

The v2 endpoint uses $select in the URL to specify which fields to return. This keeps the query efficient by only pulling the data you need.

Step 4: create the report query

This query merges the three data sources into a single report table. The query names used below (Reviews, Users, Actions) must match the names you gave your queries in steps 1 to 3. If you chose different names, update the references in this code to match.

Select Data > Get Data > From Other Sources > Blank Query.
Select Advanced Editor and paste the following code:

let ReviewsData = Reviews, MergeUsers = Table.NestedJoin( ReviewsData, {"LearnerId"}, Users, {"Id"}, "User", JoinKind.LeftOuter ), ExpandUsers = Table.ExpandTableColumn(MergeUsers, "User", { "FullName", "UserProgram_CurrentProgramme", "UserProgram_StartDate", "UserProgram_PlannedEndDate", "UserPersonalDetails_OwnerFullName" }, { "LearnerName", "Programme", "ProgrammeStart", "ProgrammeEnd", "CaseOwner" }), MergeActions = Table.NestedJoin( ExpandUsers, {"LearnerId", "ProgramId"}, Actions, {"LearnerId", "ProgrammeId"}, "ActionsData", JoinKind.LeftOuter ), ExpandActions = Table.ExpandTableColumn(MergeActions, "ActionsData", { "Action", "Status", "DueDate", "CreatedDate" }, { "ActionText", "ActionStatus", "ActionDueDate", "ActionCreatedDate" }), SelectFinal = Table.SelectColumns(ExpandActions, { "LearnerName", "Programme", "ProgrammeStart", "ProgrammeEnd", "CaseOwner", "Name", "Type", "Status", "CompletedDate", "OwnerName", "ActionText", "ActionStatus", "ActionDueDate", "ActionCreatedDate" }), SortRows = Table.Sort(SelectFinal, { {"LearnerName", Order.Ascending}, {"Name", Order.Ascending} }) in SortRows

Select Done.
Rename the query to Report.
Select Close & Load To > Table and choose a worksheet location.

The report loads into your worksheet. You can refresh it at any time by selecting Data > Refresh All or right-clicking the table and selecting Refresh.

How the data joins together

The report links the three data sources using shared fields:

Reviews to Users: matched on LearnerId in Reviews to Id in Users. This brings in the learner's name, programme, and case owner.
Reviews to Actions: matched on LearnerId and ProgramId in Reviews to LearnerId and ProgrammeId in Actions. This brings in any actions for that learner and programme.

Because the actions feed does not include a review identifier, actions are linked to a learner and programme rather than to a specific review. If a learner has multiple reviews on the same programme, their actions will appear against each review.

Filtering the report

You can add filters directly to the OData URL to reduce the volume of data returned. This is useful for large tenants where loading all records would be slow.

For example, add $filter directly to the v2 actions URL to return only actions due after a specific date:

"https://yourtenant.aptem.co.uk/odata/2.0/actions?$select=Id,Action,Status,DueDate,LearnerId,ProgrammeId,CreatedDate,CreatedBy,UpdatedDate&$filter=DueDate ge 2026-01-01"

For guidance on optimising OData query performance, see the OData API performance user guide in the Help Centre.

Optional: adding closure fields to the report

The actions feed also includes ClosedDate and ClosedBy fields, which record when an action was closed and by whom. These are not included in the report by default, but you can add them if needed.

To include them, make two changes:

1. Update the Actions query (step 3) to add the fields to the $select list:

let Source = OData.Feed( "https://yourtenant.aptem.co.uk/odata/2.0/actions?$select=Id,Action,Status,DueDate,LearnerId,ProgrammeId,CreatedDate,CreatedBy,UpdatedDate,ClosedDate,ClosedBy", [#"X-API-Token"="YOUR_API_KEY"], [Implementation="2.0"] ) in Source

2. Update the report query (step 4) to expand and display the new fields. In the ExpandActions step, add them to both lists:

ExpandActions = Table.ExpandTableColumn(MergeActions, "ActionsData", { "Action", "Status", "DueDate", "CreatedDate", "ClosedDate", "ClosedBy" }, { "ActionText", "ActionStatus", "ActionDueDate", "ActionCreatedDate", "ActionClosedDate", "ActionClosedBy" }),

Then add "ActionClosedDate" and "ActionClosedBy" to the SelectFinal column list.

Troubleshooting

"The name wasn't recognised": the report query in step 4 references the other queries by name. Ensure that the query names you set in steps 1 to 3 match exactly what the report query references, including capitalisation. You can check your query names in the Queries & Connections pane (select Data > Queries & Connections to open it). If you used different names, update the references in the report query code to match.

Authentication errors: check that your API key is correct and has not expired. Replace YOUR_API_KEY in each query with a valid key.

Empty results: confirm that the feeds are enabled on your tenant. Not all tenants have all OData feeds active. Contact your Aptem administrator or raise a support ticket if you are unsure.

Slow loading: large tenants may have thousands of review and action records. Add date filters to the source queries to reduce the data volume (see Filtering the report above).