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