Power query to get data

[Satheesh9012]

Hello all,

There is report where i am working on requires help in making the data look simple, i have a raw data as a first step i need to change the time zone to IST(Indian standard time zone), for which the data comes along with the date, second step is i need to split join time and leave time which comes one below the other in the same raw data file, there are chances same person joining time will be there in two rows with two different time and there are left time also mentioned subsequently, after the split of join time and leave time in two different columns i need to calculate the duration between join and wait time

I am attaching the screen shot 1 which is the raw data file, using power query i need to change it as it looks in the screen shot 2, using power query the out put should like screen shot 2.


Any help for my query raised will make my life simple in working

Thanks
Satheesh

 

[VBA learner ITG]

Expression.Error: There were too many elements in the enumeration to complete the operation.
Details:

• 
  
  Expression.Error: We couldn't parse the input provided as a Time value.
  Details:
  10/13/2021 12:01:56 PM
  
  Two errors which showed up while selecting, also i tried to remove more data to check if it is actly working i see this error as well

Expression.Error: We couldn't parse the input provided as a Time value.
Details:
10/13/2021 11:47:46 AM

The above error could occur when you pivot columns with same name, please check column names in your data source.

And there is a similar thread for your reference incase you need further clarification on what to do.

https://social.technet.microsoft.com/Forums/en-US/7923e025-a0c4-407b-bd0f-65959e5038bb/what-does-thi...

 

[VBA learner ITG]

Expression.Error: There were too many elements in the enumeration to complete the operation.
Details:

• 
  
  Expression.Error: We couldn't parse the input provided as a Time value.
  Details:
  10/13/2021 12:01:56 PM
  
  Two errors which showed up while selecting, also i tried to remove more data to check if it is actly working i see this error as well

Expression.Error: We couldn't parse the input provided as a Time value.
Details:
10/13/2021 11:47:46 AM

Also dont forget to change to the column Joined Time to TIME

 

[Satheesh9012]

Expression.Error: There were too many elements in the enumeration to complete the operation.
Details:

• 
  
  Expression.Error: We couldn't parse the input provided as a Time value.
  Details:
  10/13/2021 12:01:56 PM
  
  Two errors which showed up while selecting, also i tried to remove more data to check if it is actly working i see this error as well

Expression.Error: We couldn't parse the input provided as a Time value.
Details:
10/13/2021 11:47:46 AM

Hi

Sorry to push any luck for my query

 

[VBA learner ITG]

Are you able to share your file so we can see what the data source issue is.

Unfortunately, I am unable to assist any further.

 

[Satheesh9012]

Are you able to share your file so we can see what the data source issue is.

Unfortunately, I am unable to assist any further.

I saw the values in the list, there time and date for different people with same values, is it possible to fix that since different person time stamp will have same time and date, but pivot removes those cells and throws error, any possible way to fix that, its an attendance list, there are chances there could be different person time stamping on same day and same time, so rather than removing can we retain those as well and rest all is working fine even the duration is also fixed just the error due to pivot which makes few cells blank and throws error which in return does not have values in any of the cells, if that is also fixed i believe i have a solution, please help

 

[Satheesh9012]

Are you able to share your file so we can see what the data source issue is.

Unfortunately, I am unable to assist any further.

Hi There,

I fixed it using list. Median, however i have one more challenge to it i want it to convert the entire time to IST where in the am and pm also should change accordingly, i missed to do that step, if that is also fixed it can close this thank you for all your help

 

[Satheesh9012]

Hi There,

I fixed it using list. Median, however i have one more challenge to it i want it to convert the entire time to IST where in the am and pm also should change accordingly, i missed to do that step, if that is also fixed it can close this thank you for all your help

Sorry for lots of post, i have fixed the time zone also but its in 24 hour format, i want it to change it to 12 hour format, am and pm is not necessary, could you please help, i have a colum as Join time and leave Time, i want it to change it to 12 hour and add a new column for getting duration in hours and minutes format

please help

 

[VBA learner ITG]

Sorry for lots of post, i have fixed the time zone also but its in 24 hour format, i want it to change it to 12 hour format, am and pm is not necessary, could you please help, i have a colum as Join time and leave Time, i want it to change it to 12 hour and add a new column for getting duration in hours and minutes format

please help

Have a read of this formum which explains the additional step you need to apply to change the time format.

How do I convert time format from 12h am/pm to 24h in Power Query?

social.technet.microsoft.com