Power Query: Table of Tables

Welcome to our Power Query blog. This week, I look at how to reformat a table of data which is made up of sub-tables.

I have the following data from my imaginary salespeople:

I have information for three of my salespeople, but I’d like to reformat my data so that I have everything in separate columns. I begin by extracting my data to Power Query using the ‘From Table’ option in the ‘Get & Transform’ section on the Data tab.

I accept the default area, but I uncheck the ‘My table has headers’ section since the headings at the top are not for all the data.

I remove the ‘Changed Type’ step since it is serving no purpose here.

My data is clearly made up of three separate tables, so I will split this table using the M function Table.Split():

This function splits the table into a list of tables, where the first element of the list is a table containing the first pageSize rows from the source table, the next element of the list is a table containing the next pageSize rows from the source table, etc.

Since each of my sub-tables is the same size, this will work for my example.  I add a step using the fx button.

The M code I have used is:

This will split the table extracted as ‘Source’ into tables of four (4) rows. 

When I execute this step, I see that I get a list of three tables, and when I click the space next to ‘Table’ I can see the data in each table. I need to know what to do to each of my tables, and then put it into a function to use against my list.

I start with one of the tables, and transform my data:

This gives me my function body. I now need to convert this to a function.

Once I click ‘Done’, my function prompts for a table:

Now I can go back to my list of tables, and apply my function.

I need to apply my function to my list using List.Transform().  The M code I use is

I can see that the data within the tables in my list has been transformed.

My final step is to glue my tables back together.  I use Table.Combine() for this.  My M code is:

My data is transformed into one large table.

Come back next time for more ways to use Power Query!