Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

How to expand columns dynamically in Power Query

Expanding table columns is a common Power Query action. It even has its own icon. But there is a problem lurking here. Clicking the expand data button hardcodes the column names into the M code. So, if new columns are added to the source at a later date, they don’t show up. So, in this post, we are looking at how to expand columns dynamically in Power Query to get all the data we want.

Download the example file: Click the link below to download the example file used for this post:

Watch the video

Watch on YouTube

The problem

Let’s take a look at a simple example to see the problem.

The data

Our Excel workbook includes 4 worksheets: Q1, Q2, Q3, and Q4.

Each worksheet contains 4 columns: Date, Item, Region, and Value.

Here is the example data from the Q1 worksheet:

Example dataset for dynamically expanding data

Connecting to the data

When we connect to the workbook, in the Navigation dialog box, we select the folder to import all the worksheets.

Navigator dialog box - Select workbook, click Transform Data

The Preview window displays all the worksheets in a single table.

Preview window after Workbook connection

At the moment, we just want the Data column. So, select the Data column, then click Home > Remove Columns (drop-down) > Remove Other Columns.

Expanding the column

To expand the data, click the two arrow icon at the top of the Data column. We want all the columns, and we don’t want to include the original column name as a prefix.

Expand the Data column to append the data

Fantastic, we now have all the data expanded.

The problem

Now, let’s go back and add a new column to the source file.

In the Q3 tab, I’ve added a Size column.

New column in the source data

When we refresh the data, the new column does not appear.

Looking at the M code for the Expanded Data step reveals the problem. The code generated by automatically Power Query is:

= Table.ExpandTableColumn(Source, "Data", {"Column1","Column2","Column3","Column4"}, {"Column1", "Column2", "Column3", "Column4"})

The column names have been hard-coded into the step. Therefore, this never works when new data is added.

If we manually change the M code to include “Column5” that will work. But it’s not a great solution. We don’t want to manually edit queries each time there are changes.

Let’s create a dynamic solution.

Using Table.Combine to expand columns dynamically

Table.Combine is an M code function that (yes, you guessed it) combines tables. The great news is the function doesn’t require us to hardcode any column names; it picks up all of them.

Delete the Expanded Data step. Click on the fx icon in the formula bar, then enter the following:

= Table.Combine(#"Removed Other Columns"[Data])
  • #”Removed Other Column” is the name of the previous step
  • [Data] refers to the Data column which we want to expand.

BOOM! That’s all it took.

Table.Combine adds new data to Table

Now the preview window shows all the columns from all the Tables; including the new column.

Find out more about Table.Combine here:

Using Table.Combine while retaining columns

Before you start leaping about in celebration, Table.Combine also has an issue. It doesn’t retain any of the existing columns.

Go back to the step where we removed the other columns; change it so that the Name and Data columns are kept.

As soon as we go back to the Table.Combine step, the Name column disappears. This is because previous columns are not retained.

This means it’s time to join our knowledge and create the perfect solution.

Change the Table.Combine step to the following:

= Table.ColumnNames(Table.Combine(#"Removed Other Columns"[Data]))
Using Table.ColumnNames on Table.Combine

Table.Combine gets us all the columns. Table.ColumnNames gets us the list of the names from those columns.

Copy the code in the formula bar (excluding the equals sign). Then delete the step.

Now, let’s click the expand icon as normal.

In the M code, we can replace the first instances of this:

{"Column1", "Column2", "Column3", "Column4"}

With this the code we copied from above:

Table.ColumnNames(Table.Combine(#"Removed Other Columns"[Data]))

Looking at the documentation for Table.ExpandTableColumn reveals that the second section of column names is optional. Therefore, we can remove the second instance of:

{"Column1", "Column2", "Column3", "Column4"}

The final M code function for this step will be:

= Table.ExpandTableColumn(#"Removed Other Columns", "Data",Table.ColumnNames(Table.Combine(#"Removed Other Columns"[Data])))
Final view with columns added

The preview window displays the retained columns and the new column. AMAZING!

Find out more about:

How to get the correct column names

Our data has come from separate Excel worksheets. Because it is an unstructured source, the columns are given the names of Column1, Column2, Column3, etc. Wouldn’t having the correct column names from the original data set be better?

To do that, we must promote the headers inside each Table.

Select the step before expanding the data. In our example, it is called Removed Other Column.

Click on the fx icon next to the formula bar to create a new step and enter the following:

= Table.TransformColumns(#"Removed Other Columns",{{"Data", each Table.PromoteHeaders(_), type text}})

This function promotes headers within each table in the Data column before they are expanded.

Now, when we expand the table, we have the correct column names.

Power Query dynamic column expand with column names


By default, expanding columns in Power Query uses the Table.ExpandTableColumn function. This function hard-codes column names. Therefore, if column names change or new columns are added, our data may be incomplete or cause errors.

If we use the Table.Combine function; it includes all new columns, but does not retain any existing columns.

But we were able to use both functions together, along with Table.ColumnNames to build a completely dynamic solution.

Related posts:

Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).

Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

Your email address will not be published. Required fields are marked *