How to connect tables in Power BI using Exact Online Data

[3tf1]

I’m currently using Ivantive API connection to connect Exact Online and Power BI for financial reporting. I’ve connected the following tables :

• TransactionLinesIncremental
• AccountsIncremental
• GLAccountsIncremental
• Buisiness Line
• CostCenters

These tables were connected via OData link with the help of one of your colleagues. The problem is that I am not able to connect this tables with each other.

None of the IDs present in the ExactOnlineRest.HRM.CostCenters@eol table (this is the employee table) correspond to any ID in the *Incremental tables . As a result, transactions cannot be attributed to the employee.

These are the columns in the ExactOnlineRest.HRM.Costcenters@eol table:

I’ve used the ID column from ExactOnmineRest.HRM.Costcenters@eol table to try and connect with the incremental tables (all of them) but i didn’t find any column that has the same ID.

Here are the columns in the table ExactOnlineRest.Incremental.TransactionLinesIncremental@eol

I’m not encountering any error, I’m just not being able to design my model due to the missing join key between the differents tables. My goal is to link the tables together. This link should be build in initially but I’m not being able to find this link.

Can you please help me understand how can i link the tables with each other?

[forums]

Cost centers, cost units and journals spring into mind as lookup tables which are typically joined on the division code and code column.

It is recommended to try those.

Please note that cost centers does not contain employee data but analytical accounting features (which some companies might use the employee name or code for).

The data models (including ERD for Exact Online with some additional background) are available on:

Invantive UniversalSQL Data Models: Comprehensive Integration Documentation