Fabric does not recognize column time from csv

[VoltesDev]

Hi guys,

I have CSV file that I upload to my Lakehouse, like this sample:

stepname	workspace	notebooks	status	start_time	end_time	duration
gold load	dwh_gold	nb_gold_load_transactions,	Succeeded	12:36:59 AM	12:43:21 AM	382
silver transaction	dwh_silver	nb_silver_load_transactions	Failed	12:22:02 AM	12:28:40 AM	397

Problem is when using feature "Load to table" from that file, Fabric always gave me type of ABC which is string, how to make this a time type instead ? If I check through all my cell value, there is no "error" value, is the "AM/PM" makes it string ?

Thanks,

[rohit1991 · Accepted solution]

Hi @VoltesDev ,

The issue arises because Power BI Fabric detects time columns in CSV files as text (ABC type) instead of a proper Time format, especially when the values contain AM/PM formatting. Since CSV files do not store explicit data types, Fabric treats non-numeric values as strings by default. To resolve this, you can manually convert the start_time and end_time columns into the correct Time or DateTime type using Power Query, SQL queries, or by modifying the CSV file before upload. The most efficient method is to use Power Query in Fabric by changing the column type or using the TIMEVALUE function. Alternatively, if you're working with a Lakehouse SQL Endpoint, you can use SQL to cast the column to the correct data type. If modifying the source file is an option, changing the time format to a 24-hour format (HH:MM:SS) before uploading can also help Fabric correctly recognize the data type. Below are some practical SQL queries that can help transform the time columns after loading them into the Lakehouse.

1. Convert String Time to Proper Time Format

SELECT stepname, 
       workspace, 
       notebooks, 
       status, 
       TRY_CAST(start_time AS TIME) AS start_time,
       TRY_CAST(end_time AS TIME) AS end_time,
       duration
FROM MyLakehouseTable;

2. Convert String Time to DateTime if Needed

SELECT stepname, 
       workspace, 
       notebooks, 
       status, 
       TRY_CAST(start_time AS DATETIME) AS start_time,
       TRY_CAST(end_time AS DATETIME) AS end_time,
       duration
FROM MyLakehouseTable;

3. Handling AM/PM Time Formatting Using String Manipulation

SELECT stepname, 
       workspace, 
       notebooks, 
       status, 
       FORMAT(CONVERT(DATETIME, start_time, 109), 'HH:mm:ss') AS start_time,
       FORMAT(CONVERT(DATETIME, end_time, 109), 'HH:mm:ss') AS end_time,
       duration
FROM MyLakehouseTable;

Using these SQL transformations ensures that Power BI Fabric correctly recognizes and processes the time values without manual interventions in Power Query or Excel.

Passionate about leveraging data analytics to drive strategic decision-making and foster business growth.
Connect with me on LinkedIn: Rohit Kumar

[VoltesDev]

Yes, 

In the end, I'm using Dataflow for the table creation so I can Transform first.

Thanks.