Connecting Power BI to Your ERP or CRM: Native Connector, API or Dataflow

"Our business runs on [Business Central / Salesforce / Sage Intacct / ServiceTitan / Zoho]. How do we get that into Power BI?"

We hear a version of this in almost every project, and it is the single most requested skill in the Power BI market right now — the jobs are full of "connect Power BI to our ERP", "Power BI integration with ServiceTitan", "customer-facing report on Snowflake". The good news: it is a solved problem. The bad news: there are four different ways to solve it, and picking the wrong one is what turns a two-day job into a report that breaks every Monday.

This guide walks the four connection paths in the order you should try them, what each one looks like in Power Query, which credentials survive a scheduled refresh, and how the model changes once the ERP data is in.

Step 0: three questions before you open Power BI

Ask the person who owns the system:

Where does it run? Cloud SaaS, or a server inside the company network?
What does it expose? A certified Power BI connector, a database, or only a REST API?
How much moves per day? A few thousand rows, or millions?

Those three answers pick the path. Everything else in this article is the detail behind that table:

The system...	Path	Typical examples
is cloud and has a certified connector	Native connector	Business Central, Salesforce, Dynamics 365, Snowflake
has a SQL/Oracle database on-premises	Database + gateway	older ERPs, plant-floor MES
only offers a REST API	Power Query `Web.Contents` or a custom connector	ServiceTitan, Zoho, Sage Intacct, niche SaaS
is one of several sources, or everyone needs the same data	Dataflow (Power Query Online)	ERP + CRM + finance joined once

Path 1 — The native connector

Start here whenever it exists. Power BI handles authentication, paging and the API's quirks for you, and the query folds, so filters and column selection are pushed down to the source instead of being applied after the download.

Business Central is the textbook example. In Power BI Desktop: Get Data → Online Services → Dynamics 365 Business Central, then pick the environment, the company and the API route — use v2.0, not the legacy OData web services, which Microsoft has deprecated. Three advanced options matter in real projects: ODataMaxPageSize (default 5,000, maximum 20,000), ODataTimeout (default 8 minutes) and UseReadOnlyReplica, which sends the whole query to a read-only replica and keeps production responsive. The connector authenticates with Microsoft Entra ID as an organizational account, so the same login works in Desktop and in the service — no gateway.

A trap people hit on day two: an OData feed returns one company per call. Write a Power Query function that takes a company name and returns its tables, invoke it over a list of companies, and append the results — never duplicate the query by hand.

Snowflake follows the same shape but with a different credential story. It has a native connector and a proper star-schema source, so it usually becomes the "single source" part of the model while the CRM stays on the API side.

Path 2 — Database connector plus on-premises data gateway

Many ERPs — and almost every plant-floor system — sit on a database inside the network. The Power BI service cannot reach it, so you install the on-premises data gateway on a machine that can. It only makes outbound connections, so nothing has to be opened in the firewall.

We set it up the same way every time: standard mode (never personal mode), on a dedicated always-on server close to the database, using a read-only account pointed at reporting views rather than the ERP's own application login. And we keep it on the current release, because Microsoft only supports the last six gateway versions.

Path 3 — Only a REST API: Power Query, or a custom connector

Plenty of systems the market asks for — ServiceTitan, Zoho, Sage Intacct, most niche SaaS — ship no certified Power BI connector. For those, the honest answer is: it depends on how many people will reuse it.

If it is one or two reports, use Power Query's Web.Contents directly:

let
    BaseUrl = "https://api.example.com/v1/",
    GetPage = (url as text, token as text) as record =>
        Json.Document(
            Web.Contents(
                BaseUrl,
                [ RelativePath = url,
                  Headers = [ Authorization = "Bearer " & token, Accept = "application/json" ] ]
            )
        ),
    Pages = List.Generate(
        () => [ page = 1, data = GetPage("jobs?page=1", Token) ],
        each List.Count([data][results]) > 0,
        each [ page = [page] + 1, data = GetPage("jobs?page=" & Text.From([page] + 1), Token) ],
        each [data][results]
    ),
    Jobs = Table.FromList(List.Combine(Pages), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expanded = Table.ExpandRecordColumn(Jobs, "Column1", {"id", "customerId", "total", "completedOn"})
in
    Expanded

Three rules make this survive in production:

Keep the base URL static. Only the path, query string and headers may vary, or the service will refuse to schedule the refresh. That is exactly why the function above splits BaseUrl from RelativePath.
Put the API version in one Power Query parameter. Meta, Salesforce and ServiceTitan all retire API versions on their own calendar; the fix should be one line, not twenty.
Paginate properly and pull only the window you need. ServiceTitan and Zoho both throttle per account, and the classic failure is a first full sync that succeeds followed by a scheduled refresh that eats the daily quota and leaves the dataset silently stale.

If the same API feeds several reports — or the client's own IT team wants to build on it — invest in a custom connector (the Power Query SDK) or a certified third-party connector. You package the authentication and the pagination once, and every report that uses the API reuses it. That is a day or two of extra work that pays for itself the second time the same source is needed.

Path 4 — Dataflows: query once, serve everyone

When three reports need the same ERP tables, "each query hits the ERP separately" is the wrong design. A dataflow (Power Query Online, in a Fabric or Premium workspace) runs the extraction and the cleaning once, stores the result in OneLake-based storage, and every semantic model reads the clean table instead of the source system.

What that buys you:

The ERP sees one refresh load instead of five.
The credentials live in the dataflow, in one place — not duplicated across every .pbix.
Two dataset owners, four analysts and the finance team all get the same definition of Customer and Net Revenue.
The heavy transformation happens off the semantic model, so refresh on the report side stays fast.

The rule we use: one extraction per system, many reports per extraction as soon as a second report appears.

Modelling: the ERP is not the model

Once the data is in, do not point the visuals at the raw ERP tables. The ERP schema is built for transactions — hundreds of columns, keys you will never use, and lookups chained three levels deep. What works is the same shape every time:

A star schema. FactSales, FactInventory, FactServiceOrders as facts; DimCustomer, DimItem, DimDate, DimCompany as dimensions.
One date table, marked as a date table, related to every fact. ERPs store posting dates, document dates, due dates and modified dates in the same table, and "which date" is always the first argument in the meeting.
The dimension keys as they arrive — customer number, item number, company code. Do not invent surrogate keys; the ERP's are stable and people search by them.
Measures in a dedicated table, currency and ratio measures written once so a new source is a new query, not a rewrite.

Revenue = SUM ( FactSales[Amount] )
Service Orders = COUNTROWS ( FactServiceOrders )
Backlog Value = CALCULATE ( SUM ( FactServiceOrders[Value] ), FactServiceOrders[Status] = "Open" )
Gross Margin % = DIVIDE ( [Gross Profit], [Revenue] )

DIVIDE is not cosmetic. Every month has customers with costs and no revenue, and a plain / turns that into Infinity and breaks the axis.

Row-level security: the reason these projects exist

Half of the "customer-facing report" jobs in this space are really RLS jobs. The report goes to clients, branches or technicians, and each one must see only their own rows. The pattern that works on ERP data:

A security table (User → Company / Customer / Region) with one row per person per scope, maintained by someone in the business, not hard-coded in DAX.
A relationship from that table to the dimension, with security filter propagation in one direction only.
A role with a LOOKUPVALUE or TREATAS filter in DAX — and a test user in every role, because RLS bugs are invisible to the admin account that built them.

RLS also decides the connector: a live connection to the ERP's own security does not exist, so the filtering has to happen in the semantic model.

The traps that break these integrations

Multi-company ERPs. One connection, one company. Loop the function and append.
Query folding lost early. Right-click the last step of the query and check whether View Native Query is available. If it is greyed out, move the filter earlier — before the step that cannot fold.
Timestamps in local time, refresh in UTC. The service refreshes on UTC while the ERP writes local time. Convert once, in the first Power Query step, and never again.
Deleted rows never arrive. A nightly full load never tells you a customer was removed; the report keeps counting them. Use incremental refresh with a lookback window larger than the ERP's edit window, or a LastModified filter.
Dynamic URLs and broken credentials in the service. Both work in Desktop and fail after publishing. Keep URLs static and set the credentials on the data source in the service — and remember the gateway caches them, so a password change can take a while to take effect.
Paginated reports are a different tool. "Paginated Power BI reports for data drawn from Business Central" is not a dashboard job — it is Power BI Report Builder (RDL), published to a workspace, and it is the right answer when the client needs an invoice or a statement, not an interactive visual.
Refreshing the ERP at 8:00. Read from a replica, or schedule the load off-peak.

What we build first

Four visuals answer 90% of what a client actually wants from an ERP-connected report, and they all read from the same star schema:

Revenue and margin over time, so the numbers can be read against the business calendar.
Top customers or items by revenue, with margin next to it — the first question every sales director asks.
Order backlog and open service jobs, the operational view that the ERP itself makes painful.
A source-level KPI block: rows loaded per system, last refresh time, failed refreshes — the panel that stops the "is this number right?" e-mail.

Connecting the ERP is the easy half. Keeping it connected — correct credentials, folding queries, one extraction serving every report — is the half that decides whether the dashboard is still trusted in March.

If your business data lives in an ERP or a CRM and the reports never match, this is exactly the model we build — see our Power BI consulting page.