cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
N8_edc0 Frequent Visitor
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

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

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
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

N8_edc0 Frequent Visitor
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?

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
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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors