How to Expand Table in Power BI
Wrangling your data into the right shape is one of the most important first steps in building a useful Power BI report. If you've ever dealt with nested tables - tables tucked inside a column of another table - you know it can be a roadblock. This tutorial will walk you through exactly how to use the "Expand" feature in Power Query to flatten that complex data, making it ready for analysis and visualization.
What Does "Expand a Table" Mean in Power BI?
In Power BI's Power Query Editor, "expanding" a table refers to the process of deconstructing a column that contains nested data structures, like tables or records. Instead of having a single column where each cell is a self-contained table, the expand function unpacks that nested data and pulls out specific columns from it, adding them as new columns to your main, or "parent," table.
Imagine you have a table of online orders. Each row is an order, but one column, let's call it [OrderDetails], doesn't just contain a single value. Instead, each cell in that column holds another table with the specific products, quantities, and prices for that order.
Here’s a simplified view:
- Main Table: Orders
- Columns: OrderID, CustomerID, OrderDate, [OrderDetails]
Inside the [OrderDetails] column, each cell looks like a "Table" link. Clicking it would reveal something like this:
- Nested Table: ProductID, Quantity, UnitPrice
Expanding the [OrderDetails] column lets you pull ProductID, Quantity, and UnitPrice out of the nested structure and place them alongside OrderID and CustomerID in the main table. The result is a flat, denormalized table that is much easier to work with when creating charts and measures.
Free PDF · the crash course
AI Agents for Marketing Crash Course
Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.
Why (and When) You Need to Expand Tables
This process might seem a bit abstract at first, but it’s an incredibly common and powerful data transformation step. You'll typically need to expand tables in a few key situations:
- Handling API or JSON Data: Data pulled from many web services and APIs often arrives in a nested JSON format. Power Query interprets these nested objects as records or tables, making the expand feature essential for accessing the underlying data points.
- Combining Related Queries: When you merge two queries in Power BI (like joining your Sales table with your Customers table), Power Query often creates a column containing the entire matching table for each row. For example, for each sale, you might get a new column with a nested table containing all the details for the specific customer who made that purchase. You'd then expand this column to bring in the customer's name, email, or location.
- Simplifying the Data Model: While a fully 'normalized' data model with many related tables is technically efficient, it can be hard to work with for reporting. Sometimes, creating a flatter, wider table by expanding related data makes writing DAX measures and building visualizations much more straightforward.
- Creating Item-Level Reports: Following our orders example, if you want to analyze which products are most popular or calculate total revenue per product, you need access to the granular data inside that nested [OrderDetails] table. Expanding is the only way to get it into a usable format.
Your Step-by-Step Guide to Expanding a Table in Power Query
Let's walk through the exact process inside the Power Query Editor. It's surprisingly simple once you know what to look for.
Step 1: Get into the Power Query Editor
First things first, you need to open Power Query. From the main Power BI Desktop window, go to the Home tab and click on the Transform data button. This opens a new window - the Power Query Editor - where all data shaping happens.
Step 2: Find the Column Containing Nested Tables
Once in Power Query, navigate to the query (table) you need to modify from the list on the left. Look for a column header that has a special "expand" icon. This icon looks like two arrows pointing away from each other (↔). The cells in this column will often display the word Table or Record, rather than a specific value.
Step 3: Click the Expand Icon
This is where the magic happens. Click on the expand icon in the column header. A dropdown menu will appear, showing you a list of all the available columns within the nested tables. Power Query samples the data to figure out what fields are available inside.
Step 4: Choose the Columns You Want
In the dropdown menu, you'll see a checkbox list of the nested table's columns. By default, Power Query will have the "(Select All Columns)" box checked. It's a best practice to uncheck this first and then individually select only the columns you actually need for your report.
Bringing in unnecessary columns can slow down your data refresh and make your table messy. For our e-commerce example, you'd probably check the boxes for ProductID, Quantity, and UnitPrice.
Step 5: Decide on the Original Column Name Prefix
At the bottom of this dropdown, you'll see a checkbox labeled "Use original column name as prefix." This option is very important.
- When checked (default): The new columns will be named with the original column name as a prefix. For instance, if your original column was named OrderDetails, your new columns would become OrderDetails.ProductID, OrderDetails.Quantity, etc. This is very useful for avoiding name conflicts if your main table already has a column named "ProductID".
- When unchecked: The new columns will keep their original names (e.g., ProductID, Quantity). Uncheck this if you prefer cleaner column names and you're confident there aren't conflicting names in your main table.
As a beginner, leaving it checked is often the safer option.
Step 6: Click 'OK' to Finalize
Once you’ve selected your columns and decided on the prefix, click OK. The Power Query Editor will perform the operation, and you'll see your main table now includes the new columns you selected. The original nested column will be gone, replaced by its contents.
Step 7: Close & Apply Your Changes
With the table expanded, your data is now in a much better shape for reporting. To send this updated table back to your Power BI data model, go to the Home tab in the Power Query Editor and click Close & Apply.
After a brief loading period, your expanded table will be ready to use in your visuals and DAX calculations.
Best Practices and Common Pitfalls
While the process is straightforward, a few tips can help you avoid common issues.
1. Be Selective About Your Columns
It's worth repeating: always uncheck "Select All" and choose only what you need. Expanding a dozen columns when you only need two will significantly increase the size of your data model and slow down every data refresh. Keep things lean.
2. Watch Out for Row Duplication
This is the most common "gotcha" for new users. When you expand a one-to-many relationship - like one order that contains three separate products - Power BI will create a row for each product. This means the data from the "one" side (like OrderID and OrderDate) will be duplicated across those three rows.
Original Table: OrderID: 101, CustomerID: 55, Details: [Table of 3 items]
Expanded Table: OrderID: 101, CustomerID: 55, ProductID: "A" OrderID: 101, CustomerID: 55, ProductID: "B" OrderID: 101, CustomerID: 55, ProductID: "C"
This is the correct and expected behavior! It's what allows for item-level analysis. However, if you try to count your orders by simply counting the rows in this new table, your numbers will be wrong. You should use a DISTINCTCOUNT on the OrderID column instead. Understanding this prevents major reporting errors down the line.
Free PDF · the crash course
AI Agents for Marketing Crash Course
Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.
3. Differentiate Between "Expand" and "Aggregate"
When you click the expand icon, you'll notice Power Query also gives you the option to Aggregate. While "Expand" flattens the details, "Aggregate" performs a calculation on the nested data and returns a single value. For example, instead of listing every single product, you could use Aggregate to get a count of products in each order or a sum of the total sale amount. Use Aggregate when you only need a summary, not the line-item details.
Final Thoughts
Learning how to properly expand tables in Power Query is a fundamental skill that moves you from being a user who can only work with perfect data to one who can shape raw, complex data into a powerful analytical asset. This simple feature unlocks the ability to work with API outputs, denormalize related tables, and prepare your data for meaningful insights.
As you can see, becoming proficient in a specialized BI tool like Power BI involves learning its specific workflows and technical steps. At Graphed, we’ve created a way for you to bypass that steep learning curve entirely. Instead of clicking through menus to transform data, you can connect your sources like Google Analytics, Shopify, or Salesforce, and then simply ask in plain English for the dashboard you need. We automate the data preparation process, allowing you to go from data to a live, real-time dashboard in seconds, not hours.
Related Articles
How to Create Facebook Ads API for Claude Code
Create a Meta Marketing API access token that never expires, using a system user, so Claude Code can pull Facebook ad reports and manage campaigns.
ClickHouse vs Postgres: When to Switch to Columnar
Comparing ClickHouse vs PostgreSQL means matching storage architecture to workload. Row storage wins for transactions. Columnar wins for scan-heavy analytics.
Google Ads for Orthodontists: Get More Booked Consults
Most orthodontic Google Ads campaigns waste money on low-intent searches. The problem is structure, not the platform.