Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 ...
PersonID | DATE_ | TIME_ |
79 | 20/06/2018 | 1643 |
79 | 20/06/2018 | 1052 |
79 | 20/06/2018 | 1017 |
79 | 20/06/2018 | 926 |
79 | 20/06/2018 | 915 |
79 | 20/06/2018 | 826 |
110 | 20/06/2018 | 1530 |
110 | 20/06/2018 | 1010 |
110 | 20/06/2018 | 950 |
110 | 20/06/2018 | 915 |
I need help in detecting entrance times of exit times and detection each stage
Solved! Go to Solution.
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!
Hi,
If I understand correctly, you are trying to defind different In/Out and the stage each day for each personID like below table:
To achieve this, you can create 2 calculated columns:
1/ Rank Time by Day and ID:
2/ Name Entrance/Exit + Stage
Hi,
If I understand correctly, you are trying to defind different In/Out and the stage each day for each personID like below table:
To achieve this, you can create 2 calculated columns:
1/ Rank Time by Day and ID:
2/ Name Entrance/Exit + Stage
Thank you
I also succeeded with DAX
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"
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |