How to set up 15 min increments for in/out timestamps and calculate how many people were present

[ramsunkavalli]

Hello,

I'm trying to use PQ to use Time in and Time out data that I have for multiple people, separate the whole day out in 15 min increments and identify how many people were in each increment.

For example

Person 1 - Time In: 8:03 AM. Time Out: 12:07 PM

Person 2 - Time in: 8:15 AM. Time Out: 11:30 AM

Example Table

Time   Count of People

8:00              1

8:15              2

and so on for all 24 hours.

I would appreciate any help, thank you.

[Jimmy801]

Hello @ramsunkavalli 

were you able to solve the problem?

Jimmy

[Jimmy801]

Hey @ramsunkavalli 

were the answers of any help?

Have a nice time

Jimmy

[Jimmy801]

Hello @ramsunkavalli 

Created quickly some nice staff for you. Would this logic fit your needs

let
    TimeStampsExample = 
        let
            Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PUzBU0lEyMNMzNNIztFQwsLAyMEYWMDSyMjRQitWBKzdCU25oiqLc3MrAVCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Person = _t, In = _t, Out = _t]),
            #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"In", type datetime}, {"Out", type datetime}})
        in
        #"Changed Type",

    TimeStampInternal = TimeStampsExample,
    AddListOfEveryMinutePresent = Table.AddColumn
    (

        TimeStampInternal,
        "ListOfEveryMinutePresent",
        each List.DateTimes([In], (Number.From([Out])-Number.From([In]))*24*60, #duration(0,0,1,0))

    ),
    
    GetListOfAllTimes = List.Combine(AddListOfEveryMinutePresent[ListOfEveryMinutePresent]),

    CreateListQuarter = List.DateTimes
    (
        List.Min
        (
            TimeStampInternal[In]
        )  - #duration(0,0,Time.Minute(List.Min(TimeStampInternal[In])),0),
        ((Number.From(List.Max(TimeStampInternal[Out])) - Number.From(List.Min(TimeStampInternal[In])))*24*4)+1,
        #duration(0,0,15,0)
    ),

    CreateTableFromQuarters = #table
    (
        type table [#"Time"=  datetime],
        List.Zip({CreateListQuarter})
    ),

    AddColumnToQuarterTable = Table.AddColumn
    (
        CreateTableFromQuarters,
        "Persons",
        (add)=> if List.IsEmpty
        (
            List.Select(GetListOfAllTimes,each _ = add[Time] )
        ) then 0 else List.Count
        (
            List.Select(GetListOfAllTimes,each _ = add[Time] )
        )

    )





in
    AddColumnToQuarterTable

copy the code into the advanced editor

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[v-frfei-msft · Accepted solution]

Hi @ramsunkavalli ,

We can get a time list separating the whole day out in 15 min increments in power query using the M code as below.

let
    Source = List.Times(#time(0, 0, 0), 96, #duration(0, 0, 15, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

After that, create a calculated column in it.

Column = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Time in ]<= Query1[Column1] && 'Table'[Time out]>=Query1[Column1]))

Pbix as attached.