BI & Spreadsheets

Power BI + QuickBooks Desktop

Connect Power BI Desktop to QuickBooks Desktop using the built-in PostgreSQL connector. Build interactive dashboards and reports with real-time accounting data.

Quick Start

  1. Open Power BI Desktop. Click Get Data → Database → PostgreSQL database.
  2. Enter the server. Server: localhost, Port: 5433. You can type it as localhost:5433.
  3. Enter the database. Database: quickbooks (or the name you configured in Syntra).
  4. Choose a mode. Select Import (snapshot loaded into Power BI) or DirectQuery (live queries on each interaction).
  5. Authenticate. Select Database tab. Username: syntra, Password: your config.toml password.
  6. Select tables. Use the Navigator to pick tables like invoices, customers, items, etc., then click Load.

Import vs DirectQuery

Import

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

DirectQuery

  • Queries run live against Syntra
  • Always shows current data
  • Slower for complex visuals
  • Best for real-time dashboards

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 >= '2024-01-01';

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 via a Personal Gateway with Syntra running on the same machine.
  • ✓ Create relationships between tables (e.g., invoices.customer_ref_list_id → customers.list_id) in the Model view for drag-and-drop reporting.

Full walkthrough with screenshots: Power BI integration docs →

Build Power BI dashboards from QuickBooks

Download Syntra ODBC and connect Power BI in under five minutes.

Download Free Trial