We currently complete a monthly report using pivot tables. The data is copied into a table & the pivot table is "refreshed". An archive copy is maintained for each month (i.e. Jan report, Feb report, etc). I'm now using Power Query to retrieve the data from an folder (Excel file in the folder) & format it. I created a "connection" to the data. Then built the pivot table using Power Pivot.
I have the following questions:
1. The users of the report are used to seeing the underlying data in a table (tab) of the Excel file. I tried loading to the data model & creating a connection, but got an error. That's why I used "connection only". What's the best way for the users to see the underlying data? Not all users have Power Query, so they can't use that to view the data.
2. How to I maintain an archive copy of the report? I tried disconnecting the query, but then the underlying data disappeared.
Hi Denise,
Great to see you're implementing Power Query into your work. To answer your questions:
1. Load the data the Data Model as connection only. Build your PivotTable and then double click the 'Grand Total' value field to drill down to the underlying data. This will create a new sheet that contains the transactions that support the PivotTable.
2. Unfortunately, there's no built in way to remove the connection to the source in Power Pivot/Data Model and still retain the data. I recommend the following workaround:
Right-click the query in the query pane > Properties > Usage tab > deselect 'Refresh this connection on Refresh All'. This prevents the query refreshing on Refresh All, but won't prevent the PivotTable and underlying query refreshing if someone accidentally refreshes the PivotTable.
As a contingency, I would make a copy of the file and save it as 'Read Only', so that if anyone does accidentally refresh the PivotTable, it won't be saved. Also, put a note for users of that file warning them not to refresh the query/PivotTable.
Mynda
It's now the next month & I'm trying to follow the instructions. I'm wondering if my view is different because I'm working in Excel 2013. When I right-click in the query pane & select properties, I don't see a Usage tab. See screen shot that I've attached. The only thing that I'm seeing is a "stop updates" button. Is this what I want? Have I selected the right part of the query?
Hi Denise,
Indeed, it's different in Excel 2013! To find the Properties dialog in Excel 2013 go to the Data tab > Connections > select the query from the list > Properties.
Mynda
OK -- I found the Data tab, then clicked on Connections. There are a lot of connections! So my follow up question is --
1. Do I have to do the steps listed above for EVERY query? or
2. Can I do the steps listed above for just the first part of the query?
See the screen shot that I attached. I broke the queries down into 4 categories. Can I just un
check the "refresh all" connection for the initial query in category # 1? Will that then stop the refresh from happening for categories 2, 3 & 4?
Hmmm, maybe. It depends! This post has some information about how Power Query caches data, which is particularly important if you have queries referencing other queries.
But if you just want to keep a snap shot of the data the simplest thing, as mentioned earlier, is to save a copy of the file and make it read only so even if someone refreshes the query it won't be saved to the original file.
Mynda
