With the GetTable function, you can use all endpoints (tables) from Exact Online, allowing you to import more data than just the standard predefined tables.

A complete overview of all endpoints and their corresponding fields can be found in the official Exact Online documentation: https://start.exactonline.nl/docs/HlpRestAPIResources.aspx

Please note: this requires technical knowledge of how to use Exact Online’s API endpoints. There are also several important considerations regarding the fair use policy and performance. Excessive data usage may result in additional charges. Read this guide carefully before using this function!

If you do not have the required knowledge of the Exact Online API endpoints yourself, we can assist you on a consultancy basis. Feel free to contact us for this.

 

Use Bulk Services whenever possible

An important characteristic of the Exact Online API is that standard endpoints retrieve data in batches of 60 records per call. For large datasets, this can result in a high number of calls and therefore limited performance.

However, for a number of endpoints, special Bulk services are available. These services retrieve data in batches of 1000 records per call, which significantly improves performance. You can identify these services by the word Bulk in the Service column of the Exact Online documentation.

Exact Online applies a limit of 5000 API calls per day per administration. Once this limit is reached, you cannot retrieve any more data for the rest of the day. It is therefore essential to minimize the number of calls, which makes Bulk services especially important.

Always check whether a Bulk service is available for the endpoint you want to use, and use it whenever possible. You will receive the same data, but much faster and with significantly fewer API calls. Frequently used Bulk services include:

• LedgerTransactions (bulk/Financial/TransactionLines)
• LedgerAccounts (bulk/Financial/GLAccounts)
• SalesInvoices (bulk/SalesInvoice/SalesInvoices en bulk/SalesInvoice/SalesInvoiceLines)
• SalesOrders (bulk/SalesOrder/SalesOrders en bulk/SalesOrder/SalesOrderLines)
• GoodsDeliveries (bulk/SalesOrder/GoodsDeliveries en bulk/SalesOrder/GoodsDeliveryLines)
• Quotations (bulk/CRM/Quotations en bulk/CRM/QuotationLines)
• Items (bulk/Logistics/Items)
• SalesPrices (bulk/Logistics/SalesItemPrices)
• Accounts (bulk/CRM/Accounts)

 

Always use the Filter-parameter

It is essential to load only the records you actually need. For this, you can use the Filter parameter in the GetTable function.

If you apply a filter that is too broad, the connector will first retrieve all records that match that filter. For example, if you have 10 years of history in Exact Online, the entire dataset will be pulled in — even if you apply additional filtering later in Power Query. That additional filtering only happens after the data has been loaded.

In extreme cases, this could mean loading 1,000,000 records and then filtering out 900,000 of them. This is not efficient and results in unnecessary API calls. By using the Filter parameter correctly, you can reduce the dataset at the source so that only the relevant records are imported.

Examples of filters are:

• InvoiceDate ge DateTime'2024-01-01T00:00:00' (invoicedate greater than or equal to 01-01-2024)
• "InvoiceDate ge DateTime'" & Text.From(Date.Year(DateTime.LocalNow())-1) & "-01-01T00:00:00'" (invoice date on or after January 1st of last year, adjustable afterwards in the Power Query Editor.)
• "InvoiceDate ge DateTime'" & Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()),-2),"yyyy-MM-dd") & "-01-01T00:00:00'" (invoice date within the last 2 months, adjustable afterwards in the Power Query Editor.)
• Status eq 50 (just invoices with status 50 – Processed)
• InvoiceNumber ge 20240001 (invoicenumber greater than or equal to 20240001)

The functions you can use in the filter are: eq (equals), ne (not equal), gt (greater than), lt (less than), ge (greater than or equal to), le (less than or equal to), and, or.

For the following API endpoints (standard and bulk) a filter is mandatory:

• Cashflow/PaymentTerms
• CRM/Accounts
• CRM/Addresses
• CRM/Contacts
• CRM/Quotations en CRM/QuotationLines
• Documents/DocumentAttachments
• Documents/Documents
• Financial/GLAccounts
• Financial/GLClassifications
• Financial/TransactionLines
• Logistics/Items
• Logistics/SalesItemPrices
• SalesInvoice/SalesInvoices en SalesInvoice/SalesInvoiceLines
• SalesOrder/GoodsDeliveries en SalesOrder/GoodsDeliveryLines
• SalesOrder/SalesOrders en SalesOrder/SalesOrderLines

 

Use the Expand Parameter whenever possible

For various types of data, Exact Online provides separate endpoints for headers and lines. Examples include:

• SalesInvoices / SalesInvoiceLines
• SalesOrders / SalesOrderLines
• GoodsDeliveries / GoodsDeliveryLines

With the Expand parameter, you can load the headers and their related lines in a single request. This results in significantly better performance than retrieving the headers and lines separately.

Additionally, line endpoints often do not support date filtering, whereas the corresponding header endpoints do. Therefore, you should always use the header endpoint in combination with the Expand parameter—not the lines endpoint directly.

When using the endpoint bulk/SalesOrder/GoodsDeliveries, you can retrieve the related lines directly through the Expand parameter:

• Set the Expand parameter to: GoodsDeliveryLines
• Add the same field to the Select parameter

This ensures that when retrieving the headers, all associated lines are automatically included. In Power Query, you can then make those lines visible by expanding them.

 

Explanation GetTable function

The workflow is as follows:

• Open the folder Functions in the navigator screen and check the function GetTable.
• In this printscreen we have imported the salesitem prices (SalesItemPrices).
• 
• In the field CompanyCode enter the companycode from PowerBIConnector.nl.
• In the field Service enter the servicename of the endpoint, as shown in the Resource URI. In our example this is  the service bulk. (att.: without capitals!)
• In the field Endpoint enter the name of the endpoint, as shown in the Resource URI. In our example this is the endpoint Logistics/SalesItemPrices. (att.: case sensitive!)
• In the field Select enter the fieldnames you want to import from this endpoint, separated by comma’s. In our example these are the fields: Account,Item,Quantity,Price (case sensitive!)
  Only read the fields that you actually use in the dashboard. That is one of the basic principles of a good data model in Power BI. Every field that you read unnecessarily takes unnecessary import time and will unnecessarily slow down the data model in Power BI.
• In the field Expand enter, if needed and available, the name of the detailline endpoint, recognizable on the description ‘Collection of lines’. For example in the endpoint bulk/SalesOrder/SalesOrders there is a expand possible to the SalesOrderLines. This way you can import the headers and lines in one go.
• In the field Filter always enter a filter text, in order to just import the lines you need (read above). For example InvoiceDate ge DateTime'2025-01-01T00:00:00' to import only invoices starting from 1 januari 2025. Or for example Status eq 50 to just import the processed invoices. Or InvoiceNumber ge 20250001 to import only invoice numbers greater than or equal to 20250001.
  The equations you can use are: eq (equal to), ne (not equal to), gt (greater than), lt (less than), ge (greater than or equal to), le (less than or equal to) , and, or.
• In the field Criteria enter the function criteria, this is only mandatory for most Function URI’s. For example the function uri AgingOverviewByAccount has a mandatory parameter accountId={Edm.Guid}. In this case enter the full parameter including the parameter name, for example accountId=guid'1fecc344-5957-4099-8bd4-94b650064467'
• In the field Beta enter the text ‘true’ in case this is a beta endpoint, otherwise you can leave this field empty.
• Click on Apply, the preview is shown.
• 
• Click on Transform Data (make sure GetTable is checked) in order to import the table into the Power Query Editor. There you can expand the Result column to show the content of the records.

 

There is also a GetTableXml function available that can import data from the old Exact Online XML API. However, because the XML API will be phased out by Exact Online, we do not recommend using the GetTableXml function.