Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago

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...
Need Help
Tutorial Requests
ankitpatira's avatar
ankitpatira
Community Champion
10 years ago

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's avatar
Anonymous
Not applicable
10 years ago

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's avatar
    v-caliao-msft
    Microsoft Employee
    10 years ago

    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.