Editing and cleaning data

[Anonymous]

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

[ankitpatira]

@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]

Dear Ankit,

Thanks for your quick reply,But I fear the solution is not what I need.Please see the punchrecords data in my source,I need to transform those comma seperated values and from those time in time out I need to find the intervals.

Regards

Rishi

[v-caliao-msft]

@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.