Relating time data with different granularity (OEE figures)

[emarc1]

Hello.

We collect overall equipment effectiveness (OEE) data for several of our chemical filling lines. 

Data is handwritten onto a sheet by the line operatives and then later transposed into a digital version. Here's an example Excel file: https://drive.google.com/file/d/1AAmerEVuZnnbAWssceVibFFdwfaRjXpt/view?usp=sharing

The sheet has two main parts... The first captures the time spent with the line operational or otherwise (eg. breaks or part of the machine has broken down) broken down at the 10 minute level. The second captures batch details, start/stop times and the number of bottles filled. As there's a sheet for each line, I have the data pulled by simple formulas into two separate sheets in a more Power BI import friendly format.

The folder of spreadsheets is pulled into Power BI.

Each sheet is depivoted and merged into two large tables with all data from all the files.

One table contains the filling line status data at the ten minute granularity and another has the batch filling data with start/stop times and number of bottles filled, etc.

I can relate these two tables by date, shift and filling line and get basic insights but I'm not sure how best to do more advanced things... For example: 

We have the start and end times for a batch so we can calculate the bottles per minute for a filling line or for a batch but this doesn't account for if a batch runs across a break or if there's downtime. How do I view how many bottles per minute we get for each product on a filling line, only while the line was operating.

I understand that this 10 minute granularity thing is a bit of a pain. The reasoning behind the sheet's layout it is that it's very easy to write into and visually, line operatives and managers can quickly see what sort of shift we've had. Is there a way to use it to get the information I'm looking for without doing manual calculations?

Hope someone can help! Many thanks.

[v-jiascu-msft]

Hi @emarc1,

What's your expected result? I import the two sheets into Power BI Desktop. But I didn't find a proper relationship between these two tables. Please share the pbix file if you can. Please mask your private info first.

Best Regards,

Dale