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 Sync Services or 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 Sync and Bulk services are available. These services read data in batches of 1,000 records per call, significantly improving performance. You can recognize these services by the name Sync or 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 Sync- or Bulk services especially important.

• Bulk Services: Retrieve data in batches of 1,000 records per call. Always use a filter to limit the data to only the records you need.
• Sync Services (recommended): Also retrieve data in batches of 1,000 records per call, but additionally provide the ability to load changed data incrementally. If you read and store the Timestamp column in the Lakehouse, you can then easily retrieve only new or modified records using:
  Timestamp gt <LastTimestamp>L (note the L after the timestamp!). This approach is even more efficient than Bulk Services and is therefore preferred.

Always check whether a Sync Service or Bulk Service is available for the endpoint and use it whenever possible. You will receive the same data, but much faster and with significantly fewer API calls.

 

Always use the Filter-parameter

It is essential to load only the records you actually need. Therefore, it is mandatory to always use the Filter parameter in the GetTable function.

If you apply an overly broad filter, the connector will first load all records that match that filter. For example, if you have 10 years of history in Exact Online, the entire dataset will be retrieved—even if you later apply additional filtering in the dataframe. That extra filtering happens after the data has already been loaded.

In extreme cases, this could mean loading 1,000,000 rows and then filtering out 900,000 of them. This is inefficient and results in unnecessary API calls. By using the Filter parameter correctly, you can limit the dataset at the source to only the relevant records.

Examples of filters are:

• InvoiceDate ge DateTime'2025-01-01T00:00:00' (invoicedate greater than or equal to 01-01-2025)
• "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 20250001 (invoicenumber greater than or equal to 20250001)

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.

 

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

Create a notebook code cell with the following code:

# Set the parameters
tablename = "LedgerGetTable"

# Import data with GetTable function and save to lakehouse
try:
    # Create dataframe from GetTable function
    df_custom = ExactOnline.GetTable(
        companycode="10000",
        service="bulk",
        endpoint="Financial/GLAccounts",
        select="ID,Code,Description",
        filter="Code ge '4000'",
        # expand=None,
        # criteria=None,
        # beta=False,
    )

    if not df_custom.isEmpty():
        # Display dataframe
        display(df_custom)

        # Save dataframe as lakehouse table
        df_custom.write.format("delta").mode("overwrite").save(f"Tables/dbo/{tablename}")

    else:
        print("No data available")

except Exception as e:
    print(f"❌ GetTable failed: {e}")
    import traceback
    traceback.print_exc()

Below is an explanation of the parameters used:

• tablename: Enter the name under which the table should be stored in the Lakehouse. Important: Do not use an existing table name, as it will be overwritten.
• companycode: Enter the administration code(s) from PowerBIConnector.nl.
• service: Enter the service name of the endpoint, as specified in the Resource URI in the Exact Online documentation. In our example, this is the service bulk (note: lowercase only!).
• endpoint: Enter the name of the endpoint, as specified in the Resource URI in the Exact Online documentation. In our example, this is the endpoint Financial/GLAccounts (note: case-sensitive!).
• select: Enter the fields you want to retrieve from this endpoint, separated by commas. In our example: ID,Code,Description (case-sensitive!) Only import the fields you actually use in the dashboard. This is one of the core principles of a good data model. Every unnecessary field increases import time and slows down the data model in Fabric and Power BI.
• filter: Always specify a filter so that you only load the records you need (see above). For example, use a date filter to load only records within the desired period range: InvoiceDate ge DateTime'2026-01-01T00:00:00' to load invoices from January 1, 2026 onward. Or: Status eq 50 to load only processed invoices. Or: InvoiceNumber ge 20260001 to load invoices with numbers greater than or equal to 20260001.
  The comparison operators 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.
• expand: If possible, enter the endpoint line recognized by the description “Collection of lines” in the Exact Online documentation. This allows you to load headers and lines in one go for endpoints where this applies. For example, in the endpoint bulk/SalesOrder/SalesOrders, an expand is possible to SalesOrderLines.
• criteria: Enter the function criteria. This is only required for most Function URIs. For example, the function URI AgingOverviewByAccount requires the parameter accountId={Edm.Guid}. Enter the full parameter including the name, for example: accountId=guid'1fecc344-5957-4099-8bd4-94b650064467'.
• beta: Enter True if it is a beta endpoint; otherwise, leave this field empty.

You can disable a line by prefixing it with # (comment).

The GetTable function returns a dataframe. In the example above, the dataframe is immediately stored in the Lakehouse. However, you can use all Fabric capabilities to transform the dataframe before saving it to the Lakehouse.