Fix Excel Power Query Web Refresh Unexpected Error & Invalid CRLF
Question details
The user is unable to refresh Power Query data from a web URL, receiving an "Unexpected Error" stating the input is not a valid Excel document or contains invalid CRLF characters.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Refreshing web-based data sources across multiple Excel workbooks via Power Query.
- Observed behavior
- The data refresh fails with an unexpected error message about invalid CRLF characters or an invalid Excel document, even in completely new workbooks.
Before troubleshooting your query settings, open the web data URL in a standard browser to verify that the server is online and the source file has not been deleted or moved.
Reauthenticate the Web Data Source Connection
This is the most effective solution for resolving web refresh errors caused by expired, missing, or corrupted authentication credentials in Power Query.
When Excel suddenly fails to refresh web-based queries across both existing and blank workbooks, the root cause is typically expired login credentials rather than a corrupted file. Reinstalling Office will not fix this issue because data source credentials are saved in your user profile settings.
By clearing and updating your permissions in the Data Source Settings, you can restore access to the web file.
Open your Excel workbook, navigate to the Data tab on the top ribbon, click on Get Data, and select Data Source Settings from the dropdown menu.
In the Data Source Settings dialog box, select the Global permissions or Current Workbook tab to find the specific web URL that is failing to refresh.
Select the URL, click Edit Permissions, and under the Credentials section, click Edit. Choose the appropriate account type (such as Organizational account, Basic, or Windows) and sign in again.
Click Save to apply the new credentials, close the settings window, and click Refresh All in the Data tab to verify the connection is working.
Clear the Power Query Cache
Clearing the cache helps resolve background loading glitches and forces Excel to fetch fresh data from your web source.
Process Spreadsheets Easily with WPS Office
If you are tired of troubleshooting complex Power Query errors and credential issues, WPS Office offers a streamlined, lightweight spreadsheet alternative. It allows you to easily import, edit, and export data without heavy processing overhead, ensuring you can get your work done quickly.
- 1. Download and Install: Visit the official WPS Office website to download and install the free spreadsheet application.
- 2. Open Your Data: Launch WPS Spreadsheets and open your .xlsx or .csv files directly to seamlessly manage your data.
Frequently Asked Questions
Why does Excel say my web data contains invalid CRLF characters?
This error typically occurs when the web server denies access due to expired credentials and returns an HTML error page (like a login or 401 Unauthorized page) instead of the expected data file. Excel tries to read this HTML page as data, resulting in the invalid characters error.
Will reinstalling Microsoft Office fix this unexpected query error?
No. Reinstalling Microsoft Office generally does not resolve authentication-related Power Query errors. The issue stems from expired login tokens saved in your user profile's Data Source Settings, which are usually retained even if the application is reinstalled.
How do I determine if the issue is with my Excel file or the web connection?
Create a completely new, blank workbook and attempt to set up a new web query using the same URL. If the new workbook throws the exact same 'Unexpected Error', the issue is related to your account credentials or the web server, not a corrupted Excel file.
