March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
I may not have labeled this question correctly. I know what I'm trying to accomplish but I haven't quite figured out how to explain it. So I'll try to show you instead. 🙂
I have an "Event" View- I've done the base filtering on the database side - Here's some copy and paste Sample Data:
RowID ID DateTime Event Rotation
857772 | 2 | 2015-12-04 11:12:23 | 03 | 683536 |
857773 | 1 | 2015-12-04 11:12:25 | 04 | 38875664 |
857774 | 2 | 2015-12-04 11:12:36 | 04 | 683537 |
857775 | 1 | 2015-12-04 11:12:37 | 03 | 38875770 |
857776 | 1 | 2015-12-04 11:12:56 | 04 | 38875771 |
857778 | 1 | 2015-12-04 11:13:36 | 03 | 38876354 |
857779 | 1 | 2015-12-04 11:13:36 | 04 | 38876355 |
857781 | 2 | 2015-12-04 11:14:08 | 03 | 684939 |
857782 | 1 | 2015-12-04 11:14:08 | 03 | 38876800 |
857783 | 1 | 2015-12-04 11:14:08 | 04 | 38876801 |
857785 | 2 | 2015-12-04 11:14:09 | 04 | 684940 |
857786 | 1 | 2015-12-04 11:14:45 | 03 | 38877327 |
857787 | 1 | 2015-12-04 11:14:46 | 04 | 38877328 |
857789 | 1 | 2015-12-04 11:14:56 | 03 | 38877420 |
857790 | 1 | 2015-12-04 11:14:56 | 04 | 38877421 |
857792 | 1 | 2015-12-04 11:15:06 | 03 | 38877511 |
857793 | 1 | 2015-12-04 11:15:07 | 04 | 38877512 |
857795 | 1 | 2015-12-04 11:15:14 | 03 | 38877529 |
857796 | 1 | 2015-12-04 11:15:58 | 04 | 38877530 |
857797 | 1 | 2015-12-04 11:16:07 | 03 | 38877604 |
Data Modeling Goals -
Ideally I need a Start time (04s) with the related time stamp and then add the Stop column next to it (03s) with it's related timestamp.
Using a subset of the data provided above:
RowID ID DateTime Event Rotation
857787 | 1 | 2015-12-04 11:14:46 | 04 | 38877328 |
857789 | 1 | 2015-12-04 11:14:56 | 03 | 38877420 |
857790 | 1 | 2015-12-04 11:14:56 | 04 | 38877421 |
857792 | 1 | 2015-12-04 11:15:06 | 03 | 38877511 |
857793 | 1 | 2015-12-04 11:15:07 | 04 | 38877512 |
857795 | 1 | 2015-12-04 11:15:14 | 03 | 38877529 |
ideally it would look like this:
RowID ID Date StartTime StartEvent StopTime Stop Event StartRotation StopRotation
857787 1 2015-12-04 11:14:46 04 11:14:56 03 38877328 38877420
So on and so forth - I really don't even need the Start and Stop Event Columns.
Thinking on it - because of our second shift - I may need a StartDate and a Stop Date in case the start time is on day and the stop time is after midnight.
The reason for this particular structure is for filterable reporting and calcuating how much "off time" there was versus "on time".
I have tried to do a Merge Queries - and ended up with this -
But it's not working quite the way I want it to when I try to create a visual.
When I try to place in a simple grid visual - I get this -
It's almost like there's some funky filtering going on that I'm not seeing - you can't have a stop without a start event.
Using an index column - the times still don't match up properly -
Any ideas on how to do this a more effective / efficient way would be extremely helpful!! Thanks in advance!
~H
Solved! Go to Solution.
@dbadmin - This is a DAX way of doing it, but not sure it is doing what you want.
Using the sample data provided, Two queries:
StartTimes
let Source = Csv.Document(File.Contents("C:\temp\powerbi\downtime\downtime.txt"),[Delimiter=" ", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"RowID", Int64.Type}, {"ID", Int64.Type}, {"DateTime", type datetime}, {"Event", Int64.Type}, {"Rotation", Int64.Type}, {"TagID", Int64.Type}}), #"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type",1), #"Filtered Rows" = Table.SelectRows(#"Removed Bottom Rows", each ([Event] = 3)), #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1) in #"Added Index"
StopTimes
let Source = Csv.Document(File.Contents("C:\temp\powerbi\downtime\downtime.txt"),[Delimiter=" ", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"RowID", Int64.Type}, {"ID", Int64.Type}, {"DateTime", type datetime}, {"Event", Int64.Type}, {"Rotation", Int64.Type}, {"TagID", Int64.Type}}), #"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type",1), #"Filtered Rows" = Table.SelectRows(#"Removed Bottom Rows", each ([Event] = 4)), #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1) in #"Added Index"
In StartTimes table create new column:
TimeUp = DATEDIFF([DateTime],RELATED(StopTimes[DateTime]),SECOND)
You could then create a column like:
Hour = Hour([DateTime])
The issue I see here is that if you end up with TimeUp that might span hours. It starts before the hour and then ends after the hour. Is that the problem you are running into or are you having a different issue? Does this come close to what you are going for?
@dbadmin Here's what I did - let me know if this is what you are trying to do.
Create a Query Event3 - uncheck Enable Load - get rid of 4 and add index
Create a Query Event4 - uncheck Enable Load - get rid of 3 and add Index
Create Blank Query Result (type = Event3 in formula bar) - then Merge this with Event4 => Close and Apply
As you can see in the Resulting Table they seem to remain sorted and you can calculate the difference
Let me know if this works!
If its giving you an error both ways then there is definitely an issue with your data
Meaning if you subtract the columns manually you will get some negative durations (implying time travel)
@dbadmin try using the DATEDIFF function on the Date/Time columns (instead of just the time)
Look at this picture... I get an error for the same times when using time only
but date/time is fine (note both are in the same order)
Final Answer - @Sean @ImkeF @Greg_Deckler
There was some corrupt data, so here's what I did -
I just started blogging for Power BI (my first post comes out later this week or first of next) I think this might be a great one to put up next? Are you guys ok with that? 😄 @Sean @ImkeF @Greg_Deckler??
EDIT -
Just double checked data on Uptime in Minutes and Hours and to get a more accurate time it's better to do it this way:
UpTime in Seconds = DATEDIFF(Results[Start.TimeOfEvent],Results[TimeOfEvent],SECOND)
UpTime in Minutes = Results[UpTime in Seconds] / 60
UpTime in Hours = Results[UpTime in Minutes] / 60
Also Make sure the Data type is Decimal for at least the Uptime in Hours - I showed 2 Decimal Places. Double checked the numbers and it's a lot more accurate than using the DateDiff for Minutes and Hours.
Here's what the Final Raw Data looks like:
Hi, my first question is, how do you know which start time goes with which stop time? Is there a common ID that I am missing or are things sequential such that there is a start time and then a stop time for that event is the next row?
In the original dataset they fall in sequential order based on the time. Here's a sample:
1364435 | 1 | 2016-05-16 05:28:00 | 03 | 88469024 | 1 |
1364436 | 1 | 2016-05-16 05:28:00 | 04 | 88469024 | 1 |
1364437 | 1 | 2016-05-16 05:28:00 | 06 | 88469025 | 1 |
1364439 | 1 | 2016-05-16 05:28:41 | 03 | 88469612 | 1 |
1364440 | 1 | 2016-05-16 05:28:41 | 04 | 88469612 | 1 |
1364441 | 1 | 2016-05-16 05:28:41 | 06 | 88469613 | 1 |
1364448 | 1 | 2016-05-16 05:29:46 | 03 | 88470570 | 1 |
1364450 | 1 | 2016-05-16 05:31:29 | 04 | 88470571 | 1 |
1364451 | 1 | 2016-05-16 05:31:44 | 03 | 88470717 | 1 |
1364452 | 1 | 2016-05-16 05:31:44 | 04 | 88470718 | 1 |
1364453 | 1 | 2016-05-16 05:31:44 | 06 | 88470718 | 1 |
1364468 | 1 | 2016-05-16 05:34:27 | 03 | 88473259 | 1 |
1364477 | 1 | 2016-05-16 05:37:05 | 04 | 88473260 | 1 |
1364480 | 1 | 2016-05-16 05:38:30 | 03 | 88474561 | 1 |
So in that example, I think I see events of the following in this order
03
04
06
03
04
06
03
04
03
04
06
03
04
03
Is that correct? And I assume that you are verifying that bottom row ends up being a "04" stop event so that there is a start and stop for each one. Assuming then that you pull all of the 03 events out and assign an index to each row and do the same for 04, then your index values should match I assume that is what you are going for?
Yes! That's exactly what I was going for - I indexed both queries so I can validate the correct stop / start times. In the Query Mode everything seemed great - even calculating the duration - but once I came out of Query Mode - I couldn't get where I wanted to from there.
Ultimately - all I'm trying to do is figure how much time ID 1 was running / "up" and how long it was down/ wasn't "running".
The final report that is desired is a Ratio -
Example: for the time period of 8am - 12pm, ID 1 was down 35 minutes and 45 seconds - of course you could do the "up time" instead of the "down time".
In my head it seems simple enough - but trying to pull it off the way the data comes to me is proving to be more difficult than I anticipated. I thought I was on the right track at first.
EDIT:
The 06 I will take back out - it has the same timestamp as the 04 and the 03. It's another event that triggers when ID is not running but in this case since the timestamps are the same - it won't be relevant. I was trying something different.
@dbadmin Is it time to ask you know who... I bet you she can do this in less than 5 minutes? That includes reading the question
I'll keep trying but...
@dbadmin - This is a DAX way of doing it, but not sure it is doing what you want.
Using the sample data provided, Two queries:
StartTimes
let Source = Csv.Document(File.Contents("C:\temp\powerbi\downtime\downtime.txt"),[Delimiter=" ", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"RowID", Int64.Type}, {"ID", Int64.Type}, {"DateTime", type datetime}, {"Event", Int64.Type}, {"Rotation", Int64.Type}, {"TagID", Int64.Type}}), #"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type",1), #"Filtered Rows" = Table.SelectRows(#"Removed Bottom Rows", each ([Event] = 3)), #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1) in #"Added Index"
StopTimes
let Source = Csv.Document(File.Contents("C:\temp\powerbi\downtime\downtime.txt"),[Delimiter=" ", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"RowID", Int64.Type}, {"ID", Int64.Type}, {"DateTime", type datetime}, {"Event", Int64.Type}, {"Rotation", Int64.Type}, {"TagID", Int64.Type}}), #"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type",1), #"Filtered Rows" = Table.SelectRows(#"Removed Bottom Rows", each ([Event] = 4)), #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1) in #"Added Index"
In StartTimes table create new column:
TimeUp = DATEDIFF([DateTime],RELATED(StopTimes[DateTime]),SECOND)
You could then create a column like:
Hour = Hour([DateTime])
The issue I see here is that if you end up with TimeUp that might span hours. It starts before the hour and then ends after the hour. Is that the problem you are running into or are you having a different issue? Does this come close to what you are going for?
@Greg_Deckler @ImkeF Ok - I've tried to implement it - oddly enough - it didn't give an error but it didn't produce any results either? Not sure what happened? Trying to investigate now
EDIT -
Ok - there was a relationship issue. Corrected that. The got a DateDiff function, the start date cannot be greater than the end date - error.
Tried to create the same column in the Stop Table. Tried to just do the Time frame and still got the same error. There must be some erroneous data in there somewhere or it's created when the tables are filtered...
Edit Number 2:
Here's the sorted data from the two filtered tables in Power BI:
TimeOfEvent event_type NewColumn.TimeOfEvent NewColumn.event_type
12/4/2015 11:04 4 12/4/2015 11:06 3
12/4/2015 11:06 4 12/4/2015 11:08 3
12/4/2015 11:08 4 12/4/2015 11:10 3
12/4/2015 11:10 4 12/4/2015 11:11 3
12/4/2015 11:11 4 12/4/2015 11:11 3
12/4/2015 11:11 4 12/4/2015 11:12 3
12/4/2015 11:12 4 12/4/2015 11:12 3
12/4/2015 11:12 4 12/4/2015 11:13 3
12/4/2015 11:13 4 12/4/2015 11:14 3
12/4/2015 11:14 4 12/4/2015 11:14 3
12/4/2015 11:14 4 12/4/2015 11:14 3
12/4/2015 11:14 4 12/4/2015 11:15 3
12/4/2015 11:15 4 12/4/2015 11:15 3
12/4/2015 11:15 4 12/4/2015 11:16 3
Here's the original data - this is how it comes to me - I filter out the other data to simplify it. I might be able to manipulate the data a different way on the database side to make it more workable - but i"m not sure how else I would do it...
Any thoughts / ideas?
Row ID ID DateTime Event Code
857740 1 2015-12-04 11:04:51 04
857741 1 2015-12-04 11:06:38 03
857742 1 2015-12-04 11:06:38 04
857747 1 2015-12-04 11:08:11 03
857748 1 2015-12-04 11:08:11 04
857760 1 2015-12-04 11:10:44 03
857761 1 2015-12-04 11:10:44 04
857763 1 2015-12-04 11:11:10 03
857766 1 2015-12-04 11:11:21 04
857767 1 2015-12-04 11:11:29 03
857770 1 2015-12-04 11:11:56 04
857771 1 2015-12-04 11:12:17 03
857773 1 2015-12-04 11:12:25 04
857775 1 2015-12-04 11:12:37 03
857776 1 2015-12-04 11:12:56 04
857778 1 2015-12-04 11:13:36 03
857779 1 2015-12-04 11:13:36 04
857782 1 2015-12-04 11:14:08 03
857783 1 2015-12-04 11:14:08 04
857786 1 2015-12-04 11:14:45 03
857787 1 2015-12-04 11:14:46 04
857789 1 2015-12-04 11:14:56 03
857790 1 2015-12-04 11:14:56 04
@dbadmin Here's what I did - let me know if this is what you are trying to do.
Create a Query Event3 - uncheck Enable Load - get rid of 4 and add index
Create a Query Event4 - uncheck Enable Load - get rid of 3 and add Index
Create Blank Query Result (type = Event3 in formula bar) - then Merge this with Event4 => Close and Apply
As you can see in the Resulting Table they seem to remain sorted and you can calculate the difference
Let me know if this works!
@Sean - Hey! 🙂 I'm giving your solution a go - but I'm having trouble getting the data into the Blank query...
the type = Event 3 isn't working. Tried to reference it in the Advanced Query Editor also - it's just not doing anything. Any ideas?
EDIT - I keep getting this error:
Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?
Although I think is related to M @ImkeF - if I'm not mistaken?
@dbadmin rename both without a space and then try without a space between Event and 3 => = Event3
Got it - I deleted it and started over. Thanks!! Now to finish this. 🙂
I did that....
If its giving you an error both ways then there is definitely an issue with your data
Meaning if you subtract the columns manually you will get some negative durations (implying time travel)
Si Senor... (I think I'm becoming delusional)...
EDIT - I had a thought...
Deleted COPY
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
91 | |
86 | |
77 | |
49 |
User | Count |
---|---|
164 | |
149 | |
101 | |
73 | |
56 |