SOLVED

Power Pivot and power query help

Copper Contributor

I have this table 

Equipment IDStart Date (dd/mm/yyyy)Start TimeEnd Date (dd/mm/yyy)End TimeWork TypeDowntime
LD105/06/202218:00:0006/06/20226:00:00PM12.0
JB205/06/202218:00:0006/06/20226:00:00PM12.0
JB204/03/20226:00:0130/05/20226:00:00BD2,088.0
TR105/06/20226:00:0005/06/202218:00:00PM12.0
GR220/05/20226:00:0030/05/20226:00:00PM240.0

 

I want to be able to spread the downtime hours between days (each day is 24hrs) and how do i link it to a date table to do different filters.

 

I also have tables for Equipment ID and Work type

I have a Date table that i would like to link to this table so that i can filter by weeks, months, calendar days, fiscal periords

 

I am using power query

5 Replies
best response confirmed by Ferrol40 (Copper Contributor)
Solution

@Ferrol40 

Power Query to generate daily downtimes could be as in attached file.

Hello Sergei
Thanks for you solution 

@Ferrol40 , you are welcome

Hello Sergei,
kindly try this data. The formula doesn't work for the data. If i do a pivot table to verify it doesn't match. https://1drv.ms/x/s!Arbedp-azDVh9DyMbKoTPeg40SVZ?e=SP9B7T

Try the link

@Ferrol40 

What exactly doesn't work and which Excel version / platform do you use?

 

I added Power Query to your file, source is converted to structured table. Please check attached.

1 best response

Accepted Solutions
best response confirmed by Ferrol40 (Copper Contributor)
Solution

@Ferrol40 

Power Query to generate daily downtimes could be as in attached file.

View solution in original post