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 transform nested tables in Power Query (without coding)

Transform Nested Tables in Power Query

In Power Query, it is common to have nested Tables. These are Tables contained within a column, where each row contains a separate sub-Table. Sometimes we just want to expand the data; that is easy. But sometimes, we need to transform the data in the nested Table before expanding; that is NOT so easy. So, how can we achieve this?

That’s what we are looking at in this post; how to transform nested Tables in Power Query without writing the M code.

We will use a few techniques you may not have used before. But don’t worry; it will work if you follow the steps.

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

Watch the video

Watch the video on YouTube

What are Nested Tables?

Nested Tables appear in many circumstances in Power Query:

  • Connecting to a source with multiple data objects (e.g., From Folder or From Excel Workbook)
  • Using Group By with the All Rows option
  • Merging Tables

They look like this:

Nested Table Example

Example Data to transform nested tables

The example data in this post is a workbook with 4 tabs (P1, P2, P3, and P4). P1 looks as follows:

Example worksheet

The other tabs have different rows but look the same.

Once we connect to the workbook, the Power Query preview window looks as follows:

Example data in Power Query

NOTE: To work along with the example file, you will need to re-point the source to the workbook on your PC.

Click on the white space next to any Table to reveal the data inside that Table.

Click next to Table to show the nested data

We are going to apply our solution in 3 steps:

  1. Apply transformation to one Table
  2. Get the code for the transformation
  3. Apply the transformation to each nested Table in a column

Apply transformation to one Table

In the example, for each nested Table, we want to:

  • Remove the null values at the top
  • Remove the Total row at the bottom
  • Promote headers
  • Add an Index column.

To do this, we start by making the transformations to a single Table.

As we don’t want to mess with the original query, in the Queries pane, right-click on the query and select Duplicate.

Duplicate query

To make it clearer which Table we are working with, let’s rename the duplicate query to Duplicate.

In the Duplicate query, click on the word Table in the first row of the preview window to drill into it.

Now we can proceed with the transformations for this worksheet. In the example file, we will:

  • Delete the Promoted Headers and Changed Type steps
  • Remove the Top 3 rows by clicking Home > Remove Rows > Remove Top Rows > Number of Rows: 3 > OK
  • In the Date column, Right-click on the word TOTAL and select Filters > Does Not Equal
  • Promote the headers by clicking Transform > Use First Row as Headers
  • If the Changed Type step has not been applied automatically, you will need to add the data type for each column
  • Click Add Column > Index Column

Get the code for the transformation

So far, everything probably makes reasonable sense. But, for the following steps, you will just have to trust me…

With the Duplicate query selected, click View > Advanced Editor.

This contains all the query steps. Identify the point at which you started making changes to the individual Table.

Our first action on the individual table was to #”Removed Top Rows”. In the screenshot below, all the steps before this have been highlighted.

Make a note of the last step before making the individual Table transformations. In our example, it is #”Jan-23_Sheet”.

Splitting the steps

Delete these steps, but keep the word let at the start.

The code now looks like this:

Edited Code - with all transformation steps for nested Table

Select all the code, copy it, and then close the Advanced Editor. Don’t worry about any errors; we won’t use this query again.

Apply the transformation to each nested Table in the column

In the User Interface, there are no transformations for the Data column which give us the code we need. We could write the M code ourselves using the Table.TransformColumns function, but I assume you’re not an M code wizard. So, instead, we are going to transform another column and then steal the code.

In the original query, select the Name column and click Transform > Extract > First Characters > Count: 1 > OK

It doesn’t matter what this transformation is. We just need to generate the syntax for the Table.TransformColumns function.

The formula bar shows the following code:

= Table.TransformColumns(Source, {{"Name", each Text.Start(_, 1), type text}})

We are going to reuse this code. The parts we will change are:

  • “Name”: Refers to the name of the column to transform
  • type text: Refers to the column’s data type following the transformation
  • Text.Start(_,1): Refers to the transformation to undertake

Now let’s make the following changes to make the code work for our scenario:

  • Rename Name to Data
  • Change the code type text, to type table.
  • Highlight the Text.Start(_,1) section, then paste the code we copied earlier.

This will lead to an error. In the example, the error is due to #”Jan-23_Sheet”. This was the name that we made note of earlier.

Find each instance of #”Jan-23_Sheet”, and replace it for an _ (underscore)

The formula bar for this step now looks like this (edited sections in bold)

= Table.TransformColumns(Source, {{"Data", each let
    #"Removed Top Rows" = Table.Skip(_,3),
    #"Filtered Rows" = Table.SelectRows(#"Removed Top Rows", each [Column1] <> "TOTAL"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Item", type text}, {"Region", type text}, {"Size", type text}, {"Value", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type)
    #"Added Index", type table}})

What to understand Table.TransformColumns and write the M code yourself?

Were the nested Tables transformed?

So, did this work? Were the nested Tables transformed?

Previously, the Table looked like this:

Initial Table view

Now the Table looks like this:

Final view of nested Table

BOOM! Yes!! It worked.

Now you can expand the columns as you wish.

Finally, delete the Duplicate query as it currently serves no purpose.


The Power Query UI does not give us an easy way to transform a nested Table. However, with a bit of knowledge and code stealing, we can transform a nested table in Power Query without writing any of the M code ourselves. 😁

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 *