Power Query Problems

How to Use a SharePoint Excel File as a Power Query Source

John Wilson Oct 15, 2026 871 views

Question details

The user needs to connect an Excel workbook stored in SharePoint as a data source in Power Query without using a locally synchronized copy.

How to Use a SharePoint Excel File as a Power Query Source

Product: Microsoft Excel
Device & OS: not provided
Scenario: Establishing a direct cloud connection to a SharePoint-hosted Excel file for automated data querying and consolidation across workbooks.
Observed behavior: The goal is to successfully import data using the "From Web" or "SharePoint Folder" connectors by applying the correct SharePoint URL path and organizational authentication.

Before you start

Ensure you have the appropriate Microsoft 365 credentials and active read permissions for the SharePoint document library where your target Excel file is stored.

Solution 1Recommended

Connect using the From Web Connector

Use this method to establish a direct connection to a single SharePoint Excel workbook using its dedicated web path.

This is the most direct approach when you only need to pull data from a specific file on SharePoint. It avoids the need for local OneDrive synchronization, ensuring the query pulls the latest cloud version.

Copy the SharePoint Path

Open the source Excel file directly in SharePoint. Navigate to File > Info, and click on 'Copy Path' to copy the document's direct URL to your clipboard.

Launch the Web Connector

Open the destination Excel workbook where you want to load the data. Go to the Data tab on the ribbon, select 'Get Data', then click 'From Other Sources', and choose 'From Web'.

Paste the URL

Paste the copied SharePoint path into the URL box. If the URL ends with '?web=1', delete that specific text from the end of the URL before clicking OK.

Authenticate and Connect

When the Access Web Content dialog appears, select 'Organizational account' from the left sidebar. Click 'Sign in' and enter your Microsoft 365 credentials. Once authenticated, click 'Connect' to open the Power Query Navigator and select your data tables.

Authentication Tip: Always choose 'Organizational account' when connecting to SharePoint files. Choosing 'Windows' or 'Anonymous' will result in an access error.

Solution 2

Connect using the SharePoint Folder Connector

Best for combining data from multiple Excel files stored in a single SharePoint library.

Free Microsoft Office alternative

Try WPS Office for Your Data Management Needs

While advanced Power Query integrations with SharePoint are specific to the Microsoft 365 ecosystem, WPS Office provides a lightweight, highly compatible, and free alternative for everyday spreadsheet tasks, data analysis, and seamless cross-platform editing.

1. Download and Install: Visit the official WPS Office website to download the free installation package for your operating system.
2. Launch WPS Spreadsheets: Open the WPS Spreadsheets application to manage your data and start creating dynamic charts.
3. Open Your Excel Files: Directly open your existing Excel workbooks in WPS Office to experience seamless format compatibility and advanced data analysis features.

Fully compatible with Microsoft Excel formats including .xlsx, .xls, and .csv.Lightweight architecture ensures fast performance even when handling large datasets.Built-in cloud syncing and secure file sharing without complex server configurations.Familiar tabbed interface makes it incredibly easy to switch from other office suites.

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting an authentication error when connecting to the SharePoint file?

This usually happens if you attempt to use 'Windows' or 'Anonymous' authentication settings. To fix this, clear your global data permissions in Power Query (Data > Get Data > Data Source Settings), try connecting again, and ensure you select 'Organizational account' and sign in with your Microsoft 365 credentials.

Do I need to modify the SharePoint path after copying it?

Yes. When you copy the path from an online Excel file, it typically appends '?web=1' to the end of the URL. You must delete this parameter from the end of the URL before clicking OK in the 'From Web' connector to prevent connection errors.

Can I use this method if the SharePoint file is protected with a password?

No, Power Query cannot natively read data from an Excel workbook that is encrypted with a 'password to open'. You will need to remove the password encryption from the source file in order to use it as a Power Query data source.

Will my Power Query update automatically when the SharePoint file is modified?

Data does not refresh in real-time. However, you can configure your query to refresh automatically when the destination file is opened, or set a background refresh interval by navigating to Data > Queries & Connections, right-clicking your query, and selecting 'Properties'.

Excel & Data Problems