Data Import & Export

How to Create a Worksheet Table from an Excel Data Model

Algirdas JasaitisAlgirdas Jasaitis Nov 11, 2026 870 views

Question details

The user wants to combine and extract columns from related tables within an Excel Data Model into a single worksheet table using DAX, avoiding the performance issues of Power Query merges or exporting through MS Access.

How to Create a Worksheet Table from an Excel Data Model
Product
Excel
Device & OS
not provided
Scenario
Generating a combined table view on an Excel worksheet directly from the Data Model.
Observed behavior
The user needs a fast and efficient method to output related Data Model tables into a spreadsheet grid using DAX query integration.
Before you start

Ensure that your source tables are already loaded into the Excel Data Model and that the correct relationships between them have been established in the Power Pivot window.

Solution 1Recommended

Generate a Worksheet Table via DAX Command

Create a table connection on your worksheet and modify its command properties to execute a DAX query, which quickly combines the related data.

By directly querying the Data Model with DAX, you can bypass the performance overhead of Power Query merges. You can use DAX functions like NATURALLEFTOUTERJOIN or SUMMARIZE to combine your data efficiently.

1
Insert a Temporary Table

On the Excel ribbon, navigate to Data > Existing Connections (or Get Data > Existing Connections). Select one of the tables currently in your Data Model, open it, and choose to place it as a Table on your desired worksheet.

2
Access the DAX Editor

Right-click anywhere inside the newly imported table on your worksheet, navigate to the Table menu option, and select Edit DAX.

3
Configure the Command Type

In the Edit DAX dialog box, locate the Command Type dropdown menu and change it to DAX.

4
Execute the DAX Query

Paste your DAX expression into the expression box. For example, type EVALUATE NATURALLEFTOUTERJOIN(TableA, TableB) to join two tables. Click OK to execute the query and output the combined results to your worksheet.

Tip for Complex Queries: For more complex data models, it is highly recommended to write and test your DAX queries in a free external tool like DAX Studio before pasting them into Excel.
Free Microsoft Office alternative

Looking for a Faster Way to Handle Spreadsheets?

While Microsoft Excel's Data Model and DAX queries offer advanced BI capabilities, they can often be slow and resource-heavy. If you need a lightweight, lightning-fast spreadsheet tool for everyday data analysis, pivot tables, and reporting, WPS Office is the perfect solution.

  1. 1. Download WPS Office: Get the free WPS Office suite from the official website.
  2. 2. Install the Software: Run the lightweight installer to set up WPS Spreadsheet, Writer, and Presentation.
  3. 3. Open Your Data Files: Seamlessly open your existing .xlsx files and enjoy fast, efficient data analysis without heavy background models.
Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formatsLightning-fast performance for standard data processing and Pivot TablesClean, tabbed interface familiar to Office usersFree to use with built-in PDF tools and seamless cloud support
microsoft office alternative - wps office

Frequently Asked Questions

What is a DAX query in Excel?

DAX (Data Analysis Expressions) is a formula language used in Excel's Power Pivot and Data Model to perform advanced calculations and queries on related data tables.

Why use DAX instead of Power Query to combine tables?

Querying the Data Model directly with DAX can be significantly faster than executing a Power Query merge, especially for very large datasets, because it leverages the highly optimized VertiPaq engine.

Do I need external tools to write DAX queries for Excel?

While you can write DAX directly in Excel's connection properties, using external free tools like DAX Studio is highly recommended for building, testing, and debugging complex queries before applying them to your worksheet.