With the function GetTable you can use all endpoints (tables) of Exact Online, so you can import more data then just the predefined tables.

Take a look at https://start.exactonline.nl/docs/HlpRestAPIResources.aspx for an overview of all available endpoints, with the available fields, that Exact Online is offering.

Attn: this does require technical knowledge about the usage of the API endpoints in Exact Online. There are also a number of important points of attention regarding fair use policy and performance that you should consider. Additional costs may be charged for excessive data usage. Please read this manual carefully before using this feature! 

If you do not have the necessary knowledge of the API endpoints in Exact Online, we can help you with this on a consultancy basis. Please contact us for this.

 

Use the Bulk-services if available

A characteristic of the Exact Online API is that the data is read in calls of 60 lines. In addition to the regular endpoints, a bulk endpoint is also available for a number of endpoints. These bulk endpoints are read in calls of 1000 lines and therefore give a much better performance. The bulk endpoints can be recognized by the name Bulk in the Service column.

The Exact Online API uses a limit of 5000 calls per day per company, if you have reached that limit, you can no longer retrieve data for the rest of the day. Each set of 60 or 1000 lines (see below) is counted as 1 call.

Therefore, always check whether a bulk service is available for the endpoint and make use of it. You get the same data, but 16x faster. Commonly used bulk services are:

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

Use the Filter-parameter

It is also important to only read the lines of data that you actually need. For this you can use the filter parameter in the GetTable function.

Without using this filter parameter, the connector will read all the lines that exist. So if you have 10 years of history in Exact Online, this will be fully imported. Even if you filter the data in the Power Query editor afterwards, they will all be imported first. In theory it could be that you first read in 1,000,000 lines and then filter 900,000 away. That is not efficient, it is better to only read in the 100,000 lines you need. This is possible with the Filter parameter.

Examples of filters are:

• InvoiceDate ge DateTime'2020-01-01T00:00:00' (invoicedate greater than or equal to 01-01-2020)
• Status eq 50 (just invoices with status 50 – Processed)
• InvoiceNumber ge 20200001 (invoicenumber greater than or equal to 20200001)

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 if available

Separate endpoints of some data are available for the headers and the lines. For example SalesInvoices / SalesInvoiceLines, SalesOrders / SalesOrderLines and GoodsDeliveries / GoodsDeliveryLines. With the expand parameter you can read the headers and the lines in one go. This provides better performance than reading both separately.

Furthermore, with the Lines you do not always have the option to filter by date, which is possible with the headers. So always use the header-endpoint instead of the lines!

For example using the bulk/SalesOrder/GoodsDeliveries endpoint. By entering GoodsDeliveryLines at the expand parameter (and also entering this field in the select parameter), you read all associated lines together with the header. In Power Query you can make these lines visible by expanding them.

 

 

Explanation GetTable function

The working method 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'2020-01-01T00:00:00' to import only invoices starting from 1 januari 2020. Or for example Status eq 50 to just import the processed invoices.
  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.