'%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 Not Importing OneDrive Excel Files
Question details
The user is unable to import an Excel workbook from a OneDrive sharing link using Power Query, resulting in missing sheets or failed connections.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Attempting to connect and import data from a shared OneDrive Excel file into Power Query.
- Observed behavior
- The workbook sheets fail to appear in the Power Query navigator due to authentication changes, browser link formatting issues, or the use of unsupported software like Excel 2010.
Ensure you are using a supported version of Microsoft Office (newer than Office 2010) and verify that your Microsoft account has direct read permissions for the shared OneDrive workbook.
Use a Direct Downloadable File Path in a Supported Excel Version
Use this method to bypass browser link authentication issues by extracting the direct file path from the Excel desktop application.
Excel 2010 is no longer supported by Microsoft, meaning its Power Query add-in cannot handle modern OneDrive authentication protocols. To resolve this, you must use a modern Excel version and a direct file path rather than a standard web sharing link.
Open a currently supported version of Microsoft Excel, such as Microsoft 365 or Office 2021, as Office 2010 reached its end of support on October 13, 2020.
Open the target shared workbook in the Excel desktop app, navigate to 'File' > 'Info', and click the 'Copy path' button.
Paste the copied link into a text editor (like Notepad) and delete the '?web=1' parameter from the very end of the URL.
Go to the 'Data' tab, select 'Get Data' > 'From Web', paste your modified direct URL, and sign in with your organizational or Microsoft account when prompted to load your sheets.
Seek Assistance in the Power Platform Community
If you are dealing with advanced automation scenarios or Power Apps integration that fails with OneDrive files, consult the official Microsoft community.
Upgrade to WPS Office for a Modern, Hassle-Free Spreadsheet Experience
Since Office 2010 is no longer supported and can cause frustrating compatibility or connectivity issues, consider switching to WPS Office. It provides a lightweight, highly compatible, and free alternative for managing your spreadsheets and cloud files effortlessly.
- 1. Download and Install: Visit the official WPS website and download the free WPS Office suite for your device.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheets and open your local or cloud-stored Excel files directly.
- 3. Edit and Share: Use the built-in tools to manage your data and securely share files via WPS Cloud.
Frequently Asked Questions
Why does my OneDrive sharing link not work in Power Query?
Standard OneDrive sharing links often direct to a web viewer interface rather than the raw file data. Power Query requires a direct file path to successfully read the workbook data without browser authentication interfering.
Can I still use Power Query reliably in Excel 2010?
No. Support for Office 2010 ended on October 13, 2020. Its Power Query add-in is outdated and can no longer reliably authenticate with modern cloud services and sharing protocols used by OneDrive or SharePoint.
How do I fix authentication errors when importing from OneDrive?
Ensure you are using the 'Organizational account' sign-in option in the Power Query credentials prompt, and verify that your specific user account has direct read permissions for the shared file.
