Excel to power query clock/time issue

[saad_mce]

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

[Anonymous]

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 Tao

[saad_mce]

Hi 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

[Anonymous]

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.

[visheshjain · Accepted solution]

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