Forum Discussion
Excel to power query clock/time issue
Hi all,
I have below data in excel. This is the start and end time of a production order in SAP.
| Schedule Start | Start Time | Schedule Finish | Finish Time |
| 10.01.2022 | 22:05:35 | 10.01.2022 | 24:00:00 |
When I read the data in power query, it converts the finish time 24:00:00 to 12 AM of the same day.
Start Date Start Time Finish Date Finish Time
| 10/01/2022 | 10:05:35 PM | 10/01/2022 | 12:00:00 AM |
How do I get power query to keep the clock in 24 hours version like the excel hours? Or How do I convert finish time to 12 AM of the following day? Any suggestion is much appreciated.
Regards,
Abrar
Hi saad_mce,
I think you will need to add a conditional column that if time is greater than 23:59:59 then add 1 to the Finish Date.
Ideally, 24:00:00 is equivilant to 00:00:00, which is when the next day begins.
You can also try converting the time into a numeric value by multiplying it by 24*60*60 = 86,400 and then adding the conditional column.
I think PQ works faster on numbers than any other data type.
Hope this helps.
Thank you,
Vishesh Jain
4 Replies
Hi saad_mce,
I think you will need to add a conditional column that if time is greater than 23:59:59 then add 1 to the Finish Date.
Ideally, 24:00:00 is equivilant to 00:00:00, which is when the next day begins.
You can also try converting the time into a numeric value by multiplying it by 24*60*60 = 86,400 and then adding the conditional column.
I think PQ works faster on numbers than any other data type.
Hope this helps.
Thank you,
Vishesh Jain
Hi saad_mce ,
The first method is to change the data type of the time column to text.
The second method is that if you mainly want the report to show 24-hour time, you can also use the h:nn:ss format to change the format of the time column after loading into Power BI.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi saad_mce ,
Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your problem.
Best Regards,
Stephen TaoHi Stephen,
Apologies for late response.
I work on powerbi reports on a monthly basis. I did not have a chance to come back to this report earlier.
The conditional column solution suggested by visheshjain worked better.
Regards,
Abrar
