Connect Power BI to QuickBooks Desktop via ODBC

Syntra ODBC connects QuickBooks Desktop to reporting tools through SQL. See driver compatibility and licensing before setting up a connection.

Power BI Desktop connects to Syntra through the built-in ODBC data source. The Syntra installer registers the driver and a default DSN. A local Power BI Desktop connection does not require a gateway; scheduled refresh in the Power BI Service requires the gateway setup below.

Prerequisites

• Syntra installed and running (the default Syntra QuickBooks System DSN is present).
• Power BI Desktop (current version).
• The account running Power BI Desktop must be able to read the System DSN. If Power BI shows an empty DSN list, make sure the DSN exists under System DSN (not only User DSN) in odbcad32.

Connect

Step 1 — open the ODBC connector

1. Open Power BI Desktop.
2. On the Home ribbon, click Get data → More….
3. In the Get Data dialog, pick Other in the left pane, select ODBC, and click Connect.

Step 2 — pick the DSN

1. In Data source name (DSN), choose Syntra QuickBooks from the dropdown.
2. Leave the Advanced options section collapsed unless you need to pass a custom connection string; the DSN already has everything.
3. Click OK.

If the DSN isn’t listed, use Advanced options → Connection string (non-credential properties) and paste:

Driver={Syntra ODBC - QuickBooks ODBC};Server=127.0.0.1;Port=5433;Database=qbconnect;

Step 3 — authenticate

Power BI prompts for credentials the first time you connect:

1. Click Database on the left.
2. Enter the username and password from the [auth] section of config.toml (installer defaults are qbconnect / changeme — change them in production).
3. Click Connect.

Step 4 — pick tables

The Navigator window lists every QuickBooks entity (customers, invoices, item_inventories, bills, accounts, etc.). Tick the tables you need, then click Load to import or Transform data to stage them in Power Query first.

Import and data freshness

The generic Microsoft ODBC connector supports Import. It does not offer DirectQuery in this connection flow. Power BI loads rows into its model, and visuals use that imported snapshot until the next refresh.

A model refresh reads from Syntra, which normally serves its local cache. The data therefore reflects the last successful Syntra sync. Schedule model refresh after the required sync and keep the QuickBooks connection available for data fetches. The Pro plan can use Enterprise direct reads for supported tables when configured; this does not change Power BI’s Import mode.

Custom SQL

Rather than loading entire tables, you can write a SQL query that Syntra executes once and Power BI treats as a single result set:

1. In the ODBC data source dialog, expand Advanced options.
2. Paste the query into SQL statement (optional):

SELECT
    c.full_name                AS Customer,
    SUM(i.balance_remaining)   AS TotalOutstanding,
    COUNT(*)                   AS InvoiceCount
FROM invoices i
JOIN customers c ON i.customer_ref_list_id = c.list_id
WHERE i.is_paid = false
GROUP BY c.full_name
ORDER BY TotalOutstanding DESC

3. Click OK.

Scheduled refresh in the Power BI Service

To refresh after publishing to the Power BI Service:

1. Install the On-premises data gateway (standard mode) on the machine running Syntra (or any machine that can reach it on port 5433).
2. Configure an ODBC data source in the gateway pointing at the same DSN (Syntra QuickBooks) and the same credentials.
3. Publish your Power BI dataset and bind it to the gateway under Settings → Datasets → Gateway connection.
4. Set a refresh schedule on the dataset.

The gateway host must have the Syntra ODBC driver installed, a System DSN of the matching architecture, and network access to Syntra. On a separate gateway machine, set the DSN server to the Syntra host instead of localhost. Use the same DSN name and credentials in the published connection. Configure TLS for remote connections.

Tips

• Relationships. Power BI can auto-detect relationships using list_id ↔ *_ref_list_id columns. Review them in the Model view; Syntra exposes all foreign-key references as plain columns, so the auto-detector catches most joins but occasionally picks the wrong pair (especially for templates and terms). Edit relationships in the Model view when in doubt.
• Date tables. Build a dedicated date dimension in Power BI for time intelligence measures (YTD, MTD, YoY). QuickBooks’ own txn_date columns are transactional, not a continuous calendar.
• Import filters. Limit the transaction-table imports with a WHERE clause on txn_date (last 24 months, last fiscal year, etc.). Pulling the full history of a 10-year file slows both the initial import and every refresh.
• Custom fields. QuickBooks user-defined fields appear as custom_* columns. See Custom Fields.
• Numeric types. Money and quantities come through as Decimal and preserve precision end-to-end. Avoid the Double type for financial measures — use Fixed decimal number instead.