How to Expand Columns in Power BI

When you import data into Power BI from web services, APIs, or databases, you sometimes encounter a column full of values that just say "Table," "Record," or "List." You know there's valuable data trapped inside, but it's not immediately clear how to access it. This guide is here to help you solve that exact problem. We'll walk through how to use Power BI's "Expand" feature to unlock this nested data so you can use it in your reports and dashboards.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

First, What Is Column Expansion?

Column expansion is a data transformation feature inside Power BI's Power Query Editor. Think of it as unzipping a compressed file directly within your dataset. When your source data has complex, hierarchical structures (like JSON from an API), Power BI will often load it with an entire dataset nested inside a single column. The "Expand" operation unpacks this nested column, turning the data inside it into new, regular columns in your table that you can actually work with.

You'll most often need to expand columns when dealing with:

• APIs: Web data sources (like from a CRM, marketing platform, or financial app) almost always return data in a nested format.
• Folder Connections: When you connect Power BI to a folder of files, the initial table often includes a column with nested tables containing the actual contents of each file.
• Data Grouped in Power Query: If you use the Group By transformation, the result is often a column containing tables or lists with the grouped data.

Every transformation we discuss happens in the Power Query Editor, the powerful data preparation layer behind Power BI, not in the main report or data views.

AI Agents for Marketing

Crash Course

Graphed

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.

Download the PDF

Accessing the Power Query Editor

Before you can expand anything, you need to open the Power Query Editor. It's where all the data shaping and cleansing happens. If you're not already there, it's just one click away from the main Power BI Desktop window.

On the Home ribbon, simply click the "Transform data" button. This will open the Power Query Editor, where you'll see your tables listed on the left and a preview of your selected data in the center.

How to Expand Columns with Nested Tables

This is the most common scenario. You'll see a column where every cell says "Table," and the column header has a special expand icon with two arrows pointing away from each other.

This indicates that each cell in that column contains another complete table. For example, you might have a table of "Sales Reps" and a nested table column called "Deals" for each rep.

Step-by-Step Instructions:

1. Click the Expand Icon: Click the two-sided arrow icon in the column header of the column containing the nested tables.
2. Choose Your Columns: A pop-up box will appear, listing all the columns available inside the nested tables. By default, all are selected. Best Practice: Only select the columns you actually need for your report. Unchecking unnecessary columns will make your report faster and more efficient.
3. Handle the Column Prefix: At the bottom of the pop-up, you'll see an option that says “Use original column name as prefix.” This is checked by default. In almost every case, you should uncheck this box.
4. Click OK: Once you click OK, Power Query replaces the original column of nested tables with the specific columns you selected. Your data is now "flattened" and ready for analysis.

How to Expand Columns with Nested Records

A "Record" is similar to a "Table" but represents a single row of data (a set of name/value pairs) rather than a full table with multiple rows. The process for expanding a column of records is virtually identical to expanding a column of tables.

You'll see the same expand icon in the column header, and the steps are exactly the same:

1. Click the expand icon in the column header.
2. Select the fields (columns) from the record that you want to add to your main table.
3. Uncheck the "Use original column name as prefix" box for cleaner column names.
4. Click OK.

Power Query will again replace the original column with the new fields you selected from the records.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

How to Expand Columns with Nested Lists

Sometimes, a column doesn't contain a structured table or record, but a simple "List" of values. For example, a single product might have a list of available colors or a blog post might have a list of tags. A column of Lists looks similar, but the actions you can take are quite different.

When you click the expand icon on a column containing Lists, Power Query gives you two main choices:

• Expand to New Rows
• Extract Values

When to Use "Expand to New Rows"

Choose this option when you want each item in the list to get its very own row in your table. The other values in the original row will be duplicated for each new row.

Example: Imagine a table of products with their tags stored in a list.

Original Table:

After clicking Expand to New Rows, the table will look like this:

This is incredibly useful because it normalizes your data, allowing you to easily filter by tags or count how many products have a specific tag.

When to Use "Extract Values"

Choose Extract Values when you prefer to keep all the information in a single row but want to combine the list items into a single text value.

Continuing the example above, if you select Extract Values, Power BI will prompt you to choose a delimiter — a character to separate the values. If you chose a comma, your table would become:

This approach is useful when you just need to display the list of tags as text and don’t need to use the individual tags for filtering or analysis.

AI Agents for Marketing

Crash Course

Graphed

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.

Download the PDF

Common Mistakes and Best Practices

As you get comfortable with expanding columns, keep these tips in mind to avoid common issues and keep your reports running smoothly.

• Be Selective with Your Columns: It's tempting to expand all available columns just in case, but this can really slow down your report's data refresh time, especially with large datasets. Only bring in the fields you know you’ll use. You can always go back and edit the step later to add more.
• Double-Check Performance: Operations like "Expand to New Rows" can dramatically increase the number of rows in your table. A 1,000-row table where each row expands into 10 new rows quickly becomes a 10,000-row table. While powerful, this can impact performance on very large models.
• Clean Up Before You Expand: If you need to filter your data (for example, removing rows based on a certain category), do it before you expand. This reduces the number of records Power Query has to process during the expansion, leading to a faster refresh.
• How to Undo a Mistake: Made a wrong choice? No problem! The Power Query Editor tracks every transformation in the Applied Steps pane on the right. Simply find the step named "Expanded [Column Name]" and click the "X" next to its name to delete it. It's a non-destructive way to undo your work and start over.

Final Thoughts

Expanding columns is a core skill for anyone serious about using Power BI for data preparation. Once you understand how to unpack nested tables, records, and lists, you can connect to a much wider variety of complex data sources and shape them into the clean, tabular formats needed for meaningful analysis. It transforms confusing nested data into a flat, usable structure.

Mastering these steps in Power BI is rewarding, but it's also a great example of the manual data prep work that can consume hours every week. We built our tool, Graphed, to automate exactly this type of work for marketing and sales teams. Instead of wrestling with data transformations, you connect your platforms like Google Analytics, Shopify, and Salesforce, then simply describe the dashboards you need in plain English. We handle the complex data prep behind the scenes, turning messy API data into real-time, interactive dashboards in seconds — no clicking, expanding, or prefix management required.