Power Query: Keeping it Current – Part 1
6 July 2022
Welcome to our Power Query blog. This week, I copy a query to the workbook data has been extracted from.
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:
The query created a Table in the separate workbook:
However, what if I needed to create this Table in the original workbook?
I want to demonstrate the differences and the challenges when I move from concatenating data in an external workbook to the current workbook. I start by taking a copy of Monthly Sales. There are several ways I can do this, as I described in Power Query: It’s Good to Share (a Query). I choose to right-click on my query and ‘Copy’:
Then, I open the workbook containing the original data.
It currently has no queries as I can see if I use the ‘Queries & Connections’ button on the Data tab. If I right-click in the ‘Queries & Connections’ pane, I have a paste option:
When I paste my query, the results don’t look good!
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 I can change it to work with my current workbook. I will be doing this next time.
Come back next time for more ways to use Power Query!