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

[N8_edc0]

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 ID	Start	End	Job Type
M1	03-05-19 00:00:00	03-05-19 01:00:00	Setup
M1	03-05-19 01:00:00	03-05-19 05:00:00	Process
M1	03-05-19 06:00:00	03-05-19 07:00:00	Setup
M1	03-05-19 07:00:00	03-05-19 11:00:00	Process
M2	03-05-19 00:00:00	03-05-19 02:00:00	Setup
M2	03-05-19 02:00:00	03-05-19 06:00:00	Process
M2	03-05-19 08:00:00	03-05-19 10:00:00	Process
M3	02-05-19 22:00:00	03-05-19 05:00:00	Process

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

	M1	M2	M3
03-05-19 00:00:00	Setup	Setup	Process
03-05-19 01:00:00	Process	Setup	Process
03-05-19 02:00:00	Process	Process	Process
03-05-19 03:00:00	Process	Process	Process
03-05-19 04:00:00	Process	Process	Process
03-05-19 05:00:00	-	Process	-
03-05-19 06:00:00	Setup	-	-
03-05-19 07:00:00	Process	-	-
03-05-19 08:00:00	Process	Process	-
03-05-19 09:00:00	Process	Process	-
03-05-19 10:00:00	Process	-	-
03-05-19 11:00:00	-	-	-

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

[Anonymous · Accepted solution]

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. 

[N8_edc0]

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 : 

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

[N8_edc0]

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

[Anonymous]

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

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