SharePoint Source Evaluating won't load

[Jeff_Huber_1967]

Earlier this week I encounted a strange error for one my datasets on the power bi service. The automatic refresh didn't complete and the error was "We couldn't convert to number".  I was perplexed as it's been working fine for months. Nonetheless, I downloaded the file and opened it using Power BI desktop. After spending two trying to resolve the issue, I made some changes to

the queries in power query. I am able to see the changes for all the queries using refresh preview button in power query. When I apply the changes, two of the queries won't load. The SharePoint Source (Reference) is stuck on evaluating. The data soure is text files that are uploaded to the shared service on a weekly basis. I have tried loading the source file into excel to see if I can find any issues. I cannot. I have had issues with queries loading before but I have never seen the Sharepoint Source (Reference) stuck on evaluating before. I will upload a screen shot. 

[v-mdharahman]

Hi @Jeff_Huber_1967,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are facing issues with your automatic refresh of your datasets and when you are trying to open the file in excel it gets struck in "evaluating" phase. As @Ritaf1983 already responded to your query, please go through the provided response and check if it solves your issue.

I would also take a moment to thank @Ritaf1983, 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

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[v-mdharahman]

Hi @Jeff_Huber_1967,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

[v-mdharahman]

Hi @Jeff_Huber_1967,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.

Thank you.

[Ritaf1983 · Accepted solution]

Hi @Jeff_Huber_1967 

This is a fairly common issue and typically isn't directly related to the SharePoint source itself, but rather to mismatches between the data types in your source files and Power BI's automatically inferred data types.

To resolve this issue, try the following steps:

1. Disable Power BI's automatic data type detection:
   In Power Query, disable automatic detection and manually assign the appropriate data types, especially numeric and date columns.

2. Inspect date fields carefully:
   Verify that all date fields have consistent and correct formatting. If irregularities exist, correct them directly in the source data.

3. Check for whitespace or special characters:
   Look closely for hidden whitespace or special characters within numeric or date fields, as these can prevent proper type conversions.

4. Use 'Keep Errors' to diagnose problematic rows:
   In Power Query, right-click the problematic column and select "Keep Errors". This will isolate rows causing the conversion errors. You can then either fix these rows directly in your source or remove them if they’re not necessary.

5. Review transformation steps in Power Query:
   Sometimes, an earlier transformation step (such as merges, expands, or value replacements) can cause errors that manifest in later steps. Check each step individually to pinpoint the exact cause.

Important: After applying these steps, ensure you validate the fixes on the entire dataset—not just the preview of the first 1000 rows—since issues may occur only deeper in the data.

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