How To Create A Table From Another Table In Power BI

Creating a new table from an existing table in Power BI is a core data modeling technique that enables you to build summary tables, lookup tables, filtered subsets, and calculated reference tables without duplicating source data imports. DAX (Data Analysis Expressions) provides several powerful functions for this purpose, including SELECTCOLUMNS, SUMMARIZE, ADDCOLUMNS, FILTER, and DATATABLE. This guide covers each method with practical examples for enterprise reporting scenarios.

Understanding Calculated Tables in Power BI

Calculated tables are tables created using DAX expressions that derive their data from other tables in your model. They are computed during data refresh and stored in memory alongside your imported tables.

• When to use calculated tables — Creating lookup/dimension tables, building role-playing dimension tables, generating date tables, creating filtered subsets for specific report pages, and building summary tables for performance optimization
• When to avoid calculated tables — When the transformation can be done in Power Query (M language) during data load, which is generally more efficient for large datasets
• Memory impact — Calculated tables consume memory in the data model; use them judiciously in large models with millions of rows
• Refresh behavior — Calculated tables recalculate during every data refresh, so their content stays in sync with source data

Method 1: SELECTCOLUMNS — Create a Table with Specific Columns

SELECTCOLUMNS creates a new table by selecting and optionally renaming columns from an existing table. This is the most common method for creating a subset table.

• Creates a new table with only the specified columns
• Column names can be renamed in the output by providing new name strings
• Useful for creating dimension/lookup tables from denormalized fact tables
• Combine with DISTINCT or VALUES to remove duplicate rows

Method 2: SUMMARIZE — Create Aggregated Summary Tables

SUMMARIZE groups data by specified columns and can include aggregated measures, making it ideal for creating summary and rollup tables.

• SUMMARIZE creates distinct groupings; ADDCOLUMNS adds calculated aggregates
• This pattern (SUMMARIZE + ADDCOLUMNS) is the recommended approach over using SUMMARIZE with aggregation expressions directly
• Ideal for creating executive summary tables that pre-aggregate large fact tables
• Use ROLLUP or ROLLUPGROUP within SUMMARIZE for subtotals and grand totals

Method 3: FILTER — Create Filtered Subset Tables

FILTER creates a new table containing only rows that meet specified conditions, useful for creating focused tables for specific business units, time periods, or status categories.

• Creates a table with all columns from the source but only matching rows
• Multiple conditions can be combined with && (AND) and || (OR)
• Useful for creating role-based views or department-specific data subsets
• Combine with SELECTCOLUMNS to both filter rows and select specific columns

Method 4: DISTINCT and VALUES — Create Unique Value Tables

DISTINCT and VALUES extract unique values from a column to create lookup or reference tables.

• DISTINCT returns unique rows from a table expression (excludes blanks for single columns)
• VALUES returns unique values including the blank row that represents missing relationships
• Combine with SELECTCOLUMNS to create multi-column dimension tables from denormalized sources
• Essential for star schema modeling where dimension tables need to be extracted from flat tables

Method 5: Power Query (Recommended for Large Datasets)

For large-scale data modeling, creating derived tables in Power Query (M language) during data load is generally more efficient than DAX calculated tables, as transformations happen during refresh rather than consuming in-memory resources.

• Reference queries — Right-click a query in Power Query and select "Reference" to create a new query based on the existing one
• Duplicate queries — Create an independent copy of a query that can be modified without affecting the original
• Group By — Use the Group By transformation to create summary tables during data load
• Remove Columns — Use Remove Other Columns to create lean lookup tables from wide fact tables
• Merge Queries — Join multiple queries to create combined reference tables

Why Choose EPC Group for Power BI Data Modeling

EPC Group has over 29 years of enterprise business intelligence experience, with deep specialization in Power BI data modeling and DAX optimization. As a former Microsoft Gold Partner (2003–2022, the oldest in North America) and current Microsoft Solutions Partner, our Power BI architects have designed data models for Fortune 500 companies handling billions of rows across healthcare, finance, retail, and manufacturing. Our founder, Errin O'Connor, authored the bestselling Microsoft Press book on Power BI and leads a team of certified data modeling specialists.

• Enterprise Power BI data model architecture and star schema design
• DAX optimization for complex calculated tables and measures
• Power Query ETL development for large-scale data transformations
• Composite model design with DirectQuery and Import mode
• Performance tuning for models with billions of rows

Frequently Asked Questions