Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register 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:
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:
Great! Can't wait to see the blog!!
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@dbadmin DA-BAAAAAD-Administrator!!!
That user name is awesome - I can never read it as db-admin though.
Anyway you are on a roll! There's no stopping you! Waiting to see the blog...
@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)
@Greg_Deckler I've got a meeting at 9am this morning - but will be tackling this the rest of the day - Do you have a screenshot by chance of the results produced from this solution? That would be a quick gauge for me if it's doing what it needs to -
This is done in the Advanced Query Editor correct? I'm not very familiar with using that yet. But I'll give it a go. 🙂
Just a side note: @Greg_Deckler @ImkeF @Sean - I can't thank you guys enough for helping me tackle these different problems. I'm learning a lot from you all - and I get a little bit further in the problem solving each time before I have to start asking for help. But I am tremendously grateful! 🙂 No one else in my company is familiar with this stuff (at all); not even the Power Query and such on the Excel side - so having you all and the community to work with has been invaluable. 🙂
THANK YOU!
If @Greg_Deckler s suggestion doesn't work, check out this:
M is sometimes behaving nasty with sorted results: Simply doesn't keep them.
You need to put a "Table.Buffer" around the step that applies the sorting (and sometimes also the following steps until you reach the step where the sorted results are used.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hello @ImkeF! 🙂 If there is a link in the message, I think it's broken (@ Check out this:)
I'm going to tackle @Greg_Deckler solution after my meeting this morning. So I'll be able to give feedback probably before lunch - if I can get it working properly. I'm not very familiar with using the Advanced Query Editor yet. But I've got some DAX and Power BI books so I'll be breaking those in some more. :). I'm seriously thinking about getting a book on M.
THANK YOU! 🙂
Will get back with you all asap!
Sorry - that didn't paste very clean - here's an image...
What is your formual for parsing out the Identifiers, like the Start and Stop events? Can you just post your formula for them?
In my original attempt I duplicated the data set, did a simple filter for '03' stop events in one query and '04' start event in the other query. Then Merged the queries from there.
I didn't create a formula to parse those two.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
88 | |
86 | |
82 | |
65 | |
49 |
User | Count |
---|---|
138 | |
110 | |
104 | |
66 | |
64 |