Forum Discussion
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 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 🙂
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.
5 Replies
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.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
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.
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?
