How to Connect Exact Online to Power BI: 4 Practical Methods Compared

Scenario: Finance & Operations Report on Exact Online

Assume this setup:

• Dutch SME using Exact Online for:
  • General ledger and trial balance
  • Accounts receivable/payable
  • Sales invoices
• Power BI workspace in Power BI Pro, not Premium
• Target report:
  • P&L and balance sheet
  • Aging overview for receivables
  • Invoice-level detail for sales
• Requirements:
  • Refresh at least once per day
  • No Excel exports or manual uploads
  • Minimal IT admin overhead

We’ll use this scenario to compare four methods:

1. Direct REST API from Power BI (Power Query)
2. Exact Online via an intermediate SQL database (ETL job)
3. Third-party connector / integration platform
4. Custom connector deployed with an on-premises data gateway

Each method is technically viable. The trade-offs are around:

• Licensing: Pro vs Premium, and where gateway comes in
• Refresh reliability: token handling, throttling, schema drift
• Complexity: who owns the integration (BI vs IT vs vendor)

Method 1: Direct REST API from Power BI (No Gateway, Pro-Friendly)

This is the “pure” approach: Power Query calls the Exact Online REST APIs directly from Power BI Service. No gateway, no intermediate database.

How it works

Exact Online exposes OData/REST endpoints per division (administration). The basic flow:

1. Register an app in Exact Online (OAuth2 client ID/secret).
2. Implement OAuth2 in Power Query to get an access token.
3. Use that token to call Exact Online endpoints.
4. Publish the dataset; refresh in the service.

Power BI supports OAuth2 for web APIs via Power Query, and the Power BI Service can use stored OAuth2 credentials for scheduled refresh when the source is configured as a Web data source with OAuth2 authentication.

Important constraints

• Authentication: Exact Online uses OAuth2 with refresh tokens. You must implement token refresh correctly in Power Query, or your scheduled refresh will break when the access token expires.
• Rate limits: Exact Online enforces API limits. Large pulls (e.g. all invoices for multiple years and divisions) can hit throttling, causing refresh failures.
• Pagination: Most endpoints are paged. You must loop pages correctly or you’ll silently miss data.

Example: Power Query calling Exact Online

Below is a simplified pattern that:

• Calls a function GetExactAccessToken() (you implement this once)
• Uses the token to pull sales invoices for one division
• Handles basic pagination

let
    // Configuration
    BaseUrl       = "https://start.exactonline.nl/api/v1",
    Division      = "12345",  // use your division ID
    ResourcePath  = "/salesinvoice/SalesInvoices",

    GetExactAccessToken = () as text =>
        let
            // You implement the OAuth2 flow here.
            // The function must return a valid bearer token as text.
            // Common pattern: call a token endpoint using client ID/secret
            // and refresh token stored in parameters.
            token = ""  // placeholder: replace with your own token acquisition logic
        in
            token,

    GetPage = (skip as number) as table =>
        let
            Token      = GetExactAccessToken(),
            Url        = BaseUrl & "/" & Division & ResourcePath & "?$skip=" & Text.From(skip),
            Source     = Json.Document(
                            Web.Contents(
                                Url,
                                [
                                    Headers = [
                                        Authorization = "Bearer " & Token,
                                        Accept        = "application/json"
                                    ]
                                ]
                            )
                         ),
            Value      = if Record.HasFields(Source, "value") then Source[value] else null,
            TableValue = if Value = null then #table({}, {}) else Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
        in
            TableValue,

    GetAllPages = () as table =>
        let
            PageSize   = 60,  // Exact Online default page size for many endpoints
            FirstPage  = GetPage(0),
            Generate   = List.Generate(
                            () => [Page = FirstPage, Skip = 0],
                            each Table.RowCount([Page]) > 0,
                            each [
                                Skip = [Skip] + PageSize,
                                Page = GetPage([Skip])
                            ],
                            each [Page]
                         ),
            Combined   = Table.Combine(Generate)
        in
            Combined
in
    GetAllPages()

Key points:

• The example is a structural pattern only; you must implement GetExactAccessToken using Exact Online’s OAuth2 token endpoint, client credentials, and refresh token handling.
• In practice, you configure the Web data source in Power BI Service with OAuth2 so the service can manage the credential; the custom token logic in M must be compatible with Exact Online’s flow.

Pros

• No gateway required: Power BI Service calls the Exact Online cloud API directly.
• Works with Power BI Pro: No Premium-specific features.
• End-to-end in Power BI: BI team controls schema, transformations, and refresh.

Cons

• OAuth2 complexity: Implementing and maintaining token refresh in Power Query is non-trivial.
• Refresh reliability: API throttling or token issues directly break your dataset refresh.
• Performance: Large datasets can be slow; Exact Online is not optimised as a bulk export API.

Fit for the scenario

For our finance & operations report:

• Works if you keep data volumes moderate (e.g. recent years, limited divisions).
• Works with Pro, avoids the gateway.
• Risk: finance will see refresh failures when API limits or token issues hit, and BI owns the fix.

Method 2: Exact Online → SQL Database → Power BI (Gateway Required for On-Prem)

Here you pull Exact Online data into a SQL database first (e.g. Azure SQL Database or SQL Server), then connect Power BI to that database.

How it works

1. Use an ETL process (Azure Data Factory, Logic Apps, custom script, or a third-party sync tool) to:
   • Call Exact Online APIs
   • Load data into tables in a database (staging + cleansed schema)
2. In Power BI:
   • Use the built-in SQL connector (Import or DirectQuery)
3. Configure scheduled refresh:
   • Azure SQL Database: no gateway needed; Power BI Service connects directly.
   • On-prem SQL Server: requires on-premises data gateway.

Important constraints

• Gateway dependence: Any on-prem database needs a gateway for refresh. That’s where many teams feel the “gateway pain”: installation, updates, network rules, and ownership.
• Licensing: This pattern works with Pro and Premium. No Premium requirement by default.
• Responsibility split: IT or data engineering usually owns the ETL and database; BI owns the reports.

Example: Power BI connecting to Azure SQL

Assume your ETL has created a table dbo.FactSalesInvoice in Azure SQL.

SELECT
    InvoiceID,
    Division,
    InvoiceDate,
    DebtorID,
    AmountDC,
    AmountFC,
    Status
FROM dbo.FactSalesInvoice;

In Power BI Desktop:

1. Get Data → Azure → Azure SQL Database.
2. Enter server and database.
3. Use Import mode for finance reports (better DAX performance).
4. Publish to a Pro workspace.
5. In Power BI Service, configure credentials (SQL auth, Azure AD, or managed identity depending on your setup).

For Azure SQL, scheduled refresh runs without a gateway as long as the service can reach the database.

Pros

• Stable refresh: Database connectors are mature; token handling and API throttling are handled upstream in ETL.
• Performance: Import mode from SQL is usually faster than hitting REST APIs directly.
• Model control: You can design a clean star schema from Exact Online’s transactional layout.

Cons

• Setup overhead: You need ETL tooling and someone to build and maintain the pipeline.
• Gateway if on-prem: If you choose on-prem SQL, the gateway is mandatory for cloud refresh.
• Latency: Data freshness depends on ETL schedule plus dataset refresh.

Fit for the scenario

For our finance & operations report:

• If you use Azure SQL, this is a strong option: no gateway, solid refresh, good performance, Pro-friendly.
• If you use on-prem SQL, you must accept gateway management. That’s the trade-off: more stability vs more infra.

Method 3: Third-Party Connector / Integration Platform

Several integration platforms and vendors provide Exact Online → Power BI connectivity, usually via:

• Prebuilt Exact Online connectors
• Scheduled sync into a data store (their own or yours)
• Sometimes a direct Power BI connector or template

How it works

Typical pattern:

1. Configure Exact Online connection in the vendor portal.
2. Choose objects (e.g. GL accounts, transactions, invoices).
3. Map to a destination:
   • Vendor-hosted storage with a Power BI connector
   • Your Azure SQL or data lake
4. In Power BI, use:
   • A standard connector (SQL, OData, etc.), or
   • A custom connector supplied by the vendor

Power BI itself doesn’t care that the source is “Exact Online” at this point; it just sees a supported connector.

Important constraints

• Support model: You depend on the vendor for schema updates when Exact Online changes its API.
• Connector type: If the vendor uses a custom connector, you may need a gateway depending on how it’s implemented.
• Licensing: Some vendors target larger customers and assume Premium; others work fine with Pro.

Pros

• Reduced complexity: OAuth2, pagination, and API quirks are handled by the vendor.
• Time to value: You can often get a working dataset faster than building your own ETL.
• Maintenance: Schema drift and API changes are someone else’s problem.

Cons

• Cost: You add a subscription or usage-based fee.
• Lock-in: Data model and refresh options may be constrained by the platform.
• Transparency: Debugging refresh issues can be harder if you don’t see the underlying API calls.

Fit for the scenario

For the finance & operations report:

• Good fit if the BI team has limited engineering capacity and is fine with a vendor dependency.
• Check carefully:
  • Does the vendor deliver data into a standard storage you can query without a gateway (e.g. Azure SQL)?
  • Or does their connector require a gateway and possibly Premium features?

Method 4: Custom Connector with On-Premises Gateway

Here you build a Power Query custom connector (.mez) for Exact Online and deploy it with the on-premises data gateway. This is the “engineering-heavy” option.

How it works

1. Develop a custom connector using the Power Query SDK:
   • Implement OAuth2 for Exact Online
   • Wrap endpoints into functions (e.g. ExactOnline.SalesInvoices())
2. Install the on-premises data gateway (standard mode).
3. Deploy the connector to the gateway (place .mez in the gateway’s custom connectors folder).
4. In Power BI Desktop, use the connector to build your model.
5. Publish to the service; configure gateway mapping and credentials.

The gateway hosts the connector and handles all calls to Exact Online during refresh.

Important constraints

• Gateway is mandatory: Custom connectors are not supported natively in the Power BI Service. They must run via the on-premises gateway.
• Connector deployment: Every gateway that needs to use the connector must have the .mez file deployed and kept in sync.
• Licensing: Works with Pro and Premium; no Premium requirement by default. The pain is operational, not licensing.

Example: Connector usage pattern in Power Query

Inside Power BI Desktop, the connector might expose a function like this:

let
    Source        = ExactOnline.Contents(),
    SalesInvoices = Source{[Name = "SalesInvoices"]}[Data]
in
    SalesInvoices

The connector implementation (outside Power BI Desktop) handles:

• OAuth2 token flow
• Pagination and rate limiting
• Schema projection

From the BI developer’s perspective, this is much cleaner than raw REST calls.

Pros

• Clean UX for report authors: They see a named “Exact Online” connector instead of hand-rolled Power Query.
• Centralised logic: OAuth2 and API behaviour live in one place, not copied across .pbix files.
• Gateway controls execution: IT can monitor and manage refresh centrally.

Cons

• Gateway operational overhead: Installation, updates, network configuration, and monitoring.
• Engineering effort: Building and maintaining a custom connector requires Power Query and OAuth2 expertise.
• Deployment friction: Updating connectors across gateways is another operational process.

Fit for the scenario

For the finance & operations report:

• Strong fit if you have:
  • Multiple reports needing Exact Online
  • An IT team comfortable with gateway management
  • Developers who can build and maintain the connector
• Not ideal for small teams without dedicated engineering/IT capacity.

Premium and Gateway: Where the Real Pain Comes From

From the scenario and the four methods, the pain points usually cluster around:

• Premium requirement misunderstanding:

  • None of the four methods inherently require Premium.
  • Premium becomes relevant when you need features like large models, higher refresh limits, or advanced governance.
  • For typical SME finance reports, Power BI Pro is often sufficient if you design the integration carefully.

• Gateway friction:

  • You only need a gateway when the data source is on-premises or when using custom connectors.
  • Azure SQL and standard cloud APIs (Method 1 and Azure-based Method 2) avoid the gateway.
  • Once you choose on-prem or custom connectors, gateway operations become part of your life: updates, high availability, and monitoring.

The core decision is less about “Premium vs Pro” and more about “direct cloud vs gateway + custom/SQL”.

Choosing the Right Method for Your Team

For the finance & operations scenario, a practical decision flow:

1. Do you have Azure SQL or are you willing to set it up?

   • Yes: Method 2 (Exact → Azure SQL → Power BI) is usually the most robust.
   • No: Consider Method 1 (direct API) or a vendor (Method 3).

2. Do you have engineering capacity for ETL or custom connectors?

   • Yes, and IT can own a gateway: Method 4 (custom connector + gateway) scales well across many reports.
   • Yes, but want minimal infra: Method 2 with Azure SQL keeps infra manageable.
   • No: Method 3 (third-party) may be worth the cost.

3. Data volume and complexity:

   • High volume / many divisions / complex finance logic: Prefer Method 2 or 4; databases handle volume better.
   • Moderate volume / few divisions / simple reporting: Method 1 can be enough if you manage API limits.

For most Dutch SMEs in the described scenario, a realistic path is:

• Start with Method 2 using Azure SQL:
  • No gateway
  • Pro-compatible
  • Stable refresh
• Move to Method 4 if you later need more abstraction or standardisation across teams.

Practical Takeaway

If you’re stuck between “we don’t have Premium” and “IT doesn’t want another gateway”, focus on cloud-native patterns: either call Exact Online directly from Power BI with a well-implemented OAuth2 flow, or land the data in Azure SQL and connect from there. Both keep you in Power BI Pro territory and avoid turning a finance report into an infrastructure project.