Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
The goal is to get the "actual" view duration for each individual in a table. Example :
startedAt | finishedAt | timeSpent (minutes) | ActualtimeSpent (minutes) | ||
abc@gmail.com | 1:00:00PM | 2:00:00PM | 60 | 60 | |
abc@gmail.com | 1:15:00PM | 1:55:00PM | 40 | 0 | |
abc@gmail.com | 1:30:00PM | 2:05:00PM | 35 | 5 | |
abc@gmail.com | 2:15:00PM | 3:00:00PM | 45 | 45 | |
abc@gmail.com | 3:05:00PM | 3:30:00PM | 25 | 25 |
How am I able to get the actual time spent for everyone in my list?
Appreciate the help. Thanks in advance!
Solved! Go to Solution.
Hi @Keith011,
Sorry, I though that every process has its own start-stop.
This is the version with a common start-stop time restrictions:
let
Tasks = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVnLchQ5EPyVjT5z6JZaTYRv+wQWDJ714sUeDMz6gfEw+PH/hyVaWpRZWR2jCF9akVNVqspKleT1uvu5e9INB33//e+no8P5I9SP0HfnT/4HDfP6/S5/pB8faQkTKmaooHAwVgfxoE/VWwXReizecnw9gAa0FN24R7SUcKcpu/tFMwCWYrb0q4IwpgSgYcFSiek3tYQBzpjfGzB/NGMSZJiKW0Gh1KqAoqT7mXoDsyWTzyx9aP9gKaAl9p0AFIgn9WNESwF5AhkgUCKeAKi6o/VISauWRtxQwo+JQMkHQZ6YiRhg6gE0VksTma0gWn+KtCwpeG5rR+mfEBQWChwTgCJSZcldQnea8ec5DLIEKQAMicUocb9QYo7i7QXIkWagbO7PFksvW/rgVTsoLGRgRBC2I8ZUOuqVNjDkqWjTYYNcHNqQmAPZ22tHCaXJX2uTR2HTGyfh0uRHjjqLpSN1p6U7sjkmEFmK/UIuc0wrjUnPzJWTTNHeld01ZXxAUFroOnBH63RoThVkDk1Nwcr+mMRwrJZGan7aKoL2KOZKRJLyVEETBj5RTDnwv1ra7rildscqhpCngJb2NPBxZw9drDa4YzKSMlbQ3oHn74YuzxiiuFblrTXknRlvW9ruxKmKHHYnoiNOfU+0gcHS2ANoz3l/YnMcqY7Z0j8t58G7lt290zwp505b3J3a2g2OPJ2K2KCl0gdnLbp6ZtcHZy46U3nC0WbGrFu8rVs2t+4aptW13XQgWa0gJgeVKAEIjyVSxgqia8ZIpesBFBdAA4KQQfgxVRCv06ScQe/fOylfQoWFwpQDqKDGhRIjypbGEmEGRZ7sVVoLKmLag7RVQdGAmIQwM2pEVCIVZRQN7jJuF9Se+swoM7oPwqwZNWG6JofJ5w2Cfm7LRi0xVUNm4MbEVUtGF7FNs6UPLUfDB2UMVjO7+9hi6aMVguA8GXzSNOn1/JNawhIjiNQMy11B5no6CD03LbvLIJoSpWc2Wt8gudx0Mv/1wt9NS303timpq7K3fzXfeuxdtCTgQorlTJsX9sdhwZ1l+IKlPfP9haoPTnYZdNnQmZe2oMGRsUubPu++fGlD5TN9xlxpRPqycuUQTlTnyoYanFMhg/CCSANcBUW+J+i15EpSTAFWS6TciQQsW7pu0YHrloRf2/XoPHVca1X0APqsMelB/FnbABseQSQEvbTvjeNOJpYbYYfzdHajLNAnoRvLxeicm19aYsogOjJQYRFE8hxdEN+RnWTeakz6+nBr+3pwBPNWY5IBeNvSmxkUFuSiNMu202cModO2M0+m0RHMrcpc71oyF/0oXZ5BYaGBEUP3fNXCDMJbO8lcBe0dr7bzek8g0bmvWhV9MfjqyIUQZdciPDuHTRL4zpLAm8h3DsOlKrvOPokTLxFEF5wgu/vm7E507pusY4DZ25015I0EdzYz/OiJlkh69WJ2pwqml437Fn2+1x7Xw+6+kwuQPvk9OEogjwEPyrlBAn9QEmBHzZjHFoY/CsvouvQDY0de15B5fNGXh0dLS3pfLD2eQXRbDgsg4q7+g+DRrsv17/w/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Email = _t, startedAt = _t, finishedAt = _t, #"timeSpent (minutes)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Tasks,{{"Email", type text}, {"startedAt", type time}, {"finishedAt", type time}, {"timeSpent (minutes)", Int64.Type}}),
Schedule = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrIyMACiglylWJ1oJWMrYxgvFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Task Duration" = _t]),
ChangeType = Table.TransformColumnTypes(Schedule,{{"Task Duration", type time}}),
Join = Table.AddColumn(#"Changed Type", "data", each Table.Transpose(ChangeType, {"Start", "Stop"})),
#"Expanded ChangeType" = Table.ExpandTableColumn(Join, "data", {"Start", "Stop"}, {"Email.Start", "Email.End"}),
#"Added Custom" = Table.AddColumn(#"Expanded ChangeType", "latestStartedAt", each List.Max({[startedAt],[Email.Start]}), type time),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "earliestFinishedAt", each List.Min({[finishedAt], [Email.End]}),type time),
fCalculate = (t as table) =>
let
m = Table.Buffer(t),
fProcess = (a, n)=>
let
previousFinishedAt = List.Last(a)[earliestFinishedAt],
currentStartedAt = n[latestStartedAt],
currentFinishedAt = n[earliestFinishedAt],
actualFinish = List.Last(a)[actualFinish],
actualStart = List.Max({actualFinish, currentStartedAt}),
outputRecord = [#"ActualtimeSpent (minutes)" = List.Max({0, Duration.TotalMinutes(currentFinishedAt-actualStart)}), actualFinish = List.Max({actualFinish, currentFinishedAt})]
in outputRecord,
process = List.Skip(List.Accumulate(Table.ToRecords(m), {[actualFinish = m{0}[startedAt], #"finishedAt" = m{0}[startedAt]]}, (a, n)=> a & { n & fProcess(a, n) }))
in process,
Group = Table.Group(#"Added Custom1", "Email", {{"Data", fCalculate}}),
Output = Table.FromRecords(List.Combine(Group[Data]), Value.Type(Table.AddColumn(#"Changed Type", "ActualtimeSpent (minutes)", each null,type number)))
in
Output
Pleas let me know if this works for you.
Kind regards,
John
hey guys,
sorry i had to revisit this post again. I'm looking to expand this solution with an extended request.
Example below is the same example that I provided in my original request of this post. The only difference is that there is an additional "Task Type" column to identify "who is doing what task for how long".
My expected outcome is the same as before. Now the addtional request is how can I calculate the Actual Time Spent for each of them (email) base on the Task Type while using the Schedule Table to set the start and end time for each task
If I were to talk to Power Query, I would tell : Hey Power Query, please calculate the actual time spent for each of my worker (email) base on their task type and the Actual Time Spent should refer to the schedule table for when it starts and when it ends
@jbwtp might need to hijack your brain again. 😅
Task Table
startedAt | finishedAt | timeSpent (minutes) | ActualtimeSpent (minutes) | Task Type | |
abc@gmail.com | 1:00:00PM | 2:00:00PM | 60 | 60 | Task A |
abc@gmail.com | 1:15:00PM | 1:55:00PM | 40 | 0 | Task A |
abc@gmail.com | 1:30:00PM | 2:05:00PM | 35 | 5 | Task A |
abc@gmail.com | 2:15:00PM | 3:00:00PM | 45 | 45 | Task A |
abc@gmail.com | 3:05:00PM | 3:30:00PM | 25 | 25 | Task A |
def@gmail.com | 5:00:00PM | 6:00:00PM | 60 | 60 | Task B |
def@gmail.com | 5:15:00PM | 5:55:00PM | 40 | 0 | Task B |
def@gmail.com | 5:30:00PM | 6:05:00PM | 35 | 5 | Task B |
def@gmail.com | 6:15:00PM | 7:00:00PM | 45 | 45 | Task B |
def@gmail.com | 7:05:00PM | 7:30:00PM | 25 | 25 | Task B |
Schedule Table (note that this table format is slightly different from my original schedule table at the top)
Task Type | Start Time | End Time |
Task A | 1:00:00PM | 3:30:00PM |
Task B | 5:00:00PM | 7:30:00PM |
Hi @Keith011,
I think you need to revisit my earlier solution dated 2 Sep that had email specific start and end times.
Solved: Re: Calculate the actual time duration when there ... - Microsoft Power BI Community
Just adjust it to task-based rather than e-mail-based. This should do the job. Also change grouping in the Group step to group by Email and task, rather than just email.
Cheers,
John
Hi @Keith011 ,
Would you like a calculated column? If so, here's my solution.
ActualtimeSpent (minutes) =
VAR _MAX =
MAXX (
FILTER (
'Table',
'Table'[startedAt] < EARLIER ( 'Table'[startedAt] )
&& 'Table'[Email] = EARLIER ( 'Table'[Email] )
),
'Table'[finishedAt]
)
RETURN
IF (
'Table'[startedAt] < _MAX,
IF (
DATEDIFF ( _MAX, 'Table'[finishedAt], MINUTE ) < 0,
0,
DATEDIFF ( _MAX, 'Table'[finishedAt], MINUTE )
),
DATEDIFF ( 'Table'[startedAt], 'Table'[finishedAt], MINUTE )
)
Get the correct result.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-yanjiang-msft
Thanks for your solution. But it didn't quite work 100% , but it's almost there
The issue comes when some records have duplicate timestamps. The solution will double count the duration too. I'll need it to be 0 if that timestamp has already recorded like below
startedAt | finishedAt | timeSpent (minutes) | ActualtimeSpent (minutes) | |
abc@gmail.com | 1:00:00PM | 2:00:00PM | 60 | 60 |
abc@gmail.com | 1:00:00PM | 2:00:00PM | 60 | 0 |
abc@gmail.com | 1:00:00PM | 2:00:00PM | 60 | 0 |
I've attached an excel file as a sample let me know if you can access it.
Hi @Keith011 ,
I download your attached Excel and in the expected result seems there's a error, the first line of 8:00:00 should be 46 and the second line should be 7.
I modify the sample and here's my solution.
1.Add an index column in PQ.
2.Create a calculated column.
ActualtimeSpent (minutes) =
VAR _MaxFinish =
MAXX (
FILTER (
'Table',
'Table'[startedAt] < EARLIER ( 'Table'[startedAt] )
&& 'Table'[Email] = EARLIER ( 'Table'[Email] )
),
'Table'[finishedAt]
)
VAR _MinIndex =
MINX (
FILTER (
'Table',
'Table'[startedAt] = EARLIER ( 'Table'[startedAt] )
&& 'Table'[Email] = EARLIER ( 'Table'[Email] )
),
'Table'[Index]
)
VAR _MaxFinishSameSta =
MAXX (
FILTER (
'Table',
'Table'[Index] < EARLIER ( 'Table'[Index] )
&& 'Table'[startedAt] = EARLIER ( 'Table'[startedAt] )
&& 'Table'[Email] = EARLIER ( 'Table'[Email] )
),
'Table'[finishedAt]
)
RETURN
IF (
'Table'[startedAt] < _MaxFinish,
IF (
DATEDIFF ( _MaxFinish, 'Table'[finishedAt], MINUTE ) < 0,
0,
DATEDIFF ( _MaxFinish, 'Table'[finishedAt], MINUTE )
),
IF (
'Table'[Index] = _MinIndex,
DATEDIFF ( 'Table'[startedAt], 'Table'[finishedAt], MINUTE ),
IF (
DATEDIFF ( _MaxFinishSameSta, 'Table'[finishedAt], MINUTE ) < 0,
0,
DATEDIFF ( _MaxFinishSameSta, 'Table'[finishedAt], MINUTE )
)
)
)
Get the result.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I figure for the records that has duplicates I can just remove duplicates as a step, that should solve the problem. But how to solve records on Row 19, 22, 24, 25 in my excel where it should be 0 since the end time is already overlapping
any idea? anyone?
let
Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"startedAt", type time}, {"finishedAt", type time}, {"timeSpent (minutes)", Int64.Type}}),
Custom1 = Table.FromRecords(List.Accumulate(Table.ToRecords(#"Changed Type"),{{},[]},(x,y)=>let a=List.Times(y[startedAt],Duration.TotalMinutes(y[finishedAt]-y[startedAt]),#duration(0,0,1,0)),b=Record.FieldOrDefault(x{1},y[Email],{}),c=List.Union({a,b}),d=List.RemoveItems(a,b) in {x{0}&{y&[#"ActualTimeSpent(Minutes)"=List.Count(d)]},Record.TransformFields(x{1},{y[Email],each c},2)}){0})
in
Custom1
Thanks!!!! @wdx223_Daniel . I tried it with just 1 user and it worked! but when i try to run the same for my entire list it's taking forever to load. over 5 minutes and still loading, i cancelled the query. What's happening? My list has about 7000 rows of data in excel
P.S. i tried it on a 600 row data , it's taking forever as well
Hi @Keith011,
Would you like to try something like this?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVnLctQwEPwVymcOtmSZqtx4PwNZAoFkCbDkQciy5PH/ByjLRN3T41pV5WJV72g009MzUpbL5mFzv+l22vbf37293fEjlI/QNsf3/4M6WN9p091HSnegwKBYQAFBfQFFtAQgWo87XfnoWgB1aCm6jvfkKx51cvyRhgAsxWzpsYLQpwSgbsbS5NMTtYQOjpinFZhn1ZgEEabsFtC/9eQnbgr3c90tCQWeW/7Q+cFSQEu8dwJQIJ6Ujx4tBeQJRIBAiXgCoLIdrUcKWrHU44ESfgwESj4I4sRMRAdTC6C+WBrIbAHR+gOk5RSCFzZ3FP4BQWEmwTEBCMt6drs0IwR9sUQqQtKBGBKLXvx+qcTsZbcMIorDL6bDvaqx9LqmDt7Ug8JMBHoEYTmiT1NFvdEChjhN2rRbIRe71iXmQN7traOEUuRvtcijsOmdE3Ap8j1HncXSnm6nqduzMSYQWYrtTCyzTwv1SZvmwgmmaO/Cnpoi3iEozVQdbEfr1DSHAjJNU0OwsD8mMeyLpZ6Kn46KoC2KuRCRpDgV0ICOD+RTdvx9Tdnt1+RuX8UQ4hTQ0pYC3m9s08Vsw3ZMRlLGAto68HyoqPKMIYprVj5aQ17P+FhTdgdOVqTZHYiOOPk90AIGS30LoC39/sDGOFIes6VPNf3gc83pPmuclHOHNdsd2tx1jjwditigpakOjmp09ciud85cdKTyhKPNiFnW7LasOdyyqZhWl/bQgWS1gJgclKIEIGxLpIwFRNeMnlLXAijOgDoEIYPwYyggXqdJOYO+fHFCPocKM4mZGtCE6mdSjCibGkuEERR5sldpnVARwx6krCYUDYhJCDOiekQlUlFG0eAu4/aE2pKfEWVG906YNaIGDNfgMPm4QtCPbdqoJIZiyAzcGLhiyegilmm29LWmNXxVxmA283bfaix9s0IQnCeD7xomvZ5/V0uYYgSRmmG6C8hcTzuh56rmdBlEU6LUzErzGySWq0bmv1b4u6rJ78oWJVVV3u2Hxlvb3klNAE4kWc60eWJ/HGa2swyfsbRlvj9R9cHJLoNOKyrz1CY0ODJ2asPn3ZdPravc00fMmXqkLytnDuFEdc6sq8HpChmEF0Qa4Aoo8j1BryVnEmJysFgi5U4kYNnSeY0OnNcE/NyuR+ep41yzog3op/qkjfinlgEWPIJICFop3wtnO5lYLoQdztPZhbJAn4QuLBej0zd/1fiUQdQyUGERRPIcXRDfkZ1gXqpP+vpwaeu6cwTzUn2SAXhdU5sZFGbkYiqWdaPPGEKndWOeTKMjmGuVuda1ZC76Uao8g8JMASOG7vmqhRmEt3aSuQLaOl6tx/WWQKJzvzUr+mLw25ELIcqmRng2DpvE8Y0lgTeRbxyGS1Y2jX0SJ14iiC44QU73xzmd6NwfWUcH825X1pA3ElzZyPCjJ1oi6dWL2ZUqmF42rmv0+VprXJvddSMXIH3yu3GUQB4DbpRznTh+oyTAihoxtzUMvxWW0XXpDmNHXteQeXzRl4dbS0t6X5xqPIPothxmQMRd/QfBrV2X69/xXw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Email = _t, startedAt = _t, finishedAt = _t, #"timeSpent (minutes)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Email", type text}, {"startedAt", type time}, {"finishedAt", type time}, {"timeSpent (minutes)", Int64.Type}}),
fCalculate = (t as table) =>
let
m = Table.Buffer(t),
fProcess = (a, n)=>
let
previousFinishedAt = List.Last(a)[finishedAt],
currentStartedAt = n[startedAt],
currentFinishedAt = n[finishedAt],
actualStart = List.Max({previousFinishedAt, currentStartedAt}),
outputRecord = [#"ActualtimeSpent (minutes)" = Duration.TotalMinutes(currentFinishedAt-actualStart)]
in outputRecord,
process = List.Skip(List.Accumulate(Table.ToRecords(m), {[#"finishedAt" = m{0}[startedAt]]}, (a, n)=> a & { n & fProcess(a, n) }))
in process,
Group = Table.Group(#"Changed Type", "Email", {{"Data", fCalculate}}),
Output = Table.FromRecords(List.Combine(Group[Data]), Value.Type(Table.AddColumn(#"Changed Type", "ActualtimeSpent (minutes)", each null,type number)))
in
Output
I kept it quite detailed, so it was clearer what am I doing, but, of course, everything below "Changed Type" can be condensed.
Kind regards,
John
Hi @Keith011,
I just noticed that this does not perform well where a subsequent task completes before a preceeding task, e.g.
In the eexample above it would show 10 min on the last line, whereas theoretically, it should be 5. Let me know is this is a problem, it is reasonably somple to fix with adding one more field containing "actualFinish".
Cheers,
John
This is an updated version:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVnLchQ5EPyVjT5z6JZaTYRv+wQWDJ714sUeDMz6gfEw+PH/hyVaWpRZWR2jCF9akVNVqspKleT1uvu5e9INB33//e+no8P5I9SP0HfnT/4HDfP6/S5/pB8faQkTKmaooHAwVgfxoE/VWwXReizecnw9gAa0FN24R7SUcKcpu/tFMwCWYrb0q4IwpgSgYcFSiek3tYQBzpjfGzB/NGMSZJiKW0Gh1KqAoqT7mXoDsyWTzyx9aP9gKaAl9p0AFIgn9WNESwF5AhkgUCKeAKi6o/VISauWRtxQwo+JQMkHQZ6YiRhg6gE0VksTma0gWn+KtCwpeG5rR+mfEBQWChwTgCJSZcldQnea8ec5DLIEKQAMicUocb9QYo7i7QXIkWagbO7PFksvW/rgVTsoLGRgRBC2I8ZUOuqVNjDkqWjTYYNcHNqQmAPZ22tHCaXJX2uTR2HTGyfh0uRHjjqLpSN1p6U7sjkmEFmK/UIuc0wrjUnPzJWTTNHeld01ZXxAUFroOnBH63RoThVkDk1Nwcr+mMRwrJZGan7aKoL2KOZKRJLyVEETBj5RTDnwv1ra7rildscqhpCngJb2NPBxZw9drDa4YzKSMlbQ3oHn74YuzxiiuFblrTXknRlvW9ruxKmKHHYnoiNOfU+0gcHS2ANoz3l/YnMcqY7Z0j8t58G7lt290zwp505b3J3a2g2OPJ2K2KCl0gdnLbp6ZtcHZy46U3nC0WbGrFu8rVs2t+4aptW13XQgWa0gJgeVKAEIjyVSxgqia8ZIpesBFBdAA4KQQfgxVRCv06ScQe/fOylfQoWFwpQDqKDGhRIjypbGEmEGRZ7sVVoLKmLag7RVQdGAmIQwM2pEVCIVZRQN7jJuF9Se+swoM7oPwqwZNWG6JofJ5w2Cfm7LRi0xVUNm4MbEVUtGF7FNs6UPLUfDB2UMVjO7+9hi6aMVguA8GXzSNOn1/JNawhIjiNQMy11B5no6CD03LbvLIJoSpWc2Wt8gudx0Mv/1wt9NS303timpq7K3fzXfeuxdtCTgQorlTJsX9sdhwZ1l+IKlPfP9haoPTnYZdNnQmZe2oMGRsUubPu++fGlD5TN9xlxpRPqycuUQTlTnyoYanFMhg/CCSANcBUW+J+i15EpSTAFWS6TciQQsW7pu0YHrloRf2/XoPHVca1X0APqsMelB/FnbABseQSQEvbTvjeNOJpYbYYfzdHajLNAnoRvLxeicm19aYsogOjJQYRFE8hxdEN+RnWTeakz6+nBr+3pwBPNWY5IBeNvSmxkUFuSiNMu202cModO2M0+m0RHMrcpc71oyF/0oXZ5BYaGBEUP3fNXCDMJbO8lcBe0dr7bzek8g0bmvWhV9MfjqyIUQZdciPDuHTRL4zpLAm8h3DsOlKrvOPokTLxFEF5wgu/vm7E507pusY4DZ25015I0EdzYz/OiJlkh69WJ2pwqml437Fn2+1x7Xw+6+kwuQPvk9OEogjwEPyrlBAn9QEmBHzZjHFoY/CsvouvQDY0de15B5fNGXh0dLS3pfLD2eQXRbDgsg4q7+g+DRrsv17/w/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Email = _t, startedAt = _t, finishedAt = _t, #"timeSpent (minutes)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Email", type text}, {"startedAt", type time}, {"finishedAt", type time}, {"timeSpent (minutes)", Int64.Type}}),
fCalculate = (t as table) =>
let
m = Table.Buffer(t),
fProcess = (a, n)=>
let
previousFinishedAt = List.Last(a)[finishedAt],
currentStartedAt = n[startedAt],
currentFinishedAt = n[finishedAt],
actualFinish = List.Last(a)[actualFinish],
actualStart = List.Max({actualFinish, currentStartedAt}),
outputRecord = [#"ActualtimeSpent (minutes)" = List.Max({0, Duration.TotalMinutes(currentFinishedAt-actualStart)}), actualFinish = List.Max({actualFinish, currentFinishedAt})]
in outputRecord,
process = List.Skip(List.Accumulate(Table.ToRecords(m), {[actualFinish = m{0}[startedAt], #"finishedAt" = m{0}[startedAt]]}, (a, n)=> a & { n & fProcess(a, n) }))
in process,
Group = Table.Group(#"Changed Type", "Email", {{"Data", fCalculate}}),
Output = Table.FromRecords(List.Combine(Group[Data]), Value.Type(Table.AddColumn(#"Changed Type", "ActualtimeSpent (minutes)", each null,type number)))
in
Output
Hey it worked perfectly , you saved the day!!
Can I ask for 1 last tweak?
The same scenario but only consider the actualTimeSpent equals or before/after a certain time. For example: the work starts at 1:30PM to 3:00PM. so the below would be the "ActualTimeSpent"
startedAt | finishedAt | timeSpent (minutes) | ActualtimeSpent (minutes) | ||
abc@gmail.com | 1:00:00PM | 2:00:00PM | 60 | 30 | |
abc@gmail.com | 1:15:00PM | 1:55:00PM | 40 | 0 | |
abc@gmail.com | 1:30:00PM | 2:05:00PM | 35 | 5 | |
abc@gmail.com | 2:15:00PM | 3:00:00PM | 45 | 45 | |
abc@gmail.com | 3:05:00PM | 3:30:00PM | 25 | 0 |
P.S - the "work starts" time is pretty dynamic it can be from anytime to anytime so i actually store the info in another table (workStartTable) like below. So the "work starts" time gets updated according to the project
the 7:00PM to 8:30PM is just an example. Let's assume the work start time is 1:30PM - 3.00PM like in the example above
So I would imagine that your solution would include something that reads from this workStartTable to get the start time {0} and end time {1} then calculate the actualtimeSpent
So sorry for the last minute tweak.
thanks!
Hi @Keith011,
Sorry, I am away from my laptop until next week. [If I understand correctly what you want] Do you want to create two conditional columns to reset the start and end time? Kind of if [startTime] < 1:00pm then 1:00pm else[startTime] . And use them instead of the original columns?
Thanks,
John
Hmm... something like "only calculate the actualTimeSpent if the timestamps fall betweeen the work start time and work end time"
I store the work start time and work end time info in a seperate table (something like a lookup table, shown above). So whenever i want to calculate the actualTimeSpent from the raw data (the long list of emails with many overlap timestamps), it will refer to the lookup table to check the work start and end time, only then calculate the actualtimespent (like you did in your solution). if that make sense?
I think this should work, you can join and filter on the timeStart/timeEnd table before processing the rest with the code to calculate actualTime.
Hmm yeah understand your point but I really have no clue on how to do that simple step cause i'm really-really new at this. Really appreciate if you could show how it's done.
No worries, you can get back to me when you're back at your laptop next week 😅
Thanks, John!
Hi @Keith011,
sorry for the delay, for a cople of days I was out of connectivity reach.
This is the sample code for what (I think) you want:
let
Tasks = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVnLchQ5EPyVjT5z6JZaTYRv+wQWDJ714sUeDMz6gfEw+PH/hyVaWpRZWR2jCF9akVNVqspKleT1uvu5e9INB33//e+no8P5I9SP0HfnT/4HDfP6/S5/pB8faQkTKmaooHAwVgfxoE/VWwXReizecnw9gAa0FN24R7SUcKcpu/tFMwCWYrb0q4IwpgSgYcFSiek3tYQBzpjfGzB/NGMSZJiKW0Gh1KqAoqT7mXoDsyWTzyx9aP9gKaAl9p0AFIgn9WNESwF5AhkgUCKeAKi6o/VISauWRtxQwo+JQMkHQZ6YiRhg6gE0VksTma0gWn+KtCwpeG5rR+mfEBQWChwTgCJSZcldQnea8ec5DLIEKQAMicUocb9QYo7i7QXIkWagbO7PFksvW/rgVTsoLGRgRBC2I8ZUOuqVNjDkqWjTYYNcHNqQmAPZ22tHCaXJX2uTR2HTGyfh0uRHjjqLpSN1p6U7sjkmEFmK/UIuc0wrjUnPzJWTTNHeld01ZXxAUFroOnBH63RoThVkDk1Nwcr+mMRwrJZGan7aKoL2KOZKRJLyVEETBj5RTDnwv1ra7rildscqhpCngJb2NPBxZw9drDa4YzKSMlbQ3oHn74YuzxiiuFblrTXknRlvW9ruxKmKHHYnoiNOfU+0gcHS2ANoz3l/YnMcqY7Z0j8t58G7lt290zwp505b3J3a2g2OPJ2K2KCl0gdnLbp6ZtcHZy46U3nC0WbGrFu8rVs2t+4aptW13XQgWa0gJgeVKAEIjyVSxgqia8ZIpesBFBdAA4KQQfgxVRCv06ScQe/fOylfQoWFwpQDqKDGhRIjypbGEmEGRZ7sVVoLKmLag7RVQdGAmIQwM2pEVCIVZRQN7jJuF9Se+swoM7oPwqwZNWG6JofJ5w2Cfm7LRi0xVUNm4MbEVUtGF7FNs6UPLUfDB2UMVjO7+9hi6aMVguA8GXzSNOn1/JNawhIjiNQMy11B5no6CD03LbvLIJoSpWc2Wt8gudx0Mv/1wt9NS303timpq7K3fzXfeuxdtCTgQorlTJsX9sdhwZ1l+IKlPfP9haoPTnYZdNnQmZe2oMGRsUubPu++fGlD5TN9xlxpRPqycuUQTlTnyoYanFMhg/CCSANcBUW+J+i15EpSTAFWS6TciQQsW7pu0YHrloRf2/XoPHVca1X0APqsMelB/FnbABseQSQEvbTvjeNOJpYbYYfzdHajLNAnoRvLxeicm19aYsogOjJQYRFE8hxdEN+RnWTeakz6+nBr+3pwBPNWY5IBeNvSmxkUFuSiNMu202cModO2M0+m0RHMrcpc71oyF/0oXZ5BYaGBEUP3fNXCDMJbO8lcBe0dr7bzek8g0bmvWhV9MfjqyIUQZdciPDuHTRL4zpLAm8h3DsOlKrvOPokTLxFEF5wgu/vm7E507pusY4DZ25015I0EdzYz/OiJlkh69WJ2pwqml437Fn2+1x7Xw+6+kwuQPvk9OEogjwEPyrlBAn9QEmBHzZjHFoY/CsvouvQDY0de15B5fNGXh0dLS3pfLD2eQXRbDgsg4q7+g+DRrsv17/w/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Email = _t, startedAt = _t, finishedAt = _t, #"timeSpent (minutes)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Tasks,{{"Email", type text}, {"startedAt", type time}, {"finishedAt", type time}, {"timeSpent (minutes)", Int64.Type}}),
Schedule = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dVTJVoNAEPyVvDlzGAQC4aZx340aF4Ia9y0a/f+DzLB0VT+5zbyiuquqeygKs2oCE+bWDpaL6pQ0pzIozJpDwjyyg7mDUndqoHF1j/MwqUkxkNZrkm1II4A2qnvUdULSpiPZjpTkcdJCW54Ud51Ew3ZNqiBPigDaIU8plNuFLwOTgYY9Lpc17hy0r+UJ64A8ofDDmtSmh62OONgEWh1zfGjqRA1KWk36pZ8qkpQ7Y9IQ6p0zhKypF9UOPgVTF9V9pUOQc6kHJZ2uOAlUfs0Q6iso9BG0ms1qVtsLBZY8EFR4o/ZFVNySK9ykOzV5aTSnlcXp3vtqtts+UfCgIxfSIweBT+CJOuEDfaY9R68v5GgIyKvSLV7fiIMDfKfkcF0/1C9CDH3yc8qg3MKXi/4p90XiMLrv3riXvAxY7keHKqzfnv9K+Qc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Email = _t, Start = _t, End = _t]),
ChangeType = Table.TransformColumnTypes(Schedule,{{"Email", type text}, {"Start", type time}, {"End", type time}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Email"}, ChangeType, {"Email"}, "ChangeType", JoinKind.LeftOuter),
#"Expanded ChangeType" = Table.ExpandTableColumn(#"Merged Queries", "ChangeType", {"Start", "End"}, {"Email.Start", "Email.End"}),
#"Added Custom" = Table.AddColumn(#"Expanded ChangeType", "latestStartedAt", each List.Max({[startedAt],[Email.Start]}), type time),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "earliestFinishedAt", each List.Min({[finishedAt], [Email.End]}),type time),
fCalculate = (t as table) =>
let
m = Table.Buffer(t),
fProcess = (a, n)=>
let
previousFinishedAt = List.Last(a)[earliestFinishedAt],
currentStartedAt = n[latestStartedAt],
currentFinishedAt = n[earliestFinishedAt],
actualFinish = List.Last(a)[actualFinish],
actualStart = List.Max({actualFinish, currentStartedAt}),
outputRecord = [#"ActualtimeSpent (minutes)" = List.Max({0, Duration.TotalMinutes(currentFinishedAt-actualStart)}), actualFinish = List.Max({actualFinish, currentFinishedAt})]
in outputRecord,
process = List.Skip(List.Accumulate(Table.ToRecords(m), {[actualFinish = m{0}[startedAt], #"finishedAt" = m{0}[startedAt]]}, (a, n)=> a & { n & fProcess(a, n) }))
in process,
Group = Table.Group(#"Added Custom1", "Email", {{"Data", fCalculate}}),
Output = Table.FromRecords(List.Combine(Group[Data]), Value.Type(Table.AddColumn(#"Changed Type", "ActualtimeSpent (minutes)", each null,type number)))
in
Output
There are two tables now : Tasks - same as in the previous one, Schedule is the one that sets out when tasks can start and finish as you requested later.
We join those tables together and then create additonal interim columns for latest of the process start and task start and the earliest of the project end and task end. Then we feed them into the function instead of the original startedAt and fnishedAt.
Hope this helps,
Cheers,
John
Hi John. Thanks for getting back to me.
I don't quite get why there is a merge query in the solution. Essentially, the "schedule" table only has 2 fields -> task start time & task end time , there's no "email" field so there's nothing to "merge".
schedule table
If the schedule table , for example like above , says it starts at 7.00pm and ends at 8.30pm, then everyone's timestamps (base on email) in the task table (where there are time overlaps) should only consider calculating the actualTimeSpent that starts at 7.00pm onwards til 8.30pm
my apoligies if i made things complicated
Hi @Keith011,
Sorry, I though that every process has its own start-stop.
This is the version with a common start-stop time restrictions:
let
Tasks = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVnLchQ5EPyVjT5z6JZaTYRv+wQWDJ714sUeDMz6gfEw+PH/hyVaWpRZWR2jCF9akVNVqspKleT1uvu5e9INB33//e+no8P5I9SP0HfnT/4HDfP6/S5/pB8faQkTKmaooHAwVgfxoE/VWwXReizecnw9gAa0FN24R7SUcKcpu/tFMwCWYrb0q4IwpgSgYcFSiek3tYQBzpjfGzB/NGMSZJiKW0Gh1KqAoqT7mXoDsyWTzyx9aP9gKaAl9p0AFIgn9WNESwF5AhkgUCKeAKi6o/VISauWRtxQwo+JQMkHQZ6YiRhg6gE0VksTma0gWn+KtCwpeG5rR+mfEBQWChwTgCJSZcldQnea8ec5DLIEKQAMicUocb9QYo7i7QXIkWagbO7PFksvW/rgVTsoLGRgRBC2I8ZUOuqVNjDkqWjTYYNcHNqQmAPZ22tHCaXJX2uTR2HTGyfh0uRHjjqLpSN1p6U7sjkmEFmK/UIuc0wrjUnPzJWTTNHeld01ZXxAUFroOnBH63RoThVkDk1Nwcr+mMRwrJZGan7aKoL2KOZKRJLyVEETBj5RTDnwv1ra7rildscqhpCngJb2NPBxZw9drDa4YzKSMlbQ3oHn74YuzxiiuFblrTXknRlvW9ruxKmKHHYnoiNOfU+0gcHS2ANoz3l/YnMcqY7Z0j8t58G7lt290zwp505b3J3a2g2OPJ2K2KCl0gdnLbp6ZtcHZy46U3nC0WbGrFu8rVs2t+4aptW13XQgWa0gJgeVKAEIjyVSxgqia8ZIpesBFBdAA4KQQfgxVRCv06ScQe/fOylfQoWFwpQDqKDGhRIjypbGEmEGRZ7sVVoLKmLag7RVQdGAmIQwM2pEVCIVZRQN7jJuF9Se+swoM7oPwqwZNWG6JofJ5w2Cfm7LRi0xVUNm4MbEVUtGF7FNs6UPLUfDB2UMVjO7+9hi6aMVguA8GXzSNOn1/JNawhIjiNQMy11B5no6CD03LbvLIJoSpWc2Wt8gudx0Mv/1wt9NS303timpq7K3fzXfeuxdtCTgQorlTJsX9sdhwZ1l+IKlPfP9haoPTnYZdNnQmZe2oMGRsUubPu++fGlD5TN9xlxpRPqycuUQTlTnyoYanFMhg/CCSANcBUW+J+i15EpSTAFWS6TciQQsW7pu0YHrloRf2/XoPHVca1X0APqsMelB/FnbABseQSQEvbTvjeNOJpYbYYfzdHajLNAnoRvLxeicm19aYsogOjJQYRFE8hxdEN+RnWTeakz6+nBr+3pwBPNWY5IBeNvSmxkUFuSiNMu202cModO2M0+m0RHMrcpc71oyF/0oXZ5BYaGBEUP3fNXCDMJbO8lcBe0dr7bzek8g0bmvWhV9MfjqyIUQZdciPDuHTRL4zpLAm8h3DsOlKrvOPokTLxFEF5wgu/vm7E507pusY4DZ25015I0EdzYz/OiJlkh69WJ2pwqml437Fn2+1x7Xw+6+kwuQPvk9OEogjwEPyrlBAn9QEmBHzZjHFoY/CsvouvQDY0de15B5fNGXh0dLS3pfLD2eQXRbDgsg4q7+g+DRrsv17/w/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Email = _t, startedAt = _t, finishedAt = _t, #"timeSpent (minutes)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Tasks,{{"Email", type text}, {"startedAt", type time}, {"finishedAt", type time}, {"timeSpent (minutes)", Int64.Type}}),
Schedule = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrIyMACiglylWJ1oJWMrYxgvFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Task Duration" = _t]),
ChangeType = Table.TransformColumnTypes(Schedule,{{"Task Duration", type time}}),
Join = Table.AddColumn(#"Changed Type", "data", each Table.Transpose(ChangeType, {"Start", "Stop"})),
#"Expanded ChangeType" = Table.ExpandTableColumn(Join, "data", {"Start", "Stop"}, {"Email.Start", "Email.End"}),
#"Added Custom" = Table.AddColumn(#"Expanded ChangeType", "latestStartedAt", each List.Max({[startedAt],[Email.Start]}), type time),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "earliestFinishedAt", each List.Min({[finishedAt], [Email.End]}),type time),
fCalculate = (t as table) =>
let
m = Table.Buffer(t),
fProcess = (a, n)=>
let
previousFinishedAt = List.Last(a)[earliestFinishedAt],
currentStartedAt = n[latestStartedAt],
currentFinishedAt = n[earliestFinishedAt],
actualFinish = List.Last(a)[actualFinish],
actualStart = List.Max({actualFinish, currentStartedAt}),
outputRecord = [#"ActualtimeSpent (minutes)" = List.Max({0, Duration.TotalMinutes(currentFinishedAt-actualStart)}), actualFinish = List.Max({actualFinish, currentFinishedAt})]
in outputRecord,
process = List.Skip(List.Accumulate(Table.ToRecords(m), {[actualFinish = m{0}[startedAt], #"finishedAt" = m{0}[startedAt]]}, (a, n)=> a & { n & fProcess(a, n) }))
in process,
Group = Table.Group(#"Added Custom1", "Email", {{"Data", fCalculate}}),
Output = Table.FromRecords(List.Combine(Group[Data]), Value.Type(Table.AddColumn(#"Changed Type", "ActualtimeSpent (minutes)", each null,type number)))
in
Output
Pleas let me know if this works for you.
Kind regards,
John
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
61 | |
53 | |
27 | |
16 | |
10 |