Power Query: Sheets Ahead Part 1

Welcome to our Power Query blog.  This week, I look at uploading data from multiple sheets.

I have some simple monthly data:

This sheet is for January (apologies to any confused US readers!), and I have a similar sheet in the same workbook for February:

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:

I select this option and browse to the Workbook with the monthly sheets.  The Navigator dialog appears, and I can view the data:

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’:

Power Query has helpfully created some steps for me, but I am only interested in the Source step:

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. 

I only need the Data column (field), so I select this and choose to ‘Remove Other Columns’.

I choose to continue by inserting a step:

In the Data column, I can use the split arrows icon to combine the tables.

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.

Again, I am happy to insert a step:

My data appears.  I can delete the ‘January_Sheet’ step, since I am now looking at all the sheets:

The rest of the steps now work as before – well almost:  

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!