How To Create A Table From Another Table In Power BI

How to Create a Table from Another Table in Power BI

Power BI lets you create new tables from existing ones using DAX calculated tables or Power Query (M) transformations. Use calculated tables for summary tables, lookup tables, and filtered subsets. Use Power Query for transformations that run during data refresh. This guide covers both methods with DAX examples.

Key facts

• DAX calculated tables run in-memory after data loads. They consume model RAM but do not slow refresh.
• Power Query tables transform data during refresh. They are more efficient for large datasets.
• Key DAX functions for table creation: SELECTCOLUMNS, SUMMARIZE, FILTER, ALL, CROSSJOIN, UNION, CALENDAR.
• Calculated tables live in the Power BI data model and appear in the Fields pane alongside imported tables.
• EPC Group: Power BI architects with 29 years of Microsoft consulting. Authors of a bestselling Microsoft Press Power BI book.

Method 1 — DAX calculated tables

DAX calculated tables let you create a new table using a DAX formula. The table is calculated when the model loads and stored in memory.

Create a calculated table in Power BI Desktop

1. Open Power BI Desktop and load your data model.
2. Go to Modeling → New Table in the ribbon.
3. In the formula bar, type your DAX expression and press Enter.
4. The new table appears in the Fields pane on the right.

SELECTCOLUMNS — select specific columns

Use SELECTCOLUMNS to create a new table with only the columns you need.

SalesSubset =
SELECTCOLUMNS(
    Sales,
    "Order ID", Sales[OrderID],
    "Customer", Sales[CustomerName],
    "Amount", Sales[SaleAmount]
)

This creates a three-column table from the Sales table. Column names in quotes become the new column headers.

SUMMARIZE — create a summary table

SUMMARIZE groups rows and aggregates values, similar to a GROUP BY in SQL.

SalesByRegion =
SUMMARIZE(
    Sales,
    Sales[Region],
    Sales[Category],
    "Total Sales", SUM(Sales[SaleAmount]),
    "Order Count", COUNTROWS(Sales)
)

This creates a summary table grouped by Region and Category with two calculated columns.

FILTER — create a filtered subset

FILTER returns rows from a table that meet a condition.

ActiveCustomers =
FILTER(
    Customers,
    Customers[Status] = "Active"
)

Combine FILTER with SELECTCOLUMNS to create a filtered subset with selected columns only.

CALENDAR — create a date table

Use CALENDAR or CALENDARAUTO to generate a date table for time intelligence calculations.

DateTable =
CALENDAR(DATE(2020, 1, 1), DATE(2026, 12, 31))

Add columns for Year, Month, Quarter, and Weekday using ADDCOLUMNS wrapped around CALENDAR.

Method 2 — Power Query (M) transformations

Power Query runs during data refresh. It is more efficient for large tables because transformations happen before data loads into memory.

Duplicate a table in Power Query

1. Open Power BI Desktop and click Transform data.
2. In the Queries pane on the left, right-click the source table and select Duplicate.
3. The duplicate appears as a new query. Rename it in the Properties panel.
4. Apply filters, remove columns, or change data types as needed.
5. Click Close & Apply to load the new table into the model.

Reference a table in Power Query

Use Reference instead of Duplicate when you want the new table to reflect all changes made to the source query automatically. Right-click the source table and choose Reference.

Calculated table vs Power Query: when to use each

Scenario	Recommended method
Summary aggregations (GROUP BY logic)	DAX SUMMARIZE
Lookup / reference tables from existing data	DAX SELECTCOLUMNS or Power Query
Large filtered subset (millions of rows)	Power Query (runs at refresh, not in memory)
Date dimension table	DAX CALENDAR / CALENDARAUTO
Transformations using M functions	Power Query
Cross-join or union of two tables	DAX CROSSJOIN or UNION

Frequently asked questions

Can I create a table from a measure in Power BI?

No. Measures return scalar values, not tables. Use DAX table functions (SUMMARIZE, SELECTCOLUMNS) instead. You can reference measures inside SUMMARIZE to aggregate values in the new table.

Do calculated tables slow down report performance?

Calculated tables consume model RAM because they are stored in the Vertipaq engine. They do not slow refresh. But a large calculated table increases model file size and memory requirements. Use Power Query transformations for very large tables.

Can I create relationships between a calculated table and other tables?

Yes. Calculated tables support relationships just like imported tables. Define the relationship in the Model view using drag-and-drop or the Manage Relationships dialog.

What is the difference between SUMMARIZE and SUMMARIZECOLUMNS?

SUMMARIZECOLUMNS is the newer, more efficient function. It is preferred for most grouping scenarios because it handles blank rows better and supports multiple filter contexts. Use SUMMARIZE for legacy compatibility or when SUMMARIZECOLUMNS produces unexpected blanks.

Can I use a calculated table as a slicer source?

Yes. Any table in the model — including calculated tables — can drive a slicer. This is a common pattern for dynamic slicer lists that filter based on the current data.

Talk to a Power BI data architect

EPC Group has designed data models for Fortune 500 companies handling billions of rows. Call (888) 381-9725 or request a 30-minute discovery call.