Building a virtual assistant exchanges report using OData in Excel

James Lett

Updated 03 September 2026 10:40

This guide walks you through building an Excel report that combines learner questions to the virtual assistant with learner and programme details. It also covers two optional summary queries: one that groups activity by topic, and one that groups it by programme.

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
Conversation ID	Groups questions asked in the same session
Question	The learner's message, as they wrote it
Topic	What the question was about, derived from the capability that answered it
Date	When the learner sent the message, in UTC

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 Users query

This query pulls learner details, programme information, and case owner data from the OData v1 feed.

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"]), 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.
In the Query Settings pane on the right, rename the query to Users. This name is referenced in later queries, 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 2: create the VAExchanges query

This query connects to the OData v2 feed to pull virtual assistant questions. 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/VirtualAssistantExchanges?$select=Id,ConversationId,LearnerId,ProgrammeId,Question,Topic,Date&$filter=Date ge 2026-01-01T00:00:00Z", [#"X-API-Token"="YOUR_API_KEY"], [Implementation="2.0"] ) in Source

Select Done.
Rename the query to VAExchanges. This name is referenced in later queries, so it must match exactly.
Select Close & Load To > Only Create Connection.

Use VAExchanges rather than the full feed name. The endpoint name is long enough that it is easy to mistype in the merge steps, which produces a "the name wasn't recognised" error.

The $filter on Date is deliberate. Every learner question is a row, so this feed grows faster than most. On a large tenant an unfiltered pull can be slow and may hit the request rate limit. Adjust the date to the earliest point you need, and keep the filter in place.

Step 3: create the report query

This query joins the two data sources into a single table. The query names used below (Users, VAExchanges) must match the names you gave your queries in steps 1 and 2.

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

let ExchangesData = VAExchanges, MergeUsers = Table.NestedJoin( ExchangesData, {"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" }), SelectFinal = Table.SelectColumns(ExpandUsers, { "LearnerName", "Programme", "ProgrammeStart", "ProgrammeEnd", "CaseOwner", "ConversationId", "Question", "Topic", "Date" }), SortRows = Table.Sort(SelectFinal, { {"LearnerName", Order.Ascending}, {"Date", Order.Descending} }) 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 two data sources on a single field: LearnerId in the exchanges feed matched to Id in the Users feed. This brings in the learner's name, programme, programme dates, and case owner.

There is no second join on programme. Programme details are held on the Users feed and come through the learner join, so no further matching is needed.

Programme in this report is the learner's current programme, taken from the Users feed. The exchanges feed carries its own ProgrammeId, recorded at the time the question was asked. For a learner who has since moved to a different programme, these differ. Use the feed's ProgrammeId where you need the programme as it was at the time, as the programme summary below does.

Filtering the report

Add filters directly to the OData URL to reduce the volume of data returned. This is more efficient than loading everything and filtering in Excel.

For example, to return only exchanges on one topic within a date range:

"https://yourtenant.aptem.co.uk/odata/2.0/VirtualAssistantExchanges?$select=Id,ConversationId,LearnerId,ProgrammeId,Question,Topic,Date&$filter=Topic eq 'safeguarding' and Date ge 2026-06-01T00:00:00Z"

Note that Question cannot be used in $filter, so there is no way to search the text of learners' questions from the URL. Filter on Topic instead, then search the returned text in Excel.

For more query patterns, see Virtual assistant exchanges query examples.

Optional: summarising activity by topic

The report above gives one row per question, which is what you need for looking at individual learners. For a management view of what learners are asking about, a summary is easier to read.

Rather than change the report query, create a second query alongside it. Both can load to their own worksheet and refresh together.

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

let ExchangesData = VAExchanges, MergeUsers = Table.NestedJoin( ExchangesData, {"LearnerId"}, Users, {"Id"}, "User", JoinKind.LeftOuter ), ExpandUsers = Table.ExpandTableColumn(MergeUsers, "User", { "UserProgram_CurrentProgramme" }, { "Programme" }), LabelBlankTopics = Table.TransformColumns( ExpandUsers, {{"Topic", each if _ = null then "(not recorded)" else _, type text}} ), GroupRows = Table.Group( LabelBlankTopics, {"Programme", "Topic"}, { {"Exchanges", each Table.RowCount(_), Int64.Type}, {"Learners", each List.Count(List.Distinct(_[LearnerId])), Int64.Type}, {"FirstAsked", each List.Min(_[Date]), type datetimezone}, {"LastAsked", each List.Max(_[Date]), type datetimezone} } ), SortRows = Table.Sort(GroupRows, { {"Programme", Order.Ascending}, {"Exchanges", Order.Descending} }) in SortRows

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

