cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
N8_edc0
Frequent Visitor

Time Table Summary from Start Time, End Time, and Job Type

Hi guys, I'm new to Power BI and currently stuck on this. 

 

I have a dataset of several lines of machines, its Start date/time, End date/time, and Job Type (Setup or Process). It looks like this

Machine IDStartEndJob Type
M103-05-19 00:00:0003-05-19 01:00:00Setup
M103-05-19 01:00:0003-05-19 05:00:00Process
M103-05-19 06:00:0003-05-19 07:00:00Setup
M103-05-19 07:00:0003-05-19 11:00:00Process
M203-05-19 00:00:0003-05-19 02:00:00Setup
M203-05-19 02:00:0003-05-19 06:00:00Process
M203-05-19 08:00:0003-05-19 10:00:00Process
M302-05-19 22:00:0003-05-19 05:00:00Process

 

I want to analyze the machine's utiliziation and need an output like this

 M1M2M3
03-05-19 00:00:00SetupSetupProcess
03-05-19 01:00:00ProcessSetupProcess
03-05-19 02:00:00ProcessProcessProcess
03-05-19 03:00:00ProcessProcessProcess
03-05-19 04:00:00ProcessProcessProcess
03-05-19 05:00:00-Process-
03-05-19 06:00:00Setup--
03-05-19 07:00:00Process--
03-05-19 08:00:00ProcessProcess-
03-05-19 09:00:00ProcessProcess-
03-05-19 10:00:00Process--
03-05-19 11:00:00---

 

I couldn't find how to do this anywhere. Any kind of help would be awesome ! Thank you 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
Joren_venema Resolver V
Resolver V

Re: Time Table Summary from Start Time, End Time, and Job Type

Hello @N8_edc0 

To do this you need to create a new row in the data for every hour between start and end.

Here is the code in the query editor to do this:

let
    // Loading in the sample table using enter data
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jVU0lEyMNY1MNU1tFQwMLACIxQxQ7hYcGpJaYFSrA6GNkMs2kzhYgFF+cmpxcXYNJph0WhO2D5zTG2GhtjtMyLoPyNs9hnhUILN6Xjss8DiUAPsGo1Bioygioyw2YglRGMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Machine ID" = _t, Start = _t, End = _t, #"Job Type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Machine ID", type text}, {"Start", type datetime}, {"End", type datetime}, {"Job Type", type text}}),
    //Add a list with a row for every hour between start and end
    #"Added Custom" = Table.AddColumn(#"Changed Type", "TMP hours", each {Number.From(0).. (Duration.Hours([End]-[Start])-1)}),
    #"Expanded TMP hours" = Table.ExpandListColumn(#"Added Custom", "TMP hours"),
    // Renaming the original columns to Temporary
    #"Renamed Columns" = Table.RenameColumns(#"Expanded TMP hours",{{"Start", "Start TMP"}, {"End", "End TMP"}}),
    //Add the amount of hours to the original column to get a new DateTime column for start and end
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Start", each [Start TMP] + #duration(0,[TMP hours],0,0)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "End", each [Start] + #duration(0,1,0,0)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Start", type datetime}, {"End", type datetime}}),
    //Cleaning up columns used to create the transformed table
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Machine ID", "Job Type", "Start", "End"})
in
    #"Removed Other Columns"

 If you have any question don't hesitate to ask.

 

Kind regards
Joren Venema

Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily. 

View solution in original post

5 REPLIES 5
Joren_venema Resolver V
Resolver V

Re: Time Table Summary from Start Time, End Time, and Job Type

Hello @N8_edc0 

To do this you need to create a new row in the data for every hour between start and end.

Here is the code in the query editor to do this:

let
    // Loading in the sample table using enter data
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jVU0lEyMNY1MNU1tFQwMLACIxQxQ7hYcGpJaYFSrA6GNkMs2kzhYgFF+cmpxcXYNJph0WhO2D5zTG2GhtjtMyLoPyNs9hnhUILN6Xjss8DiUAPsGo1Bioygioyw2YglRGMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Machine ID" = _t, Start = _t, End = _t, #"Job Type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Machine ID", type text}, {"Start", type datetime}, {"End", type datetime}, {"Job Type", type text}}),
    //Add a list with a row for every hour between start and end
    #"Added Custom" = Table.AddColumn(#"Changed Type", "TMP hours", each {Number.From(0).. (Duration.Hours([End]-[Start])-1)}),
    #"Expanded TMP hours" = Table.ExpandListColumn(#"Added Custom", "TMP hours"),
    // Renaming the original columns to Temporary
    #"Renamed Columns" = Table.RenameColumns(#"Expanded TMP hours",{{"Start", "Start TMP"}, {"End", "End TMP"}}),
    //Add the amount of hours to the original column to get a new DateTime column for start and end
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Start", each [Start TMP] + #duration(0,[TMP hours],0,0)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "End", each [Start] + #duration(0,1,0,0)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Start", type datetime}, {"End", type datetime}}),
    //Cleaning up columns used to create the transformed table
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Machine ID", "Job Type", "Start", "End"})
in
    #"Removed Other Columns"

 If you have any question don't hesitate to ask.

 

Kind regards
Joren Venema

Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily. 

View solution in original post

N8_edc0
Frequent Visitor

Re: Time Table Summary from Start Time, End Time, and Job Type

Hi Jorren,

 

Thank you for your response. 

I don't understand where to put the query editor code in? And is it possible to do it with DAX instead? Thank you

Highlighted
N8_edc0
Frequent Visitor

Re: Time Table Summary from Start Time, End Time, and Job Type

As of now I could make this in the vizualisation by creating a a table relationship between the Start Date/Time and a Date/Time table I made : 

 

relationship.pngHelp.PNG

 But I still couldn't populate the table with the Job Type according to the Start/End time. How do I do that?

Joren_venema Resolver V
Resolver V

Re: Time Table Summary from Start Time, End Time, and Job Type

@N8_edc0 

Unfortunately the only way to do it is by modifying the datasource.

If you go to edit queries => New source => blank query => advanced editor =>Replace everything by the code I provided, you can check what I did. After that you have to replicate the steps I did on the original table.

N8_edc0
Frequent Visitor

Re: Time Table Summary from Start Time, End Time, and Job Type

Hi Jorren,

 

I used your code with minor edits to increase granularity to minutes and whatnot, and it works great ! Thank you so much for your help !!

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors