Hi,
I am working through setting up a reporting tool which imports financial and workforce date. The structure of the file fields at Organisation level is Service, then Division and then Cost Centre with records held for monthly $ and workforce data; eg:
| Service | Division | Cost Centre | Mth | Actual | Budget |
The issue is to import based on a flexible import to select: a single Service, OR a single Division OR multiple Divisions. I am considering using Parameters to feed into the filtering process with an If then else statement. If an Service is selected then import based on that and ignore Division; but if Division is selected then import that Division only. But not sure how to import if more than one Division?
Appreciate any thoughts.
Regards, Kym
Hi Kym,
Welcome to our forum!
I would use Power Query to bring in all of the data and then use Slicers to allow the user to select (filter) the data they want to see. Slicers are covered in session 2.03 of the Excel Dashboard course.
Mynda
Hi Mynda,
That is something I had not though about and I will work on - it might be possible as if multiple Divisions are required they are usually part of the same Service. There are 10 Service groups with 20 Divisions plus around 300 cost centres; which by monthly, account etc over 2 financial years is a significant level of data.
Importing all data will be of significant size with 3 different imports - financial, Workforce and Excess Leave all of which go to the lowest level possible plus multiple years for trend analysis. Hence hoping to limit the imported data through Parameters; and perhaps as you suggested Slicers.
Regards, Kym
As long as you're loading the data into Power Pivot (as opposed to the Excel worksheet) it should be ok to handle the volume.
You can think about it this way; if you apply filters at the 'get data' stage using Power Query and want to then switch to different data, then Power Query has to go through the whole loading of data process again. This 'refresh' will take some time to execute given the amount of data you're talking about. Whereas, if you load all of the data and then use Slicers to filter what you see in the report it should be much quicker for the Slicers to respond than having Power Query unload and then reload different data.
I would only filter the data at the Power Query stage if this is more of a permanent filter. Slicers are for changing views, filtering in Power Query is for removing unwanted data.
Hope that helps clarify things.
Hi Mynda,
Thank you, It is somewhat a permanent filter but I think also using Power Pivot with slicers is a good option. If I understand correctly all the relevant data would be in the cache memory (power query result into the data model) and then the slicer would update the data in the spreadsheet for the reporting requirements.
Regards, Kym
Correct. With Power Pivot the cache is the Power Pivot model and it's far more efficient at compressing data than the pivot cache for regular PivotTables.
