In the attached:
1. The job # sheets' data have all been converted to proper Excel tables with the table names all starting with JobNo. This is important because the Power Query query ignores any other tables you might have. If you have new data to add to any of these sheets (new rows or columns) then that new data must be included in the tables. Any new job sheets you might add should also have their data converted to Tables and those tables' names should start with JobNo.
2. There's a Power Query query which consolidates these tables and transforms the data into a form suitable for creating a Pivot table from. The PQ query actually outputs directly to the pivot table.
3. It's important to know that the data is added to the Data Model, since this allows the generation of the WhereFrom columns.
4. It's normal built-in behaviour of a pivot table to be able to double-click on a cell which shows hours, for it to produce a new sheet showing the origin of the data in the cell you double-clicked on.
5. Pivot tables need refreshing if the data they're based on changes. You can do this by clicking on the Refresh all icon in the Queries & Connections section of the Data tab or by right-clicking the pivot table and choosing Refresh.
If you need more info, comeback.
|