Power Query: Table of Tables
8 April 2020
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/175/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/175/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/175/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
I remove the ‘Changed Type’ step since it is serving no purpose here.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/175/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
My data is clearly made up of three separate tables, so I will split this table using the M function Table.Split():
Table.Split(table as table, pageSize as number) as list
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/175/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
The M code I have used is:
=Table.Split(Source, 4)
This will split the table extracted as ‘Source’ into tables of four (4) rows.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/175/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/175/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
This gives me my function body. I now need to convert this to a function.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/175/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
Once I click ‘Done’, my function prompts for a table:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/175/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
Now I can go back to my list of tables, and apply my function.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/175/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
I need to apply my function to my list using List.Transform(). The M code I use is
= List.Transform(Custom1, myfunction)
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/175/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
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:
= Table.Combine(Custom2)
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/175/image12.png/a1537847463e660a31158c8032525438.jpg)
My data is transformed into one large table.
Come back next time for more ways to use Power Query!