time calculation based on day and in and out times for every different badge number

[Erik_Vervliet]

I need the total "In" time from every badge number for every different date.

so i need to calculate between In and Out times and make a total of that

I dont know where to start ...

[ronrsnfld]

If your example is realistic, with a single IN/OUT per date, and nothing missing, you can

• Add a column which shows only the Date
  • eg "Date.From([Date])"
• Group by "badge" and "Date Only"
• In the Advanced Editor, change the aggregation to subtract the first entry from the second
  • Open the Advanced Editor
  • Find the #"Grouped Rows" step
  • Replace the code for that step with the code below
  • Change #"Previous Step" to whatever the name of the previous step in your own code is

    #"Grouped Rows" = Table.Group(#"Previous Step", {"badge", "Date Only"}, {
        {"IN Time", each Duration.From([Date]{1} - [Date]{0}), type duration}})

If you have problems because the data you post is not representative (multiple in/out on the same day; times spanning midnight; etc, then post a more realistic example AS TEXT which can be copy/pasted, as well as rules for instances where there are issues with this simple algorithm.

[Erik_Vervliet]

Hi ronrsnfld,

Thanks for your quick reply.

It's not single In/Out per date it can be multiple in and out times on a single date like the last 4 rows but can be more times

[ronrsnfld]

Let me know when you provide the textual realistic example I mentioned in my comment above

[Erik_Vervliet]

Here a link to the file with the data

https://we.tl/t-8A0OsVjXlu

[ronrsnfld · Accepted solution]

If you are happy with the handling of the misshapen data, then the following may be useful

• If the first entry of the day is an OUT, assume badge has been there since midnight
• If the last entry of the day is an IN, assume badge will be there until the next midnight
• If there are two OUTs in subsequent rows, the second one will be ignored

See the code comments for more information about the algorithm

let

//Change next lines to reflect actual data source
    Source = Excel.Workbook(File.Contents("C:\Users\ron\Desktop\Badge times.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"badge", Int64.Type}, {"Date", type datetime}, {"IN/OUT", type text}}),

//Add column of just the dates
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date Only", each Date.From([Date]), type date),

//Group by "badge" and "date"
    #"Grouped Rows" = Table.Group(#"Added Custom", {"badge", "Date Only"}, {
        {"Total IN Time", (t)=>
            let 

            //Check for a first OUT
            // Add a midnight IN if OUT is first entry for the day
                #"Add Row" = if t[#"IN/OUT"]{0} = "OUT"
                    then Table.FromRecords({Record.FromList(
                            {t[badge]{0}, DateTime.From(t[Date Only]{0}), "IN", t[Date Only]{0}}, 
                            {"badge","Date","IN/OUT","Date Only"})} & Table.ToRecords(t))
                    else t,

            //Check for a last IN
            // add a midnight (next day) OUT if last entry is an IN
                #"Add Row1" = if List.Last(#"Add Row"[#"IN/OUT"]) = "IN"
                    then Table.FromRecords(Table.ToRecords(#"Add Row") & 
                        {Record.FromList(
                            {#"Add Row"[badge]{0}, DateTime.From(Date.AddDays(#"Add Row"[Date Only]{0},1)), "OUT", #"Add Row"[Date Only]{0}}, 
                            {"badge","Date","IN/OUT","Date Only"})})
                    else #"Add Row",

            //Add Index column to enable Pivot with no aggregation with multiple items
            // then Pivot and remove the Index column
                #"Add Index" = Table.AddIndexColumn(#"Add Row1", "Index",0,1,Int64.Type),
                #"Pivot" = Table.Pivot(#"Add Index", List.Distinct(#"Add Index"[#"IN/OUT"]), "IN/OUT", "Date"),
                #"Removed Columns" = Table.RemoveColumns(#"Pivot",{"Index"}),

            //Shift the OUT column times UP one to enable easier subtraction
                #"Shift OUT Up" = Table.FromColumns(
                    Table.ToColumns(#"Removed Columns") &
                    {List.RemoveFirstN(#"Removed Columns"[OUT],1) & {null}},
                    Table.ColumnNames(#"Removed Columns") & {"Shifted OUT"}),

            //Filter out the extra rows by removing rows with null in the "Shifted OUT" column
                #"Remove Nulls in Shifted" = Table.SelectRows(#"Shift OUT Up", each ([Shifted OUT] <> null)),

            //Subtract IN from the Shifted OUT to get the IN time per login
                #"Add Total IN" = Table.AddColumn(#"Remove Nulls in Shifted", "Custom", each [Shifted OUT]-[IN])
            in 

            //Sum the total of the Custom column
                List.Sum(#"Add Total IN"[Custom]), type duration}})
in
    #"Grouped Rows"

Partial output from your data

Check output for 4083873: Note only first OUT is "counted"

[ronrsnfld]

What are your rules for handling this situation:

Note that the badge logged out twice in a row.

[Erik_Vervliet]

Thanks for the solution! 

The double OUT and/or IN times sometimes occures in our system due to technical malfunction of a badge reader, sometimes it's not sending the input to the server. We know the problem exist.