'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Power Query Errors with OneDrive Personal Excel Links
Question details
The user needs to resolve an HTML data type error that occurs in Power Query when attempting to import data from an Excel file hosted on OneDrive Personal.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing Excel data from a OneDrive Personal share link using the Web.Contents function in Power Query.
- Observed behavior
- Power Query returns an HTML data type error because the OneDrive download URL redirects to a web page (such as a file viewer or sign-in page) instead of returning the raw Excel file.
Make sure you have the exact sharing link from your OneDrive Personal account and verify that the file is not restricted by specific sign-in permissions that block automated web queries.
Modify the OneDrive Link for Direct Download
Power Query requires a direct file link to read the data correctly. You must manually alter the standard OneDrive Personal sharing URL to bypass the web viewer and force a file download.
By default, OneDrive Personal sharing links are designed for browsers and load an HTML-based Excel viewer. Power Query cannot parse this HTML interface, which is why the data type error occurs.
Navigate to your file in OneDrive Personal, right-click the Excel file, select 'Share', and copy the standard sharing link.
Paste the link into a text editor. Look at the end of the URL; if it ends with '?web=1' or similar parameters, change it to '?download=1'. In some link structures, you may need to replace the word 'embed' with 'download' in the URL path.
Open Excel, go to the 'Data' tab, click 'Get Data' > 'Data Source Settings'. Locate your previous OneDrive connection, click 'Change Source', and paste the newly modified direct-download URL.
Clear and Reset Data Source Permissions
Outdated or incorrect authentication methods can cause OneDrive to redirect Power Query to a login page, resulting in an HTML document error.
Seek Help from the Excel Community
If Microsoft changes OneDrive URL structures and the issue persists, consulting Power Query specialists in official forums is the best next step.
Try WPS Office for Seamless Spreadsheet Management
If you are frustrated by complex Power Query errors, authentication issues, and broken OneDrive links in Microsoft Excel, WPS Office offers a highly compatible, free, and lightweight alternative. Enjoy a familiar interface and robust spreadsheet tools without the complex cloud setup.
- 1. Download and Install: Visit the official WPS Office website and download the free version for your operating system.
- 2. Open Your Excel File: Launch WPS Spreadsheets and open your existing .xlsx files directly, preserving all your data and formatting.
- 3. Work Without Hassle: Use WPS Office's built-in data tools to filter, analyze, and manage your data without worrying about complex web query errors.
Frequently Asked Questions
Why does Power Query return an HTML error for my Excel file?
This happens when the provided OneDrive link redirects Power Query to a web-based file viewer or a login page instead of the direct raw file. Power Query attempts to read the webpage's HTML code instead of the actual spreadsheet data.
Can I use OneDrive Business links the same way as OneDrive Personal?
No, OneDrive for Business uses SharePoint's backend and requires 'Organizational account' authentication in Power Query. Personal links generally require modifying the URL for direct download and using 'Anonymous' access.
How do I safely share my Power Query issue online?
Always remove sensitive data, private access keys, and personal authentication tokens from your Web.Contents URL and M code before posting in public forums or communities.
