How to query Odata V2 feeds

The OData v2 documentation on your tenant (at /odata/2.0/docs) shows you the query options available for each feed. This guide explains what those options mean and how to use them, both directly in your browser and in Excel Power Query. For a list of available fields and their types, see the Actions feed reference.

What are query options

Query options let you control what data the API returns. Instead of pulling every record and every field, you can ask for exactly what you need. This makes queries faster and the results easier to work with.

Query options are added to the end of the feed URL after a ? and separated with &. For example: https://yourtenant.aptem.co.uk/odata/2.0/actions?$filter=Status eq 'Open'&$select=Id,Action,DueDate

This request says: give me the actions where the status is Open, and only return the Id, Action, and DueDate fields.

The query options explained in the URL

$select: choose which fields to return

By default, the API returns every field. Use $select to request only the fields you need. This reduces the amount of data returned and speeds up the query.

List the field names separated by commas: /odata/2.0/actions?$select=Id,Action,Status,DueDate

Field names are case-sensitive and must match exactly as shown in the documentation on your tenant. For the full list of field names, see the Actions feed reference.

$filter: return only matching records

Use $filter to return only the records that match your criteria. The filter is written as a condition using an operator.

The available operators are:

Text values must be wrapped in single quotes (for example 'Open'). Dates do not need quotes (for example 2026-08-01). Date-time values use the format 2026-08-01T00:00:00Z.

You can combine multiple conditions with and, or, and not:

Not all fields support filtering. On the actions feed, the Action, CreatedBy, and ClosedBy fields cannot be used in $filter or $orderby. See the Actions feed reference for details.

$orderby: sort the results

Use $orderby to sort the results by a field. By default, results sort in ascending order (earliest date first, A to Z). Add desc after the field name to sort in descending order.

$top: limit the number of results

Use $top to return only the first N items. This is useful when you want a quick look at the data without pulling everything.

$count: get a total count

Add $count=true to include the total number of matching items in the response. This is useful when you want to know how many records match a filter without needing all the data.

Setting $top=0 alongside $count=true gives you just the count without returning any records.

Combining query options

You can combine any of the options above in a single request by separating them with &. For example, to get the 50 most recent open actions showing only key fields:

Trying queries in the browser

The quickest way to test a query is in your browser. Log into your Aptem tenant, then type or paste the full URL into the address bar:

The response displays as raw JSON. Select Pretty print at the top of the page to format the data so it is easier to read.

This is a good way to check that a filter returns what you expect before using it in Excel or an integration.

Using queries in Excel Power Query

The same query options work in Excel. You add them to the URL inside the OData.Feed function in the Advanced Editor.

For example, if you tested this query in the browser:

The equivalent Power Query M code is:

The URL is the same one you tested in the browser, placed inside quotes as the first argument to OData.Feed. Your API key goes in the second argument.

To set this up in Excel:

1. Select Data > Get Data > From Other Sources > Blank Query.

2. Select Advanced Editor.

3. Paste the M code above, replacing yourtenant with your tenant name and YOUR_API_KEY with your API key.

4. Select Done.

The data loads into Power Query where you can review it, merge it with other queries, or load it into a worksheet.

Building up queries step by step

A good approach when creating a new report is:

1. Start with a simple query in the browser to see what fields and data are available.

2. Add a $filter to narrow the results to what you need.

3. Add $select to limit the fields to only the ones your report uses.

4. Test the final URL in the browser to confirm the results look right.

5. Copy the URL into the Power Query Advanced Editor in Excel.

Examples

For ready-made queries you can copy and use, including full browser URLs and Excel Power Query code, see OData v2 query examples.

Next steps

Once you are comfortable with query options, you can build multi-table reports that combine v2 actions with v1 data:

• Building a reviews and actions report using OData in Excel

Getting help

If you have questions about building queries:

• 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