Power Query: Keeping it Current – Part 2

Welcome to our Power Query blog.  This week, I start to convert 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 workbook:

Last week, I copied this query to the original workbook containing the source data:

The message is telling me the query can’t access the workbook because I have it open.  This is true since it is this workbook.  However, I have the M code, and this week, I will change it to point to the current workbook.

I close the error dialog and right-click on the Monthly Sales query, so that I may Edit:

This takes me to the Power Query editor, where I select the first step (Source).

The M code for this step is:

Instead of pointing at an external workbook, I need to change this to use the current workbook.

I can use Intellisense to help me here.  If I delete ‘Excel.Workbook’ and start typing, I can see the following available functions:

Therefore, instead of Excel.Workbook(), I can use Excel.CurrentWorkbook().

Since I have a location, I don’t need the parameters, so I may delete them.  Note that the Expression.Error dialog tells me that no arguments are required.

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

Next time, I will adapt the query and the data to get the results I need.

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