Forum Discussion
Editing and cleaning data
Dear Team,
I am new to Power Bi,I have a requirement which is shown below.Can anyone explain me how to do the same.
If I select a student id 2928,I need a table which shows like the one given below.
Time in/out Door time -period
10:10 in FD 0:00
10:45 out FD 0:35
11:14 in FD 0:29
12:42 out BD 1:28
12:55 in BD 0:13
14:06 out BD 1:11
14:24 in BD 0:18
14:41 out FD 0:17
14:57 in FD 0:16
16:39 out BD 1:42
16:54 in BD 0:15
17:56 out FD 1:02
But my source data is like this
| Studentid | StudentName | AttendanceDate | InTime | OutTime | PunchRecords |
| 2928 | Neha | 25/11/2015 | 10:10 | 17:56 | 10:10:in(FD),10:45:Out(FD),11:14:in(FD),12:42:Out(BD),12:55:in(BD),14:06:Out(BD),14:24:in(BD),14:41:Out(FD),14:57:in(FD),16:39:Out(BD),16:54:in(BD),17:56:Out(FD) |
| 2929 | Gireesh | 25/11/2015 | 10:03 | 19:01 | 10:03:in(FD),11:14:in(FD),12:42:Out(FD),12:55:in(FD),14:07:in(BD),14:07:Out(BD),14:23:in(BD),14:31:Out(FD),16:01:Out(BD),16:02:in(BD),16:54:in(FD),19:01:Out(FD) |
| 2882 | Sara | 25/11/2015 | 9:06 | 18:36 | 09:06:in(FD),12:54:Out(BD),13:27:in(BD),13:39:Out(FD),13:45:in(FD),16:12:Out(FD),16:16:in(FD),18:36:Out(FD) |
Thank you for your time and consideration
Regards,
Rishi
3 Replies
Anonymous Follow below steps. I have created sample dataset similar to yours.
1. Go to query editor and make sure TimeIn and TimeOut columns are declared as Time under Data Type.
2. Select TimeIn TimeOut columns and click Unpivot columns which will give you result columns as below.
3. Add Index column under Add Column tab and exit Query Editor by Close&Apply.
4. Finally add custom column using below DAX code to get difference in minutes.
Difference = 24. * 60 *(IF( 'sam'[Index] = 0, sam[Value], LOOKUPVALUE( 'sam'[Value], 'sam'[Index], 'sam'[Index]-1) ) - sam[Value])
Anonymous
I don't think there is a functionally to transform those comma seperated values and from those time in time out directly. You'd better to tranfer it in ETL tools and then display the data in Power BI.
