maiadegraaf
commented
2 months ago Hi, thanks for the issue. Seems like something we would like to have in our documentation. Could you turn it into a PR? You can add it as a subsection on the configuration page. For reference here are our contributor guidelines.
I have an existing DuckDB database which I would like to using excel PowerPivot If you have an existing DuckDB database that you want to use with Excel PowerPivot, you can follow these steps to connect to the database and import tables into your PowerPivot model: Ensure DuckDB ODBC Driver is Installed: Make sure you have the DuckDB ODBC driver installed on your system. If not, download and install it from the DuckDB website (https://duckdb.org/docs/api/odbc.html). Open Excel and Navigate to PowerPivot: Open Microsoft Excel and navigate to the PowerPivot window (Data -> Manage Data Model). Connect to DuckDB Database: In the PowerPivot window, go to "Home" -> "Get External Data" -> "Other Sources" -> "ODBC". In the "From ODBC" dialog box, select "DuckDB" as the data source and click "Next". Enter the connection details for your existing DuckDB database: Data Source Name (DSN): You can provide a name for the data source connection. Database: Specify the path to your existing DuckDB database file. Click "Next" to proceed. Select Tables to Import: In the "Navigator" dialog box, you will see a list of tables available in your DuckDB database. Select the tables you want to import into your PowerPivot model by checking the corresponding checkboxes. You can preview the data in each table by clicking on the table name. Click "Load" to import the selected tables into PowerPivot. Build and Customize the Data Model: Once the tables are imported, you can create relationships between them based on common columns to establish a logical data model. You can also create calculated columns, measures, and hierarchies using DAX (Data Analysis Expressions) to enhance your data model and perform calculations. Use the Data Model with DAX: After building the data model, you can create PivotTables, PivotCharts, and other visualizations in Excel using the imported data from DuckDB. Use DAX functions and expressions to perform calculations, aggregations, and data analysis on the imported data. Here's an example of a DAX measure that calculates the average price of products:
Average Price = AVERAGE(Products[Price])
This measure calculates the average of the "Price" column from the "Products" table. Remember to save your Excel workbook to persist the PowerPivot model and any changes you make to it. By following these steps, you should be able to connect Excel PowerPivot to your existing DuckDB database, import tables, and build a data model for analysis and reporting purposes using DAX. (edited)
– https://community.fabric.microsoft.com/t5/Desktop/DuckDB-file-connection/td-p/3257101