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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tlotfi
Frequent Visitor

Calculate the hours of personnel presence at work

Hey guys,

I'm going to calculate the individual hours of each person individually

A person may have 5 times for entrance and 5 times for the exit for per day has been registered, That's all brought in one column.

I think The time column should also be converted to the time format with the help of the following formula:
FORMAT(Table1[TIME_], "#0:#0")

 

As an example, the ID 79 , entrance at 8:26 AM and leaves at 9:15 AM and returns at 9:26 AM and ends at 10:17 AM and ...

 

PersonIDDATE_TIME_
7920/06/20181643
7920/06/20181052
7920/06/20181017
7920/06/2018926
7920/06/2018915
7920/06/2018826
11020/06/20181530
11020/06/20181010
11020/06/2018950
11020/06/2018915

 

I need help in detecting entrance times of exit times and detection each stage

 

2 ACCEPTED SOLUTIONS
LivioLanzo
Solution Sage
Solution Sage

First you are going to need to split the time column into 2 columns: Entrance Time and Exit Time and then the duration could be easily calculated. The split should be done with PowerQuery, if it is not possible to do it directly with the source Database. 

So the end table will have half the number of rows of the current table

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

Anonymous
Not applicable

Hi,

 

If I understand correctly, you are trying to defind different In/Out and the stage each day for each personID like below table:Q23.PNG

To achieve this, you can create 2 calculated columns:

 

1/ Rank Time by Day and ID: 

 

 

 

Q23.PNG

 

 

 

 

 

 

2/ Name Entrance/Exit + Stage

 

Q22.PNG

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hi,

 

If I understand correctly, you are trying to defind different In/Out and the stage each day for each personID like below table:Q23.PNG

To achieve this, you can create 2 calculated columns:

 

1/ Rank Time by Day and ID: 

 

 

 

Q23.PNG

 

 

 

 

 

 

2/ Name Entrance/Exit + Stage

 

Q22.PNG

Thank you

I also succeeded with DAX

LivioLanzo
Solution Sage
Solution Sage

First you are going to need to split the time column into 2 columns: Entrance Time and Exit Time and then the duration could be easily calculated. The split should be done with PowerQuery, if it is not possible to do it directly with the source Database. 

So the end table will have half the number of rows of the current table

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Thank you

I succeeded

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"STATUS", Int64.Type}, {"DATE_",type date}, {"TIME_", Int64.Type}, {"PersonID", type any}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"DATE_", "PersonID", "TIME_"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"PersonID", "DATE_"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1) , type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"TIME_", "Index"}, {"TIME_", "Count.Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Count",{{"Count.Index", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","1","entrance 1",Replacer.ReplaceText,{"Count.Index"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","2","leaves 1",Replacer.ReplaceText,{"Count.Index"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","3","entrance 2",Replacer.ReplaceText,{"Count.Index"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","5","entrance 3",Replacer.ReplaceText,{"Count.Index"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","4","leaves 2",Replacer.ReplaceText,{"Count.Index"}),
    #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","6","leaves 3",Replacer.ReplaceText,{"Count.Index"}),
    #"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","8","leaves 4",Replacer.ReplaceText,{"Count.Index"}),
    #"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","7","entrance 4",Replacer.ReplaceText,{"Count.Index"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value7", List.Distinct(#"Replaced Value7"[Count.Index]), "Count.Index", "TIME_", List.Sum)
in
    #"Pivoted Column"

Capture.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.