Forum Discussion

NaqNaq's avatar
NaqNaq
Frequent Visitor
5 months ago
Solved

Time column is throwing Data Type Error in Power BI

I am using direct query to connect to my table in SQL Server that has the following columns -   Employee ID  Product  Date(Date/Time) Time(Time)   My table has around 5000 records. When I use Tab...
Need Help
  • Juan-Power-bi's avatar
    Juan-Power-bi
    5 months ago

    Hi! This is a known issue with the SQL Server Time data type in DirectQuery mode. Power BI doesn't have a native Time data type — it maps it to Duration internally, and when the table visual paginates (fetches the next batch of rows as you scroll), the query it generates can sometimes produce a type mismatch for certain Time values, especially anything involving midnight or values close to 24-hour boundaries.
    The cleanest fix is to cast the Time column to a string on the SQL Server side, either in a view or a calculated column:


    sql-- Create a view or use this in your query
    SELECT
    EmployeeID,
    Product,
    Date,
    CONVERT(varchar(8), Time, 108) AS TimeFormatted -- returns HH:MM:SS
    FROM YourTable


    Then connect Power BI to that view/column instead. You lose the ability to do time arithmetic directly in DAX, but for display purposes it works perfectly and the error goes away.
    If you need to keep it as a proper time value for calculations, another option is to convert it to minutes or seconds as an integer in SQL, and then format it in DAX on the Power BI side.
    The root cause is that Time in SQL Server is a type that Power BI's DirectQuery engine doesn't handle consistently across all paginated fetches — it's been a known rough edge for a while. Using a string or numeric representation at the source is the most reliable workaround. 😊

Juan-Power-bi's avatar
Juan-Power-bi
Super User
5 months ago

Hi! This is a known issue with the SQL Server Time data type in DirectQuery mode. Power BI doesn't have a native Time data type — it maps it to Duration internally, and when the table visual paginates (fetches the next batch of rows as you scroll), the query it generates can sometimes produce a type mismatch for certain Time values, especially anything involving midnight or values close to 24-hour boundaries.
The cleanest fix is to cast the Time column to a string on the SQL Server side, either in a view or a calculated column:


sql-- Create a view or use this in your query
SELECT
EmployeeID,
Product,
Date,
CONVERT(varchar(8), Time, 108) AS TimeFormatted -- returns HH:MM:SS
FROM YourTable


Then connect Power BI to that view/column instead. You lose the ability to do time arithmetic directly in DAX, but for display purposes it works perfectly and the error goes away.
If you need to keep it as a proper time value for calculations, another option is to convert it to minutes or seconds as an integer in SQL, and then format it in DAX on the Power BI side.
The root cause is that Time in SQL Server is a type that Power BI's DirectQuery engine doesn't handle consistently across all paginated fetches — it's been a known rough edge for a while. Using a string or numeric representation at the source is the most reliable workaround. 😊

v-menakakota's avatar
v-menakakota
Community Support
5 months ago

Hi NaqNaq ,

Thanks for reaching out to the Microsoft fabric community forum. 

 

I would also take a moment to thank Juan-Power-bi   , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you .

 

 

Best Regards, 
Community Support Team