Data Analysis - Pivot Table

Hi Andrew,

Thank you for sending the Trays Driver Analysis spreadsheet and an explanation of what you want to achieve ie. % Trays Out by Trays In statistics per driver for each week.

This is no easy task looking at your data as the totals for each driver work across the multiple columns of daily trays in/out. Also, the drivers do not always do the same run. Ideally what you need is to have 3 columns showing Driver List, No. of Trays In and No. of Trays Out in order to analyse each drivers' total stats in a Pivot Table. Then you can create a calculated field to display % Trays Out by Trays In.

So to get your data structured to these 3 columns you will need to use Power Query which is available within Excel (new versions of Office 365 but also available in older versions as an add-in). This amazing tool can transform your data to how you need it to look and then you can output the changed structure to a Pivot Table to analyse your data.

Here is a Youtube link to an introduction to Power Query:

https://www.youtube.com/watch?v=L4BuUzccLpo

Also, STL provides training in Power Query which you may want to consider

Hope this helps

Kind regards
Martin Sutherland
(IT Trainer)