Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gavinf4444
Frequent Visitor

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

2 REPLIES 2
gavinf4444
Frequent Visitor

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

v-yadongf-msft
Community Support
Community Support

Hi @gavinf4444 ,

 

You can append tables in Power Query like below:

vyadongfmsft_0-1667378736489.png

 

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

vyadongfmsft_1-1667378876687.png

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.