Nov 16 2021 09:32 AM
Nov 16 2021 09:32 AM
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?
Nov 16 2021 12:05 PM
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.
Nov 16 2021 12:39 PM
Nov 16 2021 01:23 PM
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.
Nov 16 2021 05:22 PM
Nov 17 2021 01:20 AM
Formatting of current data as the table is only to play with design, actually you will need to format tables returned by Power Query. If you see that new design meets your need, you re-apply it to Power Query tables. To illustrate feasibility I converted one of ranges to table
Power Query requires to work if not with actual when with more or less realistic data. Unfortunately I can't take such project, lot of other work.
Nov 17 2021 04:45 AM