How to Import a Personal OneDrive Excel File with Power Query
Question details
The user needs to import an Excel workbook stored on Personal OneDrive into Power Query, but the default shortened sharing link is failing.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Attempting to use the From Web connector in Power Query to load data from an Excel file hosted on Personal OneDrive.
- Observed behavior
- Excel Power Query's From Web connector rejects the shortened sharing link or fails to retrieve the workbook due to URL redirects or authentication requirements.
Ensure that your Excel file is saved to your Personal OneDrive and that you have permission to generate a sharing link. You will need to obtain a direct access URL, as standard short links cannot be interpreted by the From Web connector.
Modify the OneDrive Link to a Direct Download URL
Convert the standard OneDrive sharing link into a direct download URL so Power Query can directly access the file content without web page redirects.
Power Query cannot parse a web page wrapper; it requires the actual file data. By changing the URL parameters, you force OneDrive to deliver the file directly.
Open your Personal OneDrive in a web browser, locate your target Excel file, right-click it, and select 'Share' or 'Copy link'. Choose a permission level (e.g., 'Anyone with the link') and copy the generated URL.
Paste the link into a text editor. Depending on the link format, alter the parameters to enforce a download. For example, replace '?web=1' with '?download=1', or change the '1drv.ms' short link into the expanded direct file path.
Open Excel, navigate to the 'Data' tab, click 'Get Data', choose 'From Other Sources', and select 'From Web'.
Paste your modified direct download URL into the dialog box and click 'OK'. When the Navigator window appears, select the tables or sheets you wish to load into your workbook.
Use Local OneDrive Sync Folder
Bypass web connectors entirely by syncing your Personal OneDrive to your local computer and importing the file as a local workbook.
Looking for a Lighter, Faster Spreadsheet Alternative?
While Microsoft Excel's Power Query offers advanced data extraction, dealing with link-sharing rules and authentication errors can be frustrating. If you need a fast, reliable, and completely free office suite for your daily spreadsheet tasks, try WPS Office. It provides robust compatibility with Excel files and a highly intuitive user interface.
- 1. Download WPS Office: Visit the official WPS Office website to download the free suite for your operating system.
- 2. Install and Open: Run the installer, follow the quick setup steps, and open WPS Spreadsheets.
- 3. Edit Seamlessly: Open your existing .xlsx files instantly and enjoy complete formatting retention without the hassle of complicated web queries.
Frequently Asked Questions
Why does Power Query say 'Unable to connect' when I use my OneDrive sharing link?
Power Query's 'From Web' connector expects a direct file stream. Standard OneDrive sharing links often point to an HTML web-page wrapper (the OneDrive viewing interface) or involve redirects that Power Query cannot interpret directly.
How do I force a OneDrive sharing link to download directly?
You can typically force a direct download by altering the parameters at the end of the URL. For example, changing '?web=1' to '?download=1' or appending '&download=1' to the link depending on the specific URL structure provided by OneDrive.
Can I use Power Automate instead of Power Query for this?
Yes. If dealing with shortened URLs and redirects proves too difficult, you can set up a Power Automate flow that uses the OneDrive connector to retrieve the file content securely and push it to a destination where Excel can easily read it.
