DuckDB Power Query Connector by MotherDuck

This is the Power Query Custom Connector for DuckDB. Use this to connect to a DuckDB database in memory, from a local file or on MotherDuck with Power BI and Excel.

1. Installing
2. How to use with Power BI
3. Turning on UTF-8 support in the Language & Region settings
4. [Experimental] Power BI Service

Installing

1. Download the latest DuckDB ODBC driver from the DuckDB Power Query Connector GitHub Releases for Windows:

   • duckdb_odbc-windows-amd64.zip

2. Extract the .zip archive into a permanent location, such as C:\Program Files\duckdb_odbc, and install the latest DuckDB driver by running odbc_install.exe.

3. Check that the correct version was installed. To do this, open the Registry Editor by running regedit in the command prompt or Run dialog. Browse to the HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\DuckDB Driver entry and check that the Driver field contains the version you installed. If not, delete the DuckDB Driver registry key and rerun the installer.

4. Open Power BI, go to File -> Options and settings -> Options -> Security -> Data Extensions. Enable "Allow any extensions to load without validation or warning".

5. Download the latest version of the DuckDB Power Query extension:

   • duckdb-power-query-connector.mez

6. Create this folder if it does not yet exist: [Documents]\Power BI Desktop\Custom Connectors.

7. Move or copy the duckdb-power-query-connector.mez file into [Documents]\Power BI Desktop\Custom Connectors. Note that if this location does not work, you may need to place this in your OneDrive Documents folder.

How to use with Power BI

1. Click on Get Data -> More...
2. Search for DuckDB and click "Connect"
3. Enter your database location. This can be a local file path (e.g. ~\my_database.db) or a MotherDuck database location (e.g. md:my_database). (Optional) enter your MotherDuck token. If you want to access the database in read_only mode, you can set it to true. Click "OK".
4. Click "Connect".
5. Select the table(s) you want to import. Click "Load".
6. You can now query your data and create visualizations!

Turning on UTF-8 support in the Language & Region settings

UTF-8 is currently not supported in the DuckDB ODBC driver. As a workaround, you can turn on UTF-8 decoding in Windows. Note that this may change behavior for other applications, so please use with caution.

1. Open start menu and type "Language settings". Open the "Language & region" settings
2. Click on "Administrative language settings"
3. Click on "Change system locale"
4. Check the "Beta: Use Unicode UTF-8 for worldwide language support" and click OK

5. This prompts Windows to restart.
6. Next, open Power BI, click on "Options and settings" -> "Options" -> "Data Load" and click the "Clear cache" button.

Now, you should be able to load your UTF-8 encoded database with Power BI directly:

[Experimental] Power BI Service

To use the Power BI Service with DuckDB, you can use the On-Premises Data Gateway application. Note that data will only be available when the gateway is on and connected to the internet. This will enable features like automated refresh, and let you share your PowerBI dashboards online.

1. Follow these instructions to install the on-premises data gateway: Download and install a standard gateway.
2. Open Services, and find the On-premises data gateway service. Double click to open the Properties dialog.

Go to the "Log On" tab and click "Local System Account" and check "Allow service to interact with Desktop".

3. Click on "Restart" to restart the service. Open the "Connectors" tab and wait until "DuckDB" shows up in Custom Connectors. If it does not, please follow the Custom Connector installation instructions above.

4. Go to Power BI Service and click on the "Settings" icon in the top right. Navigate to the "Manage connections and gateways" page.

5. Click on the dots menu next to the gateway you configured in step 1. and click on "Settings"

6. Click the "Allow user's cloud data sources to refresh through this gateway cluster" and "Allow user's custom data connectors to refresh through this gateway cluster."

7. On the "Manage connections and gateways" page, click on the "Connections" tab and click "+ New"

8. Enter your connection details. You can enter any local file name here, or use MotherDuck. If you do, make sure to enter a valid MotherDuck token.

   If you are using MotherDuck, make sure that the MotherDuck extension is installed under C:\WINDOWS\system32\config\systemprofile\.duckdb\extensions\. To do so, you can download the DuckDB CLI client for Windows, and run INSTALL motherduck. Then, you can copy over the extension files via PowerShell:

   cp -R '~\.duckdb\extensions\v1.2.0\' "C:\WINDOWS\system32\config\systemprofile\.duckdb\extensions\"
   

Enable the "Skip test connection" check box.

Click "Create".

9. Now you can create a Power BI report and publish it to Power BI Service. On your report, click "Publish".

10. Done! Now you can click on the URL in the Publishing to Power BI dialog to access the online report.