Power Query: Keeping it Current – Part 3

Welcome to our Power Query blog.  This week, I complete the query to look for data in the current workbook.

In Power Query: Sheets Ahead  – Part 1,  I uploaded data from multiple sheets into another workbook.  I had some simple monthly data:    

This sheet is for January; I had a similar sheet in the same workbook for February:

I created a query that not only concatenated this data, but also included the sheets for other months as they appeared if I refreshed it.  This query was in a separate external workbook:

Last week, I amended a copy of this query in the original workbook containing the source data.

However, I have an issue with accessing data from within the workbook: I can see the tables (and Named Ranges) but not data on the sheets.

Last week, I received access errors treating this workbook as an external file by using Excel.Workbook().  Now, I can’t see data that is not in a Table or a Named Range now that I am using Excel.Current.Workbook().  Therefore, I need to amend the data.  If the data is likely to be added to, then converting each month’s data to a Table is a good idea.

In the Excel, I use CTRL + T (Insert -> Table) to convert each sheet to a Table, making sure I am in a cell within the data when I do this:

I rename the Tables to make them easier to identify, though the names will not be referenced by the query. 

I repeat this for each month and go back to the Monthly Sales query:

I can see the data now.  You should note that if there were Excel Tables or Named Ranges which should not be included in the query, I could filter here on Name to remove the data that should be excluded.  This is not a problem in my example, but I have another issue to address.  This step gives different results to the Source step in the external version of the query.  In particular, the data is now in the Content column rather than the Data column:

I will need to change the steps that expand the data.  I remove the ‘Removed Other Columns’ and ‘Expanded Data’ steps, and expand the data in Content.  I ignore any warnings about deleting and inserting steps:

As the data was already in Excel Tables, I do not need to promote the headers this time, so I can delete the ‘Promoted Headers’ step.  I do need to remove any duplicate rows, as the results of the query will be in the same workbook.

The data can now be loaded to the Excel workbook using the ‘Close & Load’ option from the Home tab:

The data appears in the same workbook:

The final test is to add another tab for April – which must also be a Table.

The new data is picked up when I refresh.

Come back next time for more ways to use Power Query!