Excel Power Query: connect to OneDrive files online
Excel

Excel Power Query: connect to OneDrive files online

by HubSite 365 about Leila Gharani [MVP]

Connect Power Query to OneDrive or SharePoint with a cloud link so colleagues can refresh without relying on your local file path.

Key insights

  • A file path from File Explorer can point to a local synchronized copy that differs between users.
  • For an Excel workbook, choose File > Info > Copy Path—not Copy Local Path—and remove the trailing ?web=1.
  • For an existing query, change its source in Data > Get Data > Data Source Settings.
  • For a SharePoint text or CSV file, copy its Path from the file's Details pane.
  • If credentials expire or a password changes, sign in as a different user with the updated account.

Excel Power Query can connect to OneDrive or SharePoint files through their cloud links, avoiding refresh errors caused by a path tied to one computer. This approach matters to people who share workbooks and expect colleagues to refresh the same query. Leila Gharani [MVP] demonstrates the steps for Excel workbooks and text files, as well as how to update an existing connection.

What it does

When you browse to a synchronized OneDrive or SharePoint file through File Explorer, Power Query may save the local path on your computer. That path works for you, but another person can have a different folder path, so their refresh may fail even if they can access the file online.

Using the file’s cloud link changes what Power Query uses as its source. The query points to the OneDrive or SharePoint location rather than to a local folder on the computer that created it. That makes the connection more suitable for a workbook shared with colleagues who need to refresh it.

The same basic idea applies to an existing connection and to text or CSV files. The steps to obtain the cloud link differ by file type: use Copy Path for an Excel workbook, and copy the Path shown in the SharePoint Details pane for a text file.

Who it is for and what you need

This method is for Excel users who import files from OneDrive for work or school or SharePoint and share the resulting Power Query workbook with others. It is especially relevant when a colleague can open the source file but gets an error when refreshing the query.

You need access to the source file and an Excel workbook in which to create or edit the query. The first time you connect, Excel may ask you to authenticate with your work account. OneDrive personal links work differently, so the steps described here apply to work or school OneDrive and SharePoint.

If you are fixing an existing workbook, you can change the source for a connection that already exists rather than rebuilding every query. This is useful when multiple queries point to the same file.

How it works

  1. Copy the workbook’s cloud path. Open the source workbook, choose File > Info, then select Copy Path. Do not choose Copy Local Path.
  2. Use the link in Power Query. In the workbook that will import the data, go to Data > Get Data > From File > From Excel Workbook. Paste the copied link into the file selection box.
  3. Remove the extra ending. If the link ends in ?web=1, delete that part so the address ends with the workbook’s .xlsx extension. Then press Enter or select Open.
  4. Authenticate and select data. Sign in with your work account if prompted. Select the workbook content you need, then load or transform the data.

To update a query that already uses a local path, go to Data > Get Data > Data Source Settings. Select the relevant connection, choose Change Source, and replace the local path with the cloud path copied from the source workbook. Remove ?web=1 from the end, confirm the change, and close the settings.

For a SharePoint text or CSV file, open its location in a browser and find the file in the document library. Open the file’s menu, choose Details, and locate Path. Copy that direct link, then in Excel choose Data > Get Data > From File > From Text/CSV and paste it into the file selection box. If the copied link ends in ?web=1, remove that suffix.

Limitations and alternatives

The local-path method can work on the computer where the query was created, but it is not portable when another user’s synchronized folder has a different path. The cloud-link method addresses that path mismatch; it does not replace the need for users to have access to the file.

Use the correct link for the file type. For Excel workbooks, Copy Path is the option to use; for a SharePoint text or CSV file, copy Path from the Details pane rather than copying the browser address. A link ending in ?web=1 needs that suffix removed before connecting.

Authentication can also require attention later. If a password changes or credentials expire, the query may report that the credentials are invalid. Select Sign in as different user, choose the account, and sign in with the current password.

OneDrive personal sharing links follow a different process from work or school OneDrive and SharePoint links. If your workbook uses a personal OneDrive file, do not assume these same link steps apply.

Sources

Frequently asked questions

How do I connect Excel Power Query to a OneDrive workbook?

In the source workbook, choose File > Info > Copy Path. In Excel, use Data > Get Data > From File > From Excel Workbook, paste the link, and remove a trailing ?web=1 before opening it.

How do I fix a Power Query connection that uses a local OneDrive path?

Go to Data > Get Data > Data Source Settings, select the connection, and choose Change Source. Paste the cloud path from Copy Path, remove ?web=1 if present, and confirm.

How do I connect Power Query to a SharePoint CSV file?

In SharePoint, open the file’s Details pane and copy the Path. In Excel, choose Data > Get Data > From File > From Text/CSV, paste the link, and remove a trailing ?web=1 if present.

What should I do if Power Query says my credentials are invalid?

Choose Sign in as different user, select your account, and sign in with your updated password.