Please note javascript is required for full website functionality.


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!