Calculate the actual time duration when there are overlaps in between the data

[Keith011]

The goal is to get the "actual" view duration for each individual in a table. Example :

Email	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

• Actual Time Spent was 0 at the second row because it's already between the 1pm - 2pm timestamp in the first row
• Actual Time Spent was 5 at the third row because the first row end time was at 2pm hence there's only extra 5 minutes for this timestamp

How am I able to get the actual time spent for everyone in my list?

Appreciate the help. Thanks in advance!

[Keith011]

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

Email	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

[jbwtp]

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

[v-yanjiang-msft]

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.

[Keith011]

@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

Email	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.

Calculate The Actual View Duration.xlsx

[v-yanjiang-msft]

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.

[Keith011]

@v-yanjiang-msft 

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

[Keith011]

any idea? anyone?

[wdx223_Daniel]

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

[Keith011]

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

[jbwtp]

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 

[jbwtp]

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

[jbwtp]

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

@Keith011 

[Keith011]

@jbwtp 

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"

Email	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!

[jbwtp]

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

[Keith011]

@jbwtp 

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?

[jbwtp]

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.

[Keith011]

@jbwtp 

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!

[jbwtp]

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

[Keith011]

@jbwtp 

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

[jbwtp · Accepted 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