how to export mutiple filtered views of a table without hgaving to duplicate the query

[shneierl]

As various members of our hastily cobbled together team are now starting to be drawn back into the normal roles i am trying to consolidate some of their old excel reports into power query/ power pivot models to reduce the future workload as team numbers diminish, as it would then become a simple dump and hit refresh job (rather than the couple hour slog some of these reports are at the moment).

one of these reports currently is you extract data from a power bi dashboard (i'm waiting to see if the'll let me just connect the excel file into the data warehouse) then after adding a couple look up columns for things like the target output and region of the line in question you then filter and paste each regions data onto a its respective tab refresh all the pivots change any new column names and save down separate files that only contain each regions data (due for confidential regions we aren't allowed to share data cross region).

i can easily load the master table as an input table in power query in excel and then have it spit out a table into each tab that would feed the the pivots (need to ensure that they won't be able to change the pivot to show other regions). my question is the only way i can think of doing this is loading the data in and hitting duplicate 7 times renaming each table to the region and changing the filter and output point on each one accordingly. other than not wanting to go through the steps of each query 7 times my concern with this is i have to load the data 7 times on the refresh which i wonder how time saving that would actually be.

therefore i am wondering if after loading the data and adding in the look ups etc we want into the table could i not just output the table a few times and then change the request code so each table request s a specific filtered view or even output the table in filtered tabs similar to the view filter pages option on a pivot table.

[watkinnc · Accepted solution]

Making sure that the end user can select only their own region limits your consolidation options; the only way to not be able to select the other regions is if they are not in the dataset. But you don't have to duplicate the queries; you can load  the query once, and then add a Reference query. On this reference query, go in and add the Region filter as far upstream as possible (to cut as much data right up front, and then duplicate this query for the rest of your regions, changing the region filter in each. Upon first load, choose "Only Create Connection", so you don't load the main table. Then, load each query to a Pivot Table, as opposed to loading to an Excel table and creating pivots from those tables. This is way "lighter" to load than using actual Excel tables as the pivot source.

Note, however, that unless you make a separate workbook for each region, there is really nothing stopping folks from accessing the other queries in the workbook; unlike PowerBI, Excel does not use Row Level Security.

You could make one big table in a workbook, and then make seven different workbooks to access the one big table, each workbook referring to only one region in the query. If you are worried about people accessing other regions, I would recommend separate workbook per region altogether.

--Nate

[shneierl]

this works great bit of a pain having an extra 20 queries in the view pain but at least it works so thats all tha matters (sorry for the delayed response i was starting to type when your message first came in got a call from my boss and then forgot about it)