Calculate work time from punch in/out times

[Idigor]

Hi all,

My organization wants to get the punch in/out data and calculate how many hours each employee works.

My table looks like this:

Employee	Date	Action
John	01/11/2020 8:00:00	In
John	01/11/2020 14:00:00	Out
Alex	28/10/2020 7:54:23	In
Alex	28/10/2020 12:23:52	Out
Alex	28/10/2020 14:01:44	In
Jane	12/10/2020 8:27:27	In
Jane	12:10/2020 15:44:22	Out

As you can see, Alex forgot to punch out the second time, so I want to skip that value. His working hour should be from 7:54 to 12:23.

The idea is to get the hours/month and hours/year worked by each employee, but only counting In-Out pairs, if there is only a In, discard it.

If anyone can point me in the right direction, I'm quite new to PBI. Thanks.

[CNENFRNL]

Hi, @Idigor , you might want to try a more generalized Power Query solution addressing some more complex senarios like this,

Employee	Date	Action
Test	2020-10-30 09:00	Out
John	2020-11-01 08:00	In
John	2020-11-01 12:00	In
John	2020-11-01 14:00	Out
Alex	2020-10-28 07:54	In
Alex	2020-10-28 12:23	Out
Alex	2020-10-28 14:01	In
Jane	2020-10-12 08:27	In
Jane	2020-10-12 15:44	Out
Jane	2020-10-11 15:23	Out
Test	2020-10-31 11:28	In
Test	2020-10-31 16:09	Out
Test	2020-10-31 14:20	In
Jane	2020-10-11 10:09	In
Jane	2020-10-13 09:00	In

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdGxDsIgGATgV2mYS3L/D7WUzVEXFzfSwYHEwdTBmvTxhTRpaRVcyZe7A5wTV/8aRS0YDEmQChU6C4Sjy3sUfe3E+XkfFkESVMHM4jRkAPE/oLcdx4efkhVsKrS20WvENwgdrIoRsYSSFbfBJ4A43oPbEqDGap0+xU5QFJsV++cMgiybteQHOFh05QhtGfmdAWCOyAC1/GkE/Qc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Date = _t, Action = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Date", type datetime}, {"Action", type text}}, "fr"),
    
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Employee", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Employee"}, 
        {{
            "All", each
                [
                    log = Table.ToRecords(Table.AddIndexColumn(_,"Index",0,1)),
                    pairs = List.Accumulate(
                        log,
                        {},
                        (s,c) => if c[Action] = "Out" then s else 
                            [
                                next_c = log{c[Index]+1}?,
                                #"added In/Out" = if next_c[Action]?="Out" then s&{[In=c[Date], Out=next_c[Date]]} else s
                            ][#"added In/Out"]
                    )
                ][pairs]
        }}),
    #"Expanded All" = Table.ExpandRecordColumn(Table.ExpandListColumn(#"Grouped Rows", "All"), "All", {"In", "Out"}, {"In", "Out"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All", "Working Time", each [Out] - [In])
in
    #"Added Custom"

[AnkitKukreja]

Hi @Idigor 

What you can do is split your columns to have in and out data then you can create a calulated column and use

Datediff ( In time , Out time , hours/minute/day) whatever you want to select, it's in your hand then.

Thanks,

Ankit

[Anonymous]

Hi ,

I am trying this query in my avd editor

bur i got null values

please help me in this

code :

let
Source = Excel.Workbook(File.Contents("C:\Users\Gaini\Documents\todays.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Employee", type text}, {"Date", type datetime}, {"Action", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Employee", Order.Ascending}, {"Date", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Employee"},
{{
"All", each
[
log = Table.ToRecords(Table.AddIndexColumn(_,"Index",0,1)),
pairs = List.Accumulate(
log,
{},
(s,c) => if c[Action] = "Out" then s else
[
next_c = log{c[Index]+1}?,
#"added In/Out" = if next_c[Action]?="Out" then s&{[In=c[Date], Out=next_c[Date]]} else s
][#"added In/Out"]
)
][pairs]
}}),
#"Expanded All" = Table.ExpandRecordColumn(Table.ExpandListColumn(#"Grouped Rows", "All"), "All", {"In", "Out"}, {"In", "Out"}),
#"Added Custom" = Table.AddColumn(#"Expanded All", "Working Time", each [Out] - [In])
in
#"Added Custom"

please help me in this

thanks in advance

[Smauro · Accepted solution]

Hi @Idigor 

Check out this approach:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUTIw1Dc01DcyMDJQsLAyMAAioKBnnlKsDjYlhiZwNf6lJbgUmRJhkAWGQY45qRVArpGFvqEBRJG5lamJlZExwhxMJYZGQAVWpkZ4zQG52tDKxATJQYl5qUCeoRFcjYWVkTkQ4VMC9JcJ0D1wu2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Date = _t, Action = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
    

    #"Split DateTime" = Table.SplitColumn(#"Changed Type", "Date", (dt) => {DateTime.Date(dt), DateTime.Time(dt)}, {"Date", "Time"}),

    #"Group by Date" = Table.Group(#"Split DateTime", {"Employee", "Date"}, {{
        "PunchTimes",
        each
            let
                times = _[[Time], [Action]],
                ins = List.Sort(Table.SelectRows(times, each [Action] = "In")[Time]),
                outs = List.Sort(Table.SelectRows(times, each [Action] = "Out")[Time]),
                zip = List.Select(List.Zip({ins, outs}), each _{0} <> null and _{1} <> null )
            in
                zip
        , type {{time}}
    }}),
    #"Split PunchTimes" = Table.SplitColumn(Table.ExpandListColumn(#"Group by Date", "PunchTimes"), "PunchTimes", (x) => x, {"In", "Out"}),
    #"Added Worked Time" = Table.AddColumn(#"Split PunchTimes", "Hours Worked", each Duration.TotalHours([Out] - [In]), type number)
in
    #"Added Worked Time"

It first splits your [Datetime] to [Date] & [Time],
It then groups per [Employee] & [Date] to pick only the existing in-out pairs -- assuming here that i) an employee could have more than one pair per day, and ii) either in or out could be missing, hence not to be counted.
Last, it expands the pairs to be counted in new rows and columns named [In] & [Out]
As an extra step, [Hours Worked] is added which calculates the duration in hours of every pair.

You could test this out on your data by using the steps after #"Changed Type", renaming #"Changed Type" to your Previous Step's name.
Also, if you do have more columns in your table, they should be added in the #"Group by Date" step, alongside {"Employee", "Date"}.

Best,
Spyros

[Anonymous]

Hi Smauro, 

Can you elaborate the steps using UI as am new to Power BI finding it Difficult to understand. Thank you

[Anonymous]

Good morning,

i need helping solving what i thought should be simple.  I have dates in column 1 and times in column 2.

DATEIN

12/22/2021	7:00:00 AM
12/22/2021	12:00:00 PM
12/22/2021	12:30:00 PM
12/22/2021	3:30:00 PM
12/23/2021	7:00:00 AM
12/23/2021	12:00:00 PM
12/23/2021	12:30:00 PM
12/23/2021	3:30:00 PM

 i need this to be formatted as one instance of each "date" in column 1 and times for each date in rows

DATE	IN	column 1	column 2	column 3
12/22/2021	7:00 AM	12:00 PM	12:30 PM	3:30 PM
12/23/2021	7:00 AM	12:00 PM	12:30 PM	3:30 PM