Forum Discussion
Automating a report with pre-existing tables and charts
Posting this again, this time with examples to hopefully help you all answer the question. I will attach two excels. The first one is an example of what I fill out every so often (Labelled: Template for help). I typically take the raw data (Example also attached, Labelled: Example raw data for help), and fill in all of the blank spaces on the template. The template includes some cells that never change (Row 9, for instance, will always stay the same as it is referencing prior year spent). I included 1 row from the raw data as an example, but there are typically 30,000+ lines of data. The raw data can be filtered by different numbers. So each building, office, person, card etc. has a number assigned to it, and you can filter by 'Building, Office #, Personnel # and a couple of other identifying numbers to get to the rows (Or lines) that you are needing to get the numbers you need for the Template. I have a pivot table that usually pulls only the info I need, for ease of finding it, but that still includes quite a bit of research and adding numerous numbers together. I am wanting to find a way to automate this, some way that the 'Template' can be automatically filled out (updated when a new report is pulled), but keep the same look and template of the Template attached, graph and all. To add more information, each table on the Template is different. So the top one shows the total spent by each office, using the Office #. The table to the right is what each Account (Or person for instance) spent, and the table on the bottom shows what each office spent, but broken apart by which card they were using.
Is there any possible way for this to be done? I think I could possibly completely recreate the Template, using PowerQuery, but to get it to match exactly seems like an actual struggle. Is there a better way that I don't know about?
7 Replies
I guess not exactly the same but close to it template could be generated using Power Query, but that all depend on concrete data. How to define parameters, now you have 6 offices, could it be 60 of them, etc.
- The template will always stay the same. As in always the same offices and stuff. Only the blank cells where the $ amounts go will ever change. Unless we get a new office in the future, which I will be able to handle at that time.
If so that's mainly how to format structured tables returned by Power Query. For that you actually don't need Power Query, you may transform ranges which are now in the template into structured table and play with formatting.
Next step is how to transform raw data by Power Query. Most probably that's possible, but without concrete sample with all details it's hard to say something more concrete.
