Fix Excel Power Query Authentication Issues With OneDrive

If Power Query cannot authenticate to OneDrive, check the account type, connector and source address before resetting credentials. A work or school OneDrive folder, a personal OneDrive sharing link and a locally synced folder need different connection approaches.

The task here is to refresh a master table from OneDrive-hosted files, then export a reviewed dataset for public sharing. For example, three monthly workbooks might each contain a table named Records with columns record_id, month, category and amount. A successful refresh should include only those intended files—not the master workbook or unrelated documents.

The menu paths below are for desktop Excel on Windows. Connector availability and refresh support differ across Excel versions; browser Excel does not support refresh for sources requiring an on-premises gateway. See Microsoft’s Excel Power Query support matrix.

1. Match the connection to the source

Your source	Connection to use	Authentication approach
Several files in OneDrive for work or school	SharePoint Folder, using the OneDrive personal-site address	Organizational account
One workbook in OneDrive for work or school	From Web, using the workbook’s file path	Organizational account
Files downloaded into a local OneDrive sync folder	From Folder, using the local filesystem path	OneDrive handles cloud sign-in; Power Query reads local files
Private files in personal OneDrive	Use a locally synced folder for this desktop workflow	Do not make private files public to bypass authentication

Microsoft documents the SharePoint Folder and Web routes for OneDrive for work or school. Its documented Web route for OneDrive for home requires the file to be publicly available and uses Anonymous authentication. That is not a suitable workaround for private source data.

2. Reset only the relevant saved credentials

For a work or school cloud connection:

1. In Excel, open Data > Get Data > Data Source Settings.
2. Select Data sources in current workbook and locate the failing source.
3. Check its address and select Edit Permissions to inspect the stored credential type.
4. If it uses the wrong identity or authentication type, select Clear Permissions for that source.
5. Reconnect using Organizational account, signing in with the work or school identity that has access to the source.

If the wrong setting persists, inspect Global permissions for the same address. Avoid Clear All Permissions: it removes saved permissions for unrelated sources too. Microsoft documents these controls in its data source permissions guide.

Also check the URL level to which credentials apply. Power Query can reuse credentials for an address and its subaddresses, so a broadly saved setting can affect multiple connections. Microsoft explains this credential scope behavior.

3. Correct the address and connector

Several work or school files: SharePoint Folder

Choose Data > Get Data > From File > From SharePoint Folder, as shown in Microsoft’s Excel import instructions. Supply the OneDrive personal-site root, not a sharing link or the address of an individual workbook.

To find it, open your work or school OneDrive in a browser. Keep the actual host and the /personal/your_user_identifier portion of its address; remove the following /_layouts/… page path and query parameters. Use your own address rather than constructing an identifier from your email. Microsoft describes this OneDrive-specific URL format.

Sign in with Organizational account, then choose Transform Data. Filter the file list to the intended folder and files before combining. For the sample monthly dataset, keep the monthly inputs and exclude the master workbook and temporary files. Ensure every input has the same table name and column structure. The SharePoint Folder connector includes files in subfolders, so inspect the filtered list rather than immediately combining everything.

One work or school workbook: From Web

Open the source workbook in desktop Excel, then select File > Info > Copy Path. In your destination workbook, use Data > From Web, paste that path and remove the trailing ?web=1 if present. Authenticate with Organizational account, then select the required table in Navigator. These are Microsoft’s documented online-workbook connection steps.

If Navigator shows webpage tables rather than workbook tables or sheets, check whether you supplied a browser or sharing-page address instead of the file path.

Local sync: a desktop alternative

For personal OneDrive—or a desktop workflow that can use local files—sync the source folder first. In Windows File Explorer, right-click that folder and select Always keep on this device. Wait for downloading and synchronization to finish. This setting also downloads new files added to the folder, according to Microsoft’s Files On-Demand documentation.

Connect with Data > Get Data > From File > From Folder, selecting the local folder. Choose Transform Data and filter before combining, as described in the Folder connector documentation.

This changes the connection to local file access; it does not repair cloud authentication. Another computer must have the files and a valid local path too.

4. If authentication still fails

Check these specific blockers:

• Access permissions: SharePoint Folder requires Read access to the site, document library and folders leading to the file. Opening a shared workbook alone does not verify all those permissions.
• Filename characters: Microsoft identifies #, % and $ as possible causes of unusual SharePoint authentication errors. Ask the owner to rename affected files if applicable.

Both checks are covered in Microsoft’s SharePoint Folder troubleshooting guidance. If the correct account, address and permissions still fail, give your administrator the exact error, connector and source address through a private support channel—not your password or access tokens.

Verify the refreshed table before publishing

After correcting the connection, run the query refresh and wait for it to finish without errors. Compare the included-file list, row count, duplicate IDs, latest reporting month and totals against the inputs. Keep a source filename column in your working query if it helps trace unexpected rows. For a fuller combination workflow, see Combine 2 Excel Files Into 1 Dashboard.

Create a separate, values-only release file containing only approved public columns. Remove personal information, confidential fields and internal file paths. Record the source and refresh date, then follow the CSV export checks. Publish the reviewed extract—not the connected working workbook—and label it with the date of its successful refresh.