Power BI do not recognize the value 24:00:00 as time

[acampos]

Hi All,

I’m importing a CSV file that contains the time and the date in different columns.  The time can contain the value 24:00:00. When importing it to Power BI the system does not recognize it as a date time type and is giving me an error message. 

The application accepts and recognize it as a date type either the values 00:00:00 or 12:00:00 a.m. but not the 24:00:00.

Any thoughts in how to import it and maintain it as the value as 24:00:00 ?

[acampos · Accepted solution]

I belive my problem was time formating. I can not use the value 23:59:59 in my computer. I solved my problem by converting the column from text to time and replacing value error to 11:59:59 p.m.

Thanks all for your reply.

[v-huizhn-msft]

Hi @acampos,

Congratulations, you have resolved your issue by yourself. Please mark your workaround as answer, so more people will get solution and useful information clearly.

Best Regards,
Angelia

[pxg08680]

@acampos

24 hour format also works and even it shows 24:00:00.

Go to the query editior and see what is your TIME column type. If it is set to type Time change it to Text.

This image shows you column Time is type text and same column is duplicated and is made to type Time, it gives error.

So chnage the data type and see.

Hope this helps.

[acampos]

I changed the column type to text but it still showing the erros.  Same amount. It changes when I change the source file from CSV to XLS.  The column value changes to 12:00:00am.

[v-huizhn-msft]

Hi @acampos,

As I tested, it works fine as the screenshot shown. Please change your data type in CSV/XLS, then import them to Power BI, keep the text type and check if it works fine.



Thanks,
Angelia