Forum Discussion
Refresh error giving blank cells
I am running into an error where when I refresh my data, it returns blank values in cells where I have formulas. My colleague uses the same sheet and has no issues with refreshes, so I know the logic is correct. We are connected with data through an ODBC connection that pulls from Smartsheet. I have the Smartsheet Live Data Connector downloaded and same settings as my colleague. In power query editor, I have access to all the data with no errors coming through (not a permission issue). Also, when I am done making changes and try to save, I have a message pop up saying, 'performing cleanup' and it just spins forever.
Data before refreshData after refreshFormula showing there should clearly be data displayed and not blanksNo errors in power query editor
May considering below:
- Check Data Types: Ensure that the data types in your Power Query editor match the expected data types in your Excel sheet. Mismatched data types can sometimes cause issues during refresh.
- Recreate the Connection: Try deleting the existing ODBC connection and recreating it. Sometimes, the connection settings might get corrupted, and recreating the connection can resolve the issue.
- Clear Cache: Clear the cache in Power Query to ensure that there are no stale data causing the issue. You can do this by going to Data > Queries & Connections > Query Properties > Clear Cache.
- Check for Hidden Characters: Sometimes, hidden characters or spaces in the data can cause issues. Ensure that there are no hidden characters in your data source.
- Update Drivers: Ensure that you have the latest version of the Smartsheet Live Data Connector and ODBC drivers installed. Outdated drivers can sometimes cause issues.
- Check for Errors in Power Query: Even though you mentioned there are no errors, double-check for any warnings or messages in the Power Query editor that might provide clues.
- Test with a Simple Query: Create a simple query that pulls a small subset of data from Smartsheet to see if the issue persists. This can help identify if the problem is with the specific query or the connection itself.
- Check Excel Settings: Ensure that your Excel settings are the same as your colleague's. Sometimes, differences in settings can cause issues.
- Review Smartsheet Permissions: Double-check that your Smartsheet permissions are the same as your colleague's. Even though you mentioned it's not a permission issue, it's worth verifying.
4 Replies
Hi,
It sounds like the most probable issue is how Excel is handling the data refresh, either due to recalculation timing, Power Query loading settings, or how ODBC is fetching data from Smartsheet. Since your colleague isn't experiencing the same issue, it’s likely related to your local Excel settings, formula references, or cache. Here are some steps that you can probably follow to troubleshoot:
- Recalculation Timing Issue: Excel might be refreshing data before formulas update. Try forcing recalculation with Ctrl + Alt + F9 and enable "Refresh all data before calculating formulas" under File → Options → Formulas (set to Automatic).
- Power Query Loading Blank Values: Check that Power Query is set to "Load to Table" rather than Connection Only. Also, disable "Enable background refresh" under Queries & Connections → Properties to ensure all data is loaded before formulas execute.
- Formula Reference Issue After Refresh: If the table expands or contracts on refresh, fixed cell references (A1:A10) might break. Convert formulas to structured references (Table1[ColumnName]) to maintain integrity.
- ODBC Connection Issue: Even if you have the Smartsheet Live Data Connector installed, re-authenticate under Data → Get Data → Data Source Settings. Also, increase "Fetch Size" in the ODBC Data Source Administrator to prevent partial data loads.
- Excel Cache Issue ("Performing Cleanup" Hanging): Clear Excel’s cache under File → Options → Advanced → Empty Temporary Files Folder. Also, check Task Manager (Ctrl + Shift + Esc) to ensure no stuck EXCEL.EXE processes.
- Final Check: Open Power Query Editor to verify that all expected data is present. If the issue persists, test refreshing on a colleague’s machine to rule out a local environment issue.
Give these steps a try, and let me know if anything changes. Hope this helps! 👍
May considering below:
- Check Data Types: Ensure that the data types in your Power Query editor match the expected data types in your Excel sheet. Mismatched data types can sometimes cause issues during refresh.
- Recreate the Connection: Try deleting the existing ODBC connection and recreating it. Sometimes, the connection settings might get corrupted, and recreating the connection can resolve the issue.
- Clear Cache: Clear the cache in Power Query to ensure that there are no stale data causing the issue. You can do this by going to Data > Queries & Connections > Query Properties > Clear Cache.
- Check for Hidden Characters: Sometimes, hidden characters or spaces in the data can cause issues. Ensure that there are no hidden characters in your data source.
- Update Drivers: Ensure that you have the latest version of the Smartsheet Live Data Connector and ODBC drivers installed. Outdated drivers can sometimes cause issues.
- Check for Errors in Power Query: Even though you mentioned there are no errors, double-check for any warnings or messages in the Power Query editor that might provide clues.
- Test with a Simple Query: Create a simple query that pulls a small subset of data from Smartsheet to see if the issue persists. This can help identify if the problem is with the specific query or the connection itself.
- Check Excel Settings: Ensure that your Excel settings are the same as your colleague's. Sometimes, differences in settings can cause issues.
- Review Smartsheet Permissions: Double-check that your Smartsheet permissions are the same as your colleague's. Even though you mentioned it's not a permission issue, it's worth verifying.
It seemed to be the data types the smartsheet was pulling in. Adjusting the type in power query editor allowed the formulas to calculate properly. Still weird it was not showing up blank for my colleague, but I am glad I got it fixed. Thank you for the detailed response.
I will go through this list, thank you for the reply. Will follow up if it works out.