The result has one row per programme and topic:

Programme	Topic	Exchanges	Learners	FirstAsked	LastAsked
Business Administration L3	learning_plan	412	96	02/06/2026	29/08/2026
Business Administration L3	general	287	88	01/06/2026	31/08/2026
Business Administration L3	evidence	134	51	04/06/2026	28/08/2026
Team Leader L3	general	96	24	03/06/2026	30/08/2026

Exchanges counts questions and Learners counts distinct people, so you can tell a topic that many learners touch once from one that a handful return to repeatedly.

This query does not include Question, so the output carries no learner-written text. Use it wherever the report is shared more widely than the detail table.

Two things worth knowing. Programme is the learner's current programme from the Users feed, so exchanges from a learner who has since moved on are counted against their new programme. Group on the feed's own ProgrammeId instead if you need the programme as it was at the time, as the next section does. And (not recorded) covers historical rows from before the topic was captured, which will show up as a large bucket on older data and taper off.

Optional: summarising activity by programme

The topic summary groups on the learner's current programme. If a learner has moved on, their older exchanges are counted against their new programme. For programme-level reporting that usually matters, so this version groups on the ProgrammeId recorded on the exchange itself.

The exchanges feed holds a programme ID but not a programme name, so the query builds a small lookup from the Users feed first.

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

let ExchangesData = VAExchanges, ProgrammeLookup = Table.Group( Table.SelectRows(Users, each [UserProgram_ProgramId] <> null), {"UserProgram_ProgramId"}, {{"ProgrammeName", each List.First(_[UserProgram_CurrentProgramme]), type text}} ), MergeProgramme = Table.NestedJoin( ExchangesData, {"ProgrammeId"}, ProgrammeLookup, {"UserProgram_ProgramId"}, "Prog", JoinKind.LeftOuter ), ExpandProgramme = Table.ExpandTableColumn(MergeProgramme, "Prog", {"ProgrammeName"}, {"Programme"}), LabelUnknown = Table.TransformColumns( ExpandProgramme, { {"Programme", each if _ = null then "(name not available)" else _, type text}, {"ProgrammeId", each if _ = null then -1 else _, Int64.Type} } ), GroupRows = Table.Group( LabelUnknown, {"ProgrammeId", "Programme"}, { {"Exchanges", each Table.RowCount(_), Int64.Type}, {"Learners", each List.Count(List.Distinct(_[LearnerId])), Int64.Type}, {"Conversations", each List.Count(List.Distinct(_[ConversationId])), Int64.Type}, {"FirstAsked", each List.Min(_[Date]), type datetimezone}, {"LastAsked", each List.Max(_[Date]), type datetimezone} } ), AddPerLearner = Table.AddColumn( GroupRows, "ExchangesPerLearner", each if [Learners] = 0 then null else Number.Round([Exchanges] / [Learners], 1), type number ), SortRows = Table.Sort(AddPerLearner, {{"Exchanges", Order.Descending}}) in SortRows

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

The result has one row per programme:

ProgrammeId	Programme	Exchanges	Learners	Conversations	ExchangesPerLearner
312	Business Administration L3	1,204	214	486	5.6
288	Team Leader L3	341	78	152	4.4
-1	(not recorded)	87	31	44	2.8

ExchangesPerLearner is the useful one for comparing programmes of different sizes. A programme with high exchanges but comparatively few conversations means learners are asking several questions per session.

Like the topic summary, this query carries no learner-written text.

Three caveats apply. (name not available) appears when no learner is currently on that programme, so the lookup has nothing to match; the ProgrammeId is still correct and you can identify it in Aptem. ProgrammeId -1 is a placeholder for exchanges recorded before the programme was captured, not a real programme ID. And the lookup takes one name per programme ID, so if a programme has been renamed you get whichever name the Users feed currently holds.

Troubleshooting

"The name wasn't recognised": the report and summary queries reference the other queries by name. Ensure that the query names you set in steps 1 and 2 match exactly what the later queries reference, 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 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, or a refresh that stalls: the v2 feeds are rate limited to 120 requests per minute per user, with at most four requests in flight at once. A large unfiltered pull is paged 1,000 rows at a time and can approach that limit. Narrow the date filter in step 2, and avoid refreshing several OData reports at the same moment.

A 429 response: this means a request limit was exceeded. Where a per-minute limit was hit, the response carries a Retry-After value in whole seconds. Wait at least that long before retrying. Reducing how much data you pull is usually a better fix than retrying.

Getting help

If you have questions about building this report or need your API key:

Contact your Implementation Consultant if you are still in implementation
Contact your Customer Success Manager if you are a live customer
Raise a support ticket