Export Excel Power Query Data to Access or a Database
Question details
Export Power Query results from Excel to Microsoft Access or another database, including datasets too large for an Excel worksheet.

- Product
- Microsoft Excel Power Query, Excel Data Model, and Microsoft Access
- Device & OS
- not provided
- Scenario
- A user wants to transfer Power Query results, potentially containing 2.8 million rows, into a database.
- Observed behavior
- Power Query does not directly export its results to Access, and Access cannot directly read the Excel Data Model.
Check that Excel supports the Data Model and that DAX Studio is installed. For large exports, check available disk space and the destination database's capacity; an Access database is limited to 2 GB.
Export the Excel Data Model with DAX Studio
Use this method when the Power Query result is too large for a worksheet or is already in the Excel Data Model.
DAX Studio can query the Excel Data Model and write its results to a file that Access or another database can import. The Data Model table name may differ from the Power Query name.
In Excel, open the Queries & Connections pane, right-click the query, and choose Load To. Select Only Create Connection and check Add this data to the Data Model, then click OK.
Save the workbook and keep it open. Launch DAX Studio and connect to the workbook's Power Pivot or Excel Data Model.
In DAX Studio, select File as the output destination. Enter EVALUATE 'Table Name', replacing Table Name with the Data Model table name, and click Run. Choose the output filename and a CSV or text format when prompted.
In Access, open the destination database and choose External Data > New Data Source > From File > Text File. Select the exported file and follow the import wizard to set delimiters, field types, and the destination table.

Import a Power Query worksheet into Access
Use this simpler option when the entire result fits on an Excel worksheet and within the Access database size limit.
Work with spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for working with compatible Office files. Open existing workbooks as you move between office suites; WPS does not fix Excel Data Model exports to Access.

Frequently Asked Questions
Can Access import directly from the Excel Data Model?
No. Export the Data Model table to CSV or text with a tool such as DAX Studio, then use Access's External Data > New Data Source > From File > Text File import wizard.
Can I load 2.8 million rows into an Excel worksheet before importing them?
No. A worksheet is limited to 1,048,576 rows, including headers. Load the query into the Data Model instead and export it with DAX Studio.
Will 2.8 million rows fit in an Access database?
It depends on the size and types of the fields. An Access database is limited to 2 GB, including objects and overhead. If the imported data exceeds that limit, use a database with greater capacity.
What DAX query exports an entire Data Model table?
In DAX Studio, enter EVALUATE 'Table Name', replacing Table Name with the name shown in the Data Model. Select File output before clicking Run.




