How to Calculate Time in and Time out Time from Attendance Records

[RAKESH1986]

Hi,

i have below the record of attendance, could you help me to find time in and time out in separate columns for each user against the given date?

[Vijay_A_Verma]

Try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddBJCsAgDEDRu7gWNIljdm2PId7/GhXbQIe4k/+IgbRmIHMIxhp04NAjjedmuh1QV5AYcQL+AVQgDnE1ETQoDDQ7Sd+vAWSKKhT2aUISOGTCVw2qrMjvXmTDp497QNVgdK9/dF+jPHo/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Access_Time = _t, Access_Date = _t, Access_UserID = _t]),
    #"Sorted Rows" = Table.Sort(Source,{{"Access_UserID", Order.Ascending}, {"Access_Date", Order.Ascending}, {"Access_Time", Order.Ascending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Access_Time", type time}, {"Access_Date", type date}, {"Access_UserID", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Access_UserID", "Access_Date"}, {{"In Time", each List.Min([Access_Time]), type nullable time}, {"Out Time", each List.Max([Access_Time]), type nullable time}})
in
    #"Grouped Rows"