Hi all,
I've relatively recently become responsible for administrating surveys at a small college. I have a fair amount of autonomy in the role and at the moment there's clear scope for improvement. Currently I'm creating surveys in Forms for Excel, and building a dashboard for each one in PowerPivot, that staff can use to explore the results.
I want to build a more robust system that can bring those datasets together and compare them, as well as reduce the amount of time I spend building dashboards. At the moment I'm wondering if it's possible in Excel.
The rough sketch of the idea I have is:
- Individual workbooks hold survey data.
- A dashboard workbook contains:
- PowerQuery calls to get data from the survey workbooks
- Register table of survey workbooks
- Table of "Type" questions
- Key table that relates the columns in each survey table to the type questions
- Dashboard built in PowerPivot designed to report on the Type questions, with data supplied from the survey workbook queries interpreted using the key table.
- (Possibly) Some kind of additional UI built in Developer to allow the user to specify which queries to call, to reduce the amount of data brought into the dashboard workbook.
- ((Possibly possibly)) Not sure if the key table should be used to transform data as its called into the worksheet via the queries (so that the data held in the worksheet can be interpreted by the dashboard) or if the translation should be done when the dashboard operates.
PowerBI isn't an option.
Would this approach be possible?
And would it be a good idea?
