DateFormat Error in Power BI using Sharepoint connection

[msft-cap]

Hello

I am working on a PBI report pulling data from Sharepoint files. It was refreshing without any issues until last month. Then started failing with large dataset issue, timeouts. 

To optimize the report, I updated some functions in desktop. and whenever I try to refresh the report, one table is giving me below error even for older dates where refresh went successful in Service until last month.

Appreciate any inputs on this issue. I

[Shruti_D]

Hi @msft-cap ,
Just  wanted to check if you had the opportunity to review the information provided by @Ritaf1983 ?.
If her response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you

[Ritaf1983]

Hi @msft-cap 

One of the columns defined as Date/Time likely contains values with inconsistent or invalid formatting, which causes Power BI to throw a [DataFormat.Error] when trying to parse them.

This usually happens when:

• The column includes values with leading/trailing spaces (e.g., " 01/02/2024").

• The date format in the source file differs from the expected format in Power BI (e.g., U.S. MM/DD/YYYY vs. U.K. DD/MM/YYYY).

• Some values are not valid dates at all.

You have two options to resolve this:

1. Fix the source file – If possible, open the SharePoint file and clean up the problematic column by removing extra spaces and making sure the date format is consistent.

2. Handle it in Power BI – In Power Query:

   • Use Text.Trim() before converting the column to date.

   • Use Using Locale when converting to date to match the original date format (e.g., English (United States) or English (United Kingdom)).

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly