Hi
I am new to this so excuse! I have selected some data from an external data source via Power Query "Capacity Report" but I now need to format this data into a report. I have a column "resource_id" which in this data has three values - 1, 2 and 14 (each is a resource). I want to be able to produce a page that has a header record that is along the lines of:
Capacity Report for resource 1
Followed by the data for resource1 (in this case 10 records for different dates) then repeat for resource 2 etc. The final out to then look like:
Capacity Report for resource 1
18/05/2020 a b c d etc
19/05/2020 a b c d etc
etc
Capacity report for resource 2
18/05/2020 a b c d etc
19/05/2020 a b c d etc
etc
I have tried creating a pivot table (see sheet 5) and whilst it has the data layout correctly, I need the resource group to be expanded for all resouces and not by a filter.
I also need to do a calculation at the header level which is based on an average of one column - so sum one column and divide by the number of records returned in the group.
I know these may be basic questions but just need some pointers. Is it possible to reference a cell from a power query in the final sheet?
Thanks for any assistance.
Hi Steve,
Welcome to our forum! Thanks for sharing your file. You can automatically generate a sheet for each resource ID using the "Show Report Filter Pages" tool as explained here: Extract sub-sets of data using a Pivot Table Report.
Yes, you can reference cells in the worksheet in Power Query, simply format the cells in a table, and load it to Power Query as a separate query, which you can then reference.
The average calculation may be possible in a PivotTable by setting the calculation to 'Average'. You can try it by right-clicking the value field in the PivotTable > Summarize values by > Average. Note: you can add the same field to the values area multiple times and set each one to a different calculation.
I hope that points you in the right direction. If the average calculation isn't what you mean, please provide an example in your file so we can see how you would calculate it manually.
Mynda
