Forum Discussion

gavinf4444's avatar
gavinf4444
Frequent Visitor
3 years ago

Create a timeline from different tables

Hello.  I am trying to create a timeline-style visual to show the period of operation for a shift and then any events within that.  I've looked at the "Matrix as Project Plan Visual" https://www.youtube.com/watch?v=R25QoiyoSVs and also the Craydec custom visual and I think that the former is the best fit for me.

Where I have problems is combining the various data sources so that everything is aligned.  I have 4 tables from various sources -

 

Machine #ShiftStartDateTimeEndDateTimeType
A12346A24/10/2022 04:19:1624/10/2022 16:18:11OPERATIONAL
Z654321A24/10/2022 05:59:1624/10/2022 17:28:11OPERATIONAL
Z654321B24/10/2022 20:59:1625/10/2022 00:28:11OPERATIONAL

 

Machine #ShiftStartDateTimeEndDateTimeType
A12346A24/10/2022 06:1624/10/2022 07:10PRODUCTION
A12346A24/10/2022 10:2024/10/2022 10:41PRODUCTION
A12346A24/10/2022 11:1924/10/2022 11:42PRODUCTION
A12346A24/10/2022 12:5824/10/2022 13:57PRODUCTION
A12346A24/10/2022 15:0424/10/2022 15:38PRODUCTION
A12346A24/10/2022 16:0424/10/2022 16:14PRODUCTION

 

Machine #ShiftStartDateTimeEndDateTimeType
A12346A24/10/2022 08:1224/10/2022 08:12COOLING
A12346A24/10/2022 10:1024/10/2022 10:18COOLING
A12346A24/10/2022 11:1124/10/2022 11:11COOLING
A12346A24/10/2022 12:0324/10/2022 12:48COOLING
A12346A24/10/2022 14:3124/10/2022 14:56COOLING

 

Machine #ShiftStartDateTimeEndDateTimeType
A12346A24/10/2022 07:08:0024/10/2022 07:09QUALITY CHECK
A12346A24/10/2022 09:13:0024/10/2022 09:13QUALITY CHECK
A12346A24/10/2022 10:32:0024/10/2022 10:32QUALITY CHECK
A12346A24/10/2022 11:35:0024/10/2022 11:35QUALITY CHECK
A12346A24/10/2022 13:48:0024/10/2022 13:48QUALITY CHECK
A12346A24/10/2022 15:31:0024/10/2022 15:31QUALITY CHECK

I cannot just append the first 3 tables as not only are there some rules in place but also I need to work out how to show the 'overlaps' between the events.

 

  • "OPERATIONAL" (Table 1) will always be the beginning and end date/time for a Shift.
  • The usual time order will be -
    • OPERATIONAL (Start)
    • PRODUCTION (Start)
    • QUALITY CHECK (Start)
    • QUALITY CHECK (End)
    • PRODUCTION (End)
    • XX
    • COOLING (Start)
    • COOLING (End)
    • XX
    • PRODUCTION (Start)
    • QUALITY CHECK (Start)
    • ......
    • OPERATIONAL (End)
  • with "XX" being the gap inbetween events but within the OPERATIONAL period.
  • Unfortuntately the sequence above isn't always kept as sometimes a PRODUCTION period will not register despite a QUALITY CHECK taking place (as shown in the data tables).

Where I need some guidance is how to either put these tables into 1, with possibly some calculated entries to ensure that there are no overlapping Start and End times, or if there is a better way to do it.  Thanks

Need Help

2 Replies

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Community Support
    3 years ago

    Hi gavinf4444 ,

     

    You can append tables in Power Query like below:

     

    You will get a table like this. Is this the result you want?

     

    Best regards,

    Yadong Fang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • gavinf4444's avatar
    gavinf4444
    Frequent Visitor
    3 years ago

    Hi, thanks for the reply.  I guess that for the Matrix Timeline to work, I need to have all of those events so that for each Machine, Shift & Date, the EndDateTime is to be calculated to be the minute before the next chronological StartDateTime, with the exception of the OPERATIONAL figure EndDateTime which will always be recorded as the end of the shift.

    Thanks