Power Query: Sheets Ahead Part 2
13 April 2022
Welcome to our Power Query blog. This week, I continue uploading data from multiple sheets.
I (still) have some simple monthly data:
This sheet is for January (once again, apologies for any confusion caused to our 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 have created my query in another workbook. Last week, I had encountered a problem:
The red under the column headings indicates one or more errors – in this instance, this has been caused by the ‘headings’ row for January:
I can solve this by using ‘Remove Rows’ from the Home tab – this means that any other sheets added would also be dealt with, as they too would have a ‘headings’ row.
Using this option removes the errors:
I can now sort by date using the arrow in the Date column:
I rename the query Monthly Sales, and I am ready to add a sheet to the first workbook:
I ‘Close & Load’ the data since I can't change the original workbook while I am in Power Query. I load the data to a sheet in my current workbook:
Back in the original workbook, I enter the data for March:
I go back and refresh the Monthly Sales query:
The exclamation mark has appeared because I had the workbook it is accessing open. Now I have closed it, I refresh the query using the sheet icon. Watch the number of rows:
The March data has been included. I can now refresh this query to pick up all available sheets.
Come back next time for more ways to use Power Query!