Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm trying to create a measure or calculated column that would give the amount of time, in minutes, that a person spent doing a task.
Here is a sample of the raw data:
TransID | PersonID | Job | TimeStamp |
255 | Sue | 100 | 7/20/19 10:00 PM |
257 | Ken | 101 | 7/20/19 10:01 PM |
258 | Kay | 112 | 7/20/19 10:01 PM |
259 | Bill | 101 | 7/20/19 10:02 PM |
260 | Ken | 100 | 7/20/19 10:03 PM |
261 | Joe | 101 | 7/20/19 10:03 PM |
262 | Bill | 120 | 7/20/19 10:03 PM |
263 | Matt | 101 | 7/20/19 10:04 PM |
264 | Bill | 100 | 7/20/19 10:04 PM |
265 | Don | 101 | 7/20/19 10:05 PM |
266 | Lee | 101 | 7/20/19 10:05 PM |
297 | Steve | 120 | 7/20/19 10:28 PM |
298 | Bill | 101 | 7/20/19 10:29 PM |
299 | Steve | 100 | 7/20/19 10:29 PM |
Here is an example of the output I would like:
Filter | Job | TimeStamp | Duration |
Bill | 101 | 7/20/19 10:02 PM | 00:01 |
120 | 7/20/19 10:03 PM | 00:01 | |
100 | 7/20/19 10:04 PM | 00:25 |
Other filters would give a similar list for that person.
I appriciate any pointers or assistance I can get.
Solved! Go to Solution.
Hi @pabeader ,
Based on your data posted at the beginning of this message, I managed to get the output as
Was this the output you were expecting?
Note that Bill Last TimeStamp was 20/7/2019 10:29:00 PM on the Job 101. As this is the last record and no more records for Bill, the duration is shown as blank > Meaning the job under WIP.
To do this I loaded the table and using Query Editor did the following steps.
let
Source = Csv.Document(File.Contents("C:\PowerBICommunity\sample.csv"),[Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"TransID", Int64.Type}, {"PersonID", type text}, {"Job", Int64.Type}, {"TimeStamp", type datetime}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"PersonID", Order.Ascending}, {"TimeStamp", Order.Ascending}}),
#"Partition" = Table.Group(#"Sorted Rows", {"PersonID"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition1" = Table.ExpandTableColumn(Partition, "Partition", {"TransID", "Job", "TimeStamp", "Index"}, {"TransID", "Job", "TimeStamp", "Index"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Partition1",{{"TimeStamp", type datetime}, {"Index", Int64.Type}}),
#"Sorted Rows1" = Table.Sort(#"Changed Type1",{{"PersonID", Order.Ascending}, {"TimeStamp", Order.Ascending}})
in
#"Sorted Rows1"
This basically
a) sorts the raw data by PersonId, TImeStamp
b) Creates Index by PersonId
Then Created a calculated column TimeTaken
Hi @pabeader,
COuld you please share breif on how to Calculate "Duration" column in expected output table?
An example of the calculation would be:
Filter by Bill so that you have a list of his entries.
When he goes from job 101 to job 120. Use the Timestamp at 120 as the 'end' time for the 101 entry. The duration is the 'end' timestamp minus the timestamp at 101. Then when he goes from 120 to 100, use the Timestamp at 100 as the 'end' time for the 120 entry. Continue this until you run out of entries.
Note: There are no overlaps, there should be no repeats and there is no 'missing' time. The start of a job is the end of the previous one.
So filtered Bill might look like this:
TransID | PersonID | Job | TimeStamp |
259 | Bill | 101 | 7/20/19 10:02 PM |
262 | Bill | 120 | 7/20/19 10:03 PM |
264 | Bill | 100 | 7/20/19 10:04 PM |
298 | Bill | 101 | 7/20/19 10:29 PM |
300 | Bill | 100 | 7/20/19 10:31 PM |
323 | Bill | 101 | 7/20/19 10:56 PM |
326 | Bill | 120 | 7/20/19 10:58 PM |
331 | Bill | 100 | 7/20/19 11:01 PM |
362 | Bill | 101 | 7/20/19 11:27 PM |
365 | Bill | 120 | 7/20/19 11:28 PM |
369 | Bill | 111 | 7/20/19 11:30 PM |
And Final table would look lke this:
Filter | Job | TimeStamp | Duration |
Bill | 101 | 7/20/19 10:02 PM | 00:01 |
120 | 7/20/19 10:03 PM | 00:01 | |
100 | 7/20/19 10:04 PM | 00:25 | |
101 | 7/20/19 10:29 PM | 00:02 | |
100 | 7/20/19 10:31 PM | 00:25 | |
101 | 7/20/19 10:56 PM | 00:02 | |
120 | 7/20/19 10:58 PM | 00:03 | |
100 | 7/20/19 11:01 PM | 00:26 | |
101 | 7/20/19 11:27 PM | 00:01 | |
120 | 7/20/19 11:28 PM | 00:02 | |
111 | 7/20/19 11:30 PM | 00:06 |
Hi @pabeader ,
Based on your data posted at the beginning of this message, I managed to get the output as
Was this the output you were expecting?
Note that Bill Last TimeStamp was 20/7/2019 10:29:00 PM on the Job 101. As this is the last record and no more records for Bill, the duration is shown as blank > Meaning the job under WIP.
To do this I loaded the table and using Query Editor did the following steps.
let
Source = Csv.Document(File.Contents("C:\PowerBICommunity\sample.csv"),[Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"TransID", Int64.Type}, {"PersonID", type text}, {"Job", Int64.Type}, {"TimeStamp", type datetime}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"PersonID", Order.Ascending}, {"TimeStamp", Order.Ascending}}),
#"Partition" = Table.Group(#"Sorted Rows", {"PersonID"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition1" = Table.ExpandTableColumn(Partition, "Partition", {"TransID", "Job", "TimeStamp", "Index"}, {"TransID", "Job", "TimeStamp", "Index"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Partition1",{{"TimeStamp", type datetime}, {"Index", Int64.Type}}),
#"Sorted Rows1" = Table.Sort(#"Changed Type1",{{"PersonID", Order.Ascending}, {"TimeStamp", Order.Ascending}})
in
#"Sorted Rows1"
This basically
a) sorts the raw data by PersonId, TImeStamp
b) Creates Index by PersonId
Then Created a calculated column TimeTaken
This solution works with just a tiny bit of tweaking.
When you created the table query you named it WorkingTime but I can't see where that was noted. Only matters when we get to the calculated column part of the solution. Once I caught that, it worked great!!
As far as the actual solution goes. Good gravy! It's going to take me a few days to figure out how it actually works. But it sure works.
Thanks for the good work!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |