1. Home
  2. Docs
  3. Power BI Connector for Exact Online (English)
  4. 2. Power BI Desktop
  5. 2.3 Function GetTable

2.3 Function GetTable

With the function GetTable you can use all endpoints (tables) of Exact Online, so you can import more data then just the predefined tables. This does require some technical knowledge about the usage of the endpoints of the Exact Online API. There are also a number of important points of attention regarding fair use policy and performance that you should consider.

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.

Use the Bulk-services if available

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

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.

 

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, the name of the detailline endpoint, recognizable on the description ‘Collection of lines’. In the most endpoints this is not necessary. For example in the endpoint bulk/SalesOrder/SalesOrders there is a expand possible to the SalesOrderLines.
  • 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 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.
Was dit artikel nuttig voor je? Ja Nee

Hoe kunnen we je helpen?