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.

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.
Important note: there are also some endpoints available in the Bulk service. These are intended for importing a large numbers of lines from, for example, transaction lines, sales invoices, items and debtors. With the Bulk service, these lines are imported faster what gives a better performance. Also be sure to import only the data you need, by using the filter-parameter. You can recognize the Bulk services by the name Bulk in the first column Service.

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 Stock lines (endpoint StockCounts).
  • 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 ‘inventory’. (att.: without capitals!)
  • In the field Endpoint enter the name of the endpoint, as shown in the Resource URI. In our example this is ‘StockCounts’. (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: ‘StockCountID,Description,Status,StockCountDate,StockCountLines’ (case sensitive!)
    Just choose the fields you need, to get a better performance!
  • In the field Expand enter, if needed, the name of the detailline endpoint. In the most endpoints this is not necessary. In our example this is ‘StockCountLines’.
  • In the field Filter enter, if needed, a filter text in order to just import the lines you need. For example ‘Status eq 21’ to just import the processed stocklines. Or for example Date ge DateTime’2020-01-01T00:00:00′ to import only data starting from 1 januari 2020.
  • 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?