Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Keith011
Helper III
Helper III

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

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

EmailstartedAtfinishedAttimeSpent (minutes) ActualtimeSpent (minutes)
abc@gmail.com1:00:00PM2:00:00PM60 60
abc@gmail.com1:15:00PM1:55:00PM40 0
abc@gmail.com1:30:00PM2:05:00PM35 5
abc@gmail.com2:15:00PM3:00:00PM45 45
abc@gmail.com3:05:00PM3:30:00PM25 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!

1 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

View solution in original post

22 REPLIES 22
Keith011
Helper III
Helper III

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

EmailstartedAtfinishedAttimeSpent (minutes)ActualtimeSpent (minutes)Task Type
abc@gmail.com1:00:00PM2:00:00PM6060Task A
abc@gmail.com1:15:00PM1:55:00PM400Task A
abc@gmail.com1:30:00PM2:05:00PM355Task A
abc@gmail.com2:15:00PM3:00:00PM4545Task A
abc@gmail.com3:05:00PM3:30:00PM2525Task A
def@gmail.com5:00:00PM6:00:00PM6060Task B
def@gmail.com5:15:00PM5:55:00PM400Task B
def@gmail.com5:30:00PM6:05:00PM355Task B
def@gmail.com6:15:00PM7:00:00PM4545Task B
def@gmail.com7:05:00PM7:30:00PM2525Task B

 

Schedule Table (note that this table format is slightly different from my original schedule table at the top)

Task TypeStart TimeEnd Time
Task A1:00:00PM3:30:00PM
Task B5:00:00PM7: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

v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_0-1661248083465.png

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

EmailstartedAtfinishedAttimeSpent (minutes)ActualtimeSpent (minutes)
abc@gmail.com1:00:00PM2:00:00PM6060
abc@gmail.com1:00:00PM2:00:00PM600
abc@gmail.com1:00:00PM2:00:00PM600

 

I've attached an excel file as a sample let me know if you can access it.

Calculate The Actual View Duration.xlsx

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.

vkalyjmsft_0-1661494070102.png

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.

vkalyjmsft_1-1661494367701.png

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 

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
Helper III
Helper III

any idea? anyone?

wdx223_Daniel
Super User
Super User

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 

jbwtp
Memorable Member
Memorable Member

Hi @Keith011,

 

I just noticed that this does not perform well where a subsequent task completes before a preceeding task, e.g.

jbwtp_0-1661396907926.png

 

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
Memorable Member
Memorable Member

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 

@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"

 

EmailstartedAtfinishedAttimeSpent (minutes) ActualtimeSpent (minutes)
abc@gmail.com1:00:00PM2:00:00PM60 30
abc@gmail.com1:15:00PM1:55:00PM40 0
abc@gmail.com1:30:00PM2:05:00PM35 5
abc@gmail.com2:15:00PM3:00:00PM45 45
abc@gmail.com3:05:00PM3:30:00PM25 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

Keith011_0-1661477654915.png

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

@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?

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.

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

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

@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

Keith011_1-1662111394189.png

 

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors
Top Kudoed Authors