It sure feels great when you finally set up your data sources and shape data just the way you want. Hopefully, when you refresh data from an external data source, the operation goes smoothly. But that’s not always the case. Changes to the flow of data all along the way can cause problems that end up as errors when you attempt to refresh data. Some errors may be easy to fix, some may be transient, and some may be hard to diagnose. What follows is a set of strategies you can take to handle errors that come your way.
There are two types of errors that might occur when you refresh data.
Local If an error occurs in your Excel workbook, then at least your troubleshooting efforts are limited and more manageable. Perhaps refreshed data caused an error with a function, or the data created an invalid condition in a drop-down list. These errors are bothersome, but fairly easy to track down, identify, and fix. Excel also has improved error handling with clearer messages and context-sensitive links to targeted help topics to help you figure out and fix the problem.
Remote However, an error that comes from a remote external data source is another matter entirely. Something has happened in a system that might be across the street, half-way around the world, or in the cloud. These types of errors require a different approach. Common remote errors include:
-
Couldn't connect to a service or resource. Check your connection.
-
The file you're trying to access could not be found.
-
The server is not responding and may be undergoing maintenance.
-
This content is not available. It may have been removed or is temporarily unavailable.
-
Please wait... the data is being loaded.
What follows are a few suggestions to help you deal with errors you may encounter.
Find and save the specific error First examine the Queries & Connections pane (Select Data > Queries & Connections, select the connection, and then display the flyout). See what data access errors occurred and make note of any additional details provided. Next, open the query to see any specific errors with each query step. All errors are displayed with a yellow background for easy identification. Write down or screen capture the error message information, even if you don’t fully understand it. A colleague, administrator, or a support service in your organization may be able to help you understand what happened and propose a solution. For more information, see Dealing with errors in Power Query.
Get help information Search the Office Help and Training site. Not only does this contain extensive help content, but also troubleshooting information. For more information see Fixes or workarounds for recent issues in Excel for Windows.
Leverage the technical community Use Microsoft Community sites to search for discussions pertaining specifically to your problem. It is highly likely you are not the first person to experience the problem, others are dealing with it, and may have even found a solution. For more information, see The Microsoft Excel Community and Office Answers Community.
Search the web Use your preferred search engine to look for additional sites on the web that may provide pertinent discussions or clues. This can be time-consuming, but it’s a way to cast a wider net to look for answers to particularly thorny questions.
Contact Office Support At this point, you probably understand the issue much better. This can help you focus your conversation and minimize time spent with Microsoft Support. For more information, see Microsoft 365 and Office Customer Support.
Although you may not be able to fix the problem, you can find out precisely what is the problem to help others understand the situation and solve it for you.
Issues with services and servers Intermittent network and communication errors are a likely culprit. The best you can do is wait and try again. Sometimes, the problem just goes away.
Changes to location or availability A database or file has been moved, corrupted, taken offline for maintenance, or the database has crashed. Disk devices can become corrupt, and files become lost. For more information, see Recover lost files on Windows 10.
Changes to authentication and privacy It can suddenly happen that a permission no longer works, or a change was made to a privacy setting. Both events can prevent access to an external data source. Check with your administrator or the administrator of the external data source to see what has changed. For more information, see Manage data source settings and permissions and Set privacy levels.
Opened or locked files If a text, CSV, or workbook is open, any changes to the file are not included in the refresh until the file has been saved. Also, if the file is open it may be locked, and can’t be accessed until it is closed. This can happen when the other person is using a non-subscription version of Excel. Ask them to close the file or check it in. For more information, see Unlock a file that has been locked for editing.
Changes to schemas at the backend Somebody changes a table name, column name, or data type. This is almost never wise, can have a huge impact, and is especially dangerous with databases. One hopes that the database management team has put the proper controls to prevent this from happening, but slipups do occur.
Blocking errors from query folding Power Query tries to improve performance whenever it can. It’s often better to run a database query on a server to take advantage of greater performance and capacity. This process is called query folding. However, Power Query blocks a query if there is a potential for data to be compromised. For example, a merge is defined between a workbook table and a SQL Server table. The workbook data privacy is set to Privacy, but the SQL Server data is set to Organizational. Because Privacy is more restrictive than Organizational, Power Query blocks the information exchange between the data sources. Query folding occurs behind the scenes, so it may surprise you when a blocking error occurs. For more information, see Query folding basics, Query folding, and Folding with Query Diagnostics.
Often with Power Query, you can find out precisely what is the problem and fix it yourself.
Renamed tables and columns Changes to original table and column names or column headers almost certainly will cause problems when you refresh data. Queries rely on table and column names to shape data in almost every step. Avoid changing or removing original table and column names, unless your purpose is to make them match with the data source.
Changes to data types Data type changes can sometimes cause errors or unintended results, especially in functions which might require a specific data type in the arguments. Examples include, replacing a text data type in a number function or attempting to do a calculation on a non-numeric data type. For more information see Add or change data types.
Cell-level errors These types of errors won't prevent a query from loading, but they display Error in the cell. To see the message, select whitespace in a table cell containing Error. You can remove, replace, or just keep the errors. Examples of cell errors include:
-
Conversion You attempt to convert a cell containing NA to a whole number.
-
Mathematical You try to multiply a text value by a numeric value.
-
Concatenation You attempt to combine strings but one of them is numeric.
Safely experiment and iterate If you are not sure that a transformation could have a negative impact, copy a query, test out your changes, and iterate through variations of a Power Query command. If the command doesn't work, just delete the step you created and try again. To quickly create sample data with the same schema and structure , create an Excel table of several columns and rows, and then import it (Select Data > From Table/Range). For more information, see Create a table and Import from an Excel Table.
You may feel like a kid in a candy store when you first grasp what you can do with data in the Power Query Editor. But resist the temptation to eat all the candy. You want to avoid making transformations that might inadvertently cause refresh errors. Some operations are straightforward, such as moving columns to a different position in the table, and should not lead to refresh errors down the road, because Power Query tracks columns by their column name.
Other operations might lead to refresh errors. One general rule of thumb can be your guiding light. Avoid making significant changes to the original columns. To play it safe, copy the original column with a command (Add a Column, Custom Column, Duplicate Column, and so on), and then make your changes to the copied version of the original column. What follows are the operations that can sometimes lead to refresh errors and some best practices to help things go more smoothly.
|
Operation |
Guidance |
|---|---|
|
Filtering |
Improve efficiency by filtering data as early as possible in the query and remove unneeded data to reduce unnecessary processing. Also, use AutoFilter to search for or select specific values and take advantage of type-specific filters available in date, datetime, and date timezone columns (such as Month, Week, Day). |
|
Data types and column headers |
Power Query automatically adds two steps to your query immediately after the first Source step: Promoted Headers, which promotes the first row of the table to be the column header, and Changed Type, which converts the values from the Any data type to a data type based on the inspection of the values from each column. This is a useful convenience, but there may be times when you want to explicitly control this behavior to prevent inadvertent refresh errors. For more information, see Add or change data types and Promote or demote rows and column headers. |
|
Rename a column |
Avoid renaming the original columns. Use the Rename command for columns that are added by other commands or actions. For more information, see Rename a column. |
|
Split Column |
Split copies of the original column, not the original column. For more information, see Split a column of text. |
|
Merge Columns |
Merge copies of the original columns, not the original columns. For more information, see Merge columns. |
|
Remove a column |
If you have a small number of columns to keep, use Choose Column to keep the ones you want. Consider the difference between removing a column and removing other columns. When you choose to remove other columns, and you refresh your data, new columns added to the data source since your last refresh might remain undetected because they would be considered other columns when the Remove Column step is again executed in the query. This situation won’t occur if you explicitly remove a column. Tip There is no command to hide a column (as there is in Excel). However, if you have a lot of columns and you want to hide many of them to help focus your work, you can do the following: remove the columns, remember the Step that was created, and then remove that step before you load the query back to the worksheet. For more information, see Remove columns. |
|
Replace a value |
When you replace a value, you are not editing the data source. Rather, you are making a change to the values in the query. The next time you refresh your data, the value you searched for may have slightly changed, or no longer be there, and so the Replace command may not work as originally intended. For more information, see Replace values. |
|
Pivot and Unpivot |
When you use the Pivot Column command, an error can occur when you pivot a column, don’t aggregate values, but more than a single value is returned. This situation can arise after a refresh operation that changes the data in an unanticipated way. Use the Unpivot Other Columns command when not all columns are known, and you want new columns added during a refresh operation to also be unpivoted. Use the Unpivot Only Selected Columns command when you don’t know the number of columns in the data source, and you want make sure that selected columns remain unpivoted after a refresh operation. For more information, see Pivot columns and Unpivot columns. |
Prevent errors from occurring If an external data source is managed by another group in your organization, they need to be aware of your dependency on them and to avoid changes to their systems that can cause problems downstream. Keep a record of impacts on data, reports, charts, and other artifacts that depend on the data. Set up lines of communication to ensure that they understand the impact and take the necessary steps to keep things running smoothly. Find ways to create controls that minimize unnecessary changes and anticipate consequences of necessary changes. Admittedly, this is easy to say and sometimes hard to do.
Future-proof with query parameters Use query parameters to mitigate changes to, for example, a data location. You can design a query parameter to substitute a new location, such as a folder path, file name, or URL. There are additional ways to use query parameters to mitigate issues. For more information, see Create a parameter query.
