Power Query: Sheets Ahead Part 1
6 April 2022
Welcome to our Power Query blog. This week, I look at uploading data from multiple sheets.
I have some simple monthly data:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/279/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
This sheet is for January (apologies to any confused US readers!), and I have a similar sheet in the same workbook for February:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/279/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
I want to create a query that not only concatenates this data, but will also include the sheets for other months as they appear if I refresh it.
I am going to create my query in another workbook. In the ‘Get & Transform’ section of the Data tab, I choose the ‘Get Data’ dropdown, where I can choose ‘Workbook’ in the File options:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/279/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
I select this option and browse to the Workbook with the monthly sheets. The Navigator dialog appears, and I can view the data:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/279/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
I start by selecting January, but it doesn’t matter which sheet I pick as I plan to amend the query to load all sheets. I opt, as ever, to ‘Transform Data’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/279/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
Power Query has helpfully created some steps for me, but I am only interested in the Source step:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/279/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
If I click in the white space next to a ‘Table’ in Data, I can see the contents. Note that the headings are on the first row.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/279/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
I only need the Data column (field), so I select this and choose to ‘Remove Other Columns’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/279/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
I choose to continue by inserting a step:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/279/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
In the Data column, I can use the split arrows icon to combine the tables.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/279/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
I have already seen that the headings are in the rows, so I choose to expand all columns. I do not need to keep any column names for prefixes.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/279/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
Again, I am happy to insert a step:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/279/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
My data appears. I can delete the ‘January_Sheet’ step, since I am now looking at all the sheets:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/279/image13.png/917da985be13220165c8d2823e95344f.jpg)
The rest of the steps now work as before – well almost:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/279/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
The red under the column names indicates one or more errors – next time I will solve this, complete my query and test it by adding more data to the workbook.
Come back next time for more ways to use Power Query!