Power Query: Keeping it Current – Part 2
13 July 2022
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:
= Excel.Workbook(File.Contents("C:\Users\kathr\OneDrive\Documents\SUMPRODUCT\PQ Blog\PQ blog 279 and 280 Sheets Ahead.xlsm"), null, true)
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!