Fabric does not recognize column time from csv

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.