Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Solved! Go to 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.
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.
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?
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.
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 !!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |