Skip to main content

BI & Spreadsheets

Power BI + QuickBooks Desktop

Connect Power BI Desktop to QuickBooks Desktop through Syntra’s bundled ODBC driver. Import invoices, customers, and other supported tables to build interactive accounting dashboards.

Quick Start

  1. Install and authorize Syntra. Follow the installation guide on the Windows machine running QuickBooks. The installer includes both ODBC drivers and creates the Syntra QuickBooks System DSN.
  2. Open Power BI Desktop. Choose Get Data → More → Other → ODBC.
  3. Choose the DSN. Select Syntra QuickBooks. For this walkthrough, Power BI and Syntra run on the same machine.
  4. Authenticate. Select Database and enter your configured Syntra credentials. The default username is qbconnect; use the password from your config.toml.
  5. Import tables. In Navigator, select invoices, customers, or item_inventories. Choose Load to import, or Transform Data to prepare the data in Power Query.

Import and data freshness

Import

  • Data copied into Power BI model
  • Faster dashboard interactions
  • Refresh manually or on schedule
  • Best for historical reporting

QuickBooks data freshness

  • Model refresh reads data from Syntra
  • Cached data reflects the last Syntra sync
  • Schedule model refresh after the required sync
  • Enterprise direct reads need the Pro plan and configuration

The generic ODBC connector supports Import; it does not offer DirectQuery in this walkthrough. See Microsoft’s ODBC connector capabilities.

Custom SQL Query

In the connection dialog, expand Advanced options and paste a SQL statement. This is useful for pre-joining tables or filtering data before it reaches Power BI:

SELECT
  i.txn_date,
  i.ref_number       AS InvoiceNumber,
  c.full_name        AS CustomerName,
  c.email,
  i.balance_remaining,
  i.is_paid,
  EXTRACT(MONTH FROM i.txn_date) AS TxnMonth,
  EXTRACT(YEAR FROM i.txn_date)  AS TxnYear
FROM invoices i
JOIN customers c ON i.customer_ref_list_id = c.list_id
WHERE i.txn_date >= CURRENT_DATE - INTERVAL '1 year';

Tips for Power BI

  • Use Import mode for most dashboards. It's faster and supports the full DAX engine.
  • Set up scheduled refresh in Power BI Service through an on-premises data gateway in standard mode. Install the Syntra driver on the gateway host and configure a matching System DSN and credentials.
  • Create relationships between tables (e.g., invoices.customer_ref_list_id → customers.list_id) in the Model view for drag-and-drop reporting.

Complete setup and gateway instructions: Power BI connection docs →

Build Power BI dashboards from QuickBooks

Try Syntra’s QuickBooks Desktop ODBC driver free for 30 days.

Download Free Trial