Time column is throwing Data Type Error in Power BI

[NaqNaq]

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 Table visual to see the data, it loads  properly. 

But when I scroll down to see the end rows...somewhere in the middle....it breaks and I get the following error.

This visual works perfectly if I dont add the time column and I am able to scroll through all 5000 records successfully. 

This is how time is stored is my database. 

Date (Date/Time) and Time (Time)

This is how things are set in power bi 

I have tried changing the format as well but nothing works. This issue only happens when I scroll through the records. and it happens only when time column is there in the visual. 

Your guidance will be appreciated. Thank you very much. 

[Juan-Power-bi · Accepted solution]

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]

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