Skip to main content

Power BI custom connector (legacy)

Power BI is an interactive data visualization product developed by Microsoft. You can connect Power BI to MotherDuck through the built-in PostgreSQL database connector using MotherDuck's Postgres endpoint.

Legacy

The custom connector is a legacy approach. Use the Postgres endpoint setup instead for a simpler connection that doesn't require installing drivers or custom extensions.

The open-source DuckDB Power Query Connector lets you connect Power BI to DuckDB and MotherDuck using the DuckDB ODBC driver.

Installing​

  1. Download the latest DuckDB ODBC driver for Windows (x86_64/AMD64). See the releases page for other versions and architectures. For more information about the Windows ODBC Driver, see the DuckDB Docs page on DuckDB ODBC API on Windows.
  2. Extract the .zip archive. Run odbc_install.exe. If Windows displays a security warning, click "More information" then "Run Anyway".
  3. Optionally, verify the installation in the Registry Editor:
    • Open Registry Editor by running regedit
    • Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\DuckDB
    • Confirm the Driver field shows your installed version
    • If incorrect, delete the DuckDB registry key and reinstall
  4. Configure Power BI security settings to allow loading of custom extensions:
    • Go to File -> Options and settings -> Options -> Security -> Data Extensions
    • Enable "Allow any extensions to load without validation or warning"
    • Dialog window showing Power BI Options to Security to Data Extensions
  5. Download the latest version of the DuckDB Power Query extension:
  6. Create the Custom Connectors directory if it does not yet exist:
    • Navigate to [Documents]\Power BI Desktop\Custom Connectors
    • Create this folder, if it doesn't exist
    • Note: If this location does not work you may need to place this in your OneDrive Documents folder instead
  7. Copy the duckdb-power-query-connector.mez file into the Custom Connectors folder
  8. Restart Power BI Desktop

How to use with Power BI​

  1. In Power BI Desktop, click "Get Data" -> "More..."
    Power BI Get Data dialog
  2. Search for "DuckDB" in the connector search box and select the DuckDB connector
    Power BI connector search showing the DuckDB connector
  3. For MotherDuck connections, you'll need to provide:
    • Database Location: Use the md: prefix followed by your database name (for example, md:my_database). This can also be a local file path (for example, ~\my_database.db) or an in-memory database (:memory:).
    • MotherDuck Token: Get your token from MotherDuck's token page. For local DuckDB connections: Enter "localtoken" to enable the connection.
      Power BI DuckDB connection dialog
    • Read Only (Optional): Set to true if you only need read access.
    • Saas_mode (Optional): Set to true to disable DuckDB extensions.
    • Attach_mode (Optional): Set to single to scope the connection to one database (recommended for BI-tool catalog browsers, which can be confused by multiple attached databases). Leave blank to use the default workspace mode and see all databases in your workspace. See Attach modes.
  4. Click "OK".
  5. Click "Connect".
    Power BI connection dialog for MotherDuck through the DuckDB connector
  6. Select the table(s) you want to import. Click "Load".
    Power BI Navigator dialog previewing and selecting tables
  7. You can query your data and create visualizations.
    Power BI report built from MotherDuck data
  8. After connecting, you can:
    • Browse and select tables from your MotherDuck or DuckDB database
    • Use "Transform Data" to modify your queries before loading
    • Write custom SQL queries using the "Advanced Editor"
    • Import multiple tables in one go
  9. Power BI maintains the connection to your MotherDuck or DuckDB database, letting you:
    • Refresh data automatically or on-demand
    • Create relationships between tables
    • Build visualizations and dashboards
    • Share reports with other users (requires proper gateway setup)

Use custom data connectors with an on-premises data gateway​

You can use custom data connectors with an on-premises data gateway to connect to data sources that are not supported by default. To do this, you need to install the on-premises data gateway and configure it to use the custom data connector. For more information, see Use custom data connectors with an on-premises data gateway in Power BI.

There are some limitations with using a custom connector with an on-premises data gateway:

  • The folder you create must be accessible to the background gateway service. Folders under user Windows folders or system folders typically aren't accessible. The on-premises data gateway app shows a message if the folder isn't accessible. This limitation doesn't apply to the on-premises data gateway (personal mode).
  • If your custom connector is on a network drive, include the fully qualified path in the on-premises data gateway app.
  • You can only use one custom connector data source when working in DirectQuery mode. Multiple custom connector data sources don't work with DirectQuery.

Additional information​

Troubleshooting​

Missing VCRUNTIME140.dll​

If you receive an error about missing VCRUNTIME140.dll, you need to install the Microsoft Visual C++ Redistributable. You can download it from Microsoft's download page.

Visual C++ and ODBC issues​

note

These steps are particularly relevant for Windows Server environments, especially for Windows Server 2019, but may also help resolve issues on other Windows versions.

If you encounter issues with ODBC connectivity or receive errors related to Visual C++ libraries, try these troubleshooting steps:

  1. Reinstall the Microsoft Visual C++ Redistributable:

    • Download the latest version from Microsoft's official website for your architecture
    • Run the installer with administrator privileges
    • Restart your computer after installation
    • Try connecting to MotherDuck again

  2. If you're still experiencing issues, you can use the ODBC Test tool to diagnose the connection:

    • Open the ODBC Test tool (typically available in Windows SDK)
    • Look for a dropdown menu labeled "hstmt 1: ..."
    • Select this option to run test queries
    • If queries work in the ODBC Test tool but not in Power BI, this indicates a Power BI-specific configuration issue

If you continue to experience problems after trying these steps:

  • Verify that your MotherDuck token is valid and hasn't expired
  • Check that your network allows connections to MotherDuck's services
  • Confirm you have the latest version of the DuckDB Power Query Connector installed

If you're still experiencing issues, reach out to us at support@motherduck.com and we'll be happy to help you troubleshoot the issue.