Skip to main content

Power BI with DuckDB and MotherDuck

Power BI is an interactive data visualization product developed by Microsoft. MotherDuck has built an open-source DuckDB Power Query Connector that you can use to connecto Power BI to DuckDB and MotherDuck.

Installing​

  1. Download the latest DuckDB ODBC driver from the DuckDB Power Query Connector GitHub Releases for Windows:
  2. Extract the .zip archive into a permanent location, such as C:\Program Files\duckdb_odbc. 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 -> Security -> 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..."
  2. Search for "DuckDB" in the connector search box and select the DuckDB connector Find DuckDB connector
  3. For MotherDuck connections, you'll need to provide:
    • Database Location: Use the md: prefix followed by your database name (e.g., md:my_database). This can also be a local file path (e.g., ~\my_database.db)
    • MotherDuck Token: Get your token from MotherDuck's token page
    • Read Only (Optional): Set to true if you only need read access Connect to your MotherDuck database
  4. Click "OK".
  5. Click "Connect". Connect dialog
  6. Select the table(s) you want to import. Click "Load". Navigator dialog to preview and select your table(s)
  7. You can now query your data and create visualizations! Power BI example usage
  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 will maintain the connection to your MotherDuck or DuckDB database, allowing you to:
    • 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.

It should be noted that there are some limitations with using a custom connector with an on-premise data gateway:

  • Make sure the folder you create is accessible to the background gateway service. Typically, folders under your users' Windows folders or system folders 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.