Learn how to reliably connect Power Query to Excel workbooks stored in SharePoint and OneDrive — whether you're pulling from a single file or consolidating dozens of regional reports from a document library. This lesson covers the right connection patterns, URL construction, authentication, and refresh strategies that actually work in production.

You've got a team of analysts who each maintain a regional sales workbook in a shared SharePoint document library. Every Monday morning, someone has to manually open each file, copy-paste the data into a master sheet, and pray nothing shifted columns. The whole process takes two hours, produces errors, and nobody enjoys it. Sound familiar?
SharePoint and OneDrive have become the de facto storage layer for collaborative Excel workbooks in most Microsoft 365 organizations. The good news: Power Query can connect directly to those files — not just one at a time, but dynamically, in ways that refresh automatically and survive file moves within the same library. The bad news: getting there requires you to understand a few connection patterns that aren't obvious, some authentication quirks that will burn you if you're not prepared, and some important differences in how URLs work between OneDrive, SharePoint team sites, and SharePoint document libraries.
By the end of this lesson, you'll be able to connect reliably to Excel workbooks stored in SharePoint or OneDrive, navigate their internal structure (sheets, tables, named ranges), filter and clean the extracted data, and set up refresh pipelines that work without manual intervention. You'll also understand how to combine data from multiple workbooks in a document library — a workflow that unlocks serious organizational power.
What you'll learn:
This lesson assumes you're comfortable with the core Power Query workflow — connecting to a source, navigating the query editor, and applying basic transformations. If you need a refresher on the fundamentals, Getting Started with Power Query: Master Connect, Transform, Load for Real-World Data is a solid foundation. You should also be familiar with connecting to local Excel workbooks; the concepts in Connecting to Excel Workbooks and Named Ranges in Power Query: A Practical Foundation Guide apply directly here and will help you understand what's different when the file lives in the cloud.
You'll need:
Before you open Power Query, you need to make a decision: do you want to connect to a single specific file, or do you want to connect to a folder and iterate across all files in it? This isn't just a preference — the two patterns use different connectors, different M functions, and have different tradeoffs.
Pattern 1: Direct file connection — You connect to one workbook by constructing its URL. This is the right choice when you have a single authoritative file (like a finance team's master budget workbook) that gets updated in place, and you want to pull specific sheets or tables from it.
Pattern 2: SharePoint folder connection — You connect to the entire document library as a folder and filter down to the files you want, then combine their contents. This is the right choice for the regional sales workbooks scenario described in the introduction, where multiple files share the same structure.
Both patterns ultimately use the SharePoint infrastructure under the hood, but they expose very different APIs to you in M. We'll cover both in depth, starting with the single-file approach since it's simpler and helps you understand the data structure before you scale up.
The most common source of confusion when connecting to SharePoint files is using the wrong URL. SharePoint gives you several different URLs for the same file, and they don't all work the same way in Power Query.
Here's the rule: you want the site URL, not the file URL.
When you open a SharePoint document library and click "Copy link" on a file, you get a sharing URL that looks something like this:
https://contoso.sharepoint.com/:x:/r/sites/SalesTeam/Shared%20Documents/Regional_Sales_Q3.xlsx?d=w1234...
Do not use that URL in Power Query. It's a sharing link, not a stable programmatic reference. Instead, you want the base SharePoint site URL:
https://contoso.sharepoint.com/sites/SalesTeam
For OneDrive for Business (which is SharePoint under the hood), the pattern looks like:
https://contoso-my.sharepoint.com/personal/jsmith_contoso_com
The file path is handled separately, as you'll see in a moment.
Tip
To find your SharePoint site URL, navigate to the document library in your browser and copy everything up to and including the site name — before /Shared Documents or any folder path. For OneDrive for Business, copy up to and including your personal site path (/personal/username_domain_com).
In Power Query, go to Get Data → Online Services → SharePoint Online List — but wait, that's for lists. For files, you want to use the M function directly. Go to Get Data → Blank Query and open the Advanced Editor. The function you need is SharePoint.Files:
let
Source = SharePoint.Files(
"https://contoso.sharepoint.com/sites/SalesTeam",
[ApiVersion = 15]
)
in
Source
This connects to the SharePoint site and returns a table of every file in the site's document libraries — a file browser you can filter in M. The ApiVersion = 15 parameter targets the SharePoint REST API version compatible with modern SharePoint Online; you should always include it.
When you run this, Power Query will prompt you to authenticate. Choose Microsoft Account and sign in with your Microsoft 365 credentials. For organizational deployments, you may be prompted to use Organizational Account instead — use whichever matches your tenant's authentication setup.
Warning
Authentication credentials in Power Query are stored per-URL root. If you connect to https://contoso.sharepoint.com/sites/SalesTeam and later try to connect to https://contoso.sharepoint.com/sites/MarketingTeam, Power Query may prompt you again — or it may try to reuse the wrong credentials silently. You can manage credentials in Data → Queries & Connections → (right-click any query) → Data Source Settings. See Implementing Custom Privacy Levels and Data Source Credentials Management in Power Query for Secure Enterprise ETL Pipelines for the full picture on credential management.
The SharePoint.Files output is a table with columns including Name, Folder Path, Date modified, Date created, and a Content binary column. You filter it to find your specific file:
let
Source = SharePoint.Files(
"https://contoso.sharepoint.com/sites/SalesTeam",
[ApiVersion = 15]
),
FilteredFile = Table.SelectRows(
Source,
each [Name] = "Regional_Sales_Q3.xlsx"
and [Folder Path] = "https://contoso.sharepoint.com/sites/SalesTeam/Shared Documents/Sales Data/"
),
FileContent = FilteredFile{0}[Content],
WorkbookData = Excel.Workbook(FileContent, null, true)
in
WorkbookData
Let's unpack what's happening in the last two lines. FilteredFile{0}[Content] extracts the binary content from the first (and only) row of your filtered result. Excel.Workbook() takes that binary and parses it exactly the way you'd expect — returning a table of all the sheets, tables, and named ranges within the workbook. The third argument true tells Power Query to treat the first row as headers.
The result is a navigation table with columns like Name, Data, Item, Kind, and Hidden. The Kind column tells you whether each entry is a Sheet, Table, or DefinedName (named range). The Data column holds the actual table contents, nested as a table value.
From the workbook navigation table, you filter to your desired object:
let
Source = SharePoint.Files(
"https://contoso.sharepoint.com/sites/SalesTeam",
[ApiVersion = 15]
),
FilteredFile = Table.SelectRows(
Source,
each [Name] = "Regional_Sales_Q3.xlsx"
),
FileContent = FilteredFile{0}[Content],
WorkbookData = Excel.Workbook(FileContent, null, true),
SalesSheet = WorkbookData{[Item="SalesData", Kind="Table"]}[Data]
in
SalesSheet
If you're targeting a named Excel table (which you should almost always prefer over raw sheets — they're more stable and self-describing), use Kind="Table". For a worksheet, use Kind="Sheet". For a named range, use Kind="DefinedName".
Key insight
Always prefer connecting to a named Excel Table (Ctrl+T in Excel) rather than a raw sheet. Named tables don't break when rows are added or columns are reordered, and they have explicit headers. Raw sheets return everything including blank rows and merged cell weirdness. If you have control over the source workbooks, enforce the named table convention.
When you need to consolidate data from multiple workbooks that share the same structure — quarterly reports, regional submissions, department budgets — the SharePoint folder pattern is dramatically more efficient than manually connecting to each file.
You have two options here: SharePoint.Contents and SharePoint.Files.
SharePoint.Files returns a flat list of all files across all document libraries in the site — good when you want to search everywhere.SharePoint.Contents returns the site's top-level structure, which you then drill into. More surgical, but requires more navigation steps.For most practical scenarios, SharePoint.Files filtered by folder path is the most direct approach.
Here's the pattern for combining multiple Excel files from a SharePoint folder. Imagine you have a folder called Regional_Reports containing Northeast_Q3.xlsx, Southeast_Q3.xlsx, West_Q3.xlsx, and Midwest_Q3.xlsx — each with an identically structured sheet named SalesData.
let
Source = SharePoint.Files(
"https://contoso.sharepoint.com/sites/SalesTeam",
[ApiVersion = 15]
),
FilteredFolder = Table.SelectRows(
Source,
each [Folder Path] = "https://contoso.sharepoint.com/sites/SalesTeam/Shared Documents/Regional_Reports/"
and Text.EndsWith([Name], ".xlsx")
and [Name] <> "~$" -- Exclude Excel temp/lock files
),
RemoveLockFiles = Table.SelectRows(
FilteredFolder,
each not Text.StartsWith([Name], "~$")
),
AddWorkbook = Table.AddColumn(
RemoveLockFiles,
"Workbook",
each Excel.Workbook([Content], null, true),
type table
),
ExpandWorkbook = Table.ExpandTableColumn(
AddWorkbook,
"Workbook",
{"Name", "Data", "Kind"},
{"Sheet.Name", "Sheet.Data", "Sheet.Kind"}
),
FilterToSalesSheet = Table.SelectRows(
ExpandWorkbook,
each [Sheet.Name] = "SalesData" and [Sheet.Kind] = "Sheet"
),
ExpandData = Table.ExpandTableColumn(
FilterToSalesSheet,
"Sheet.Data",
{"Date", "Region", "Product", "Units", "Revenue"},
{"Date", "Region", "Product", "Units", "Revenue"}
),
AddFileName = Table.AddColumn(
ExpandData,
"Source File",
each [Name],
type text
),
SelectColumns = Table.SelectColumns(
AddFileName,
{"Source File", "Date", "Region", "Product", "Units", "Revenue"}
)
in
SelectColumns
This query does a lot of work. Let me walk through the key design decisions.
The ~$ filter is not optional. When someone has a workbook open in Excel, Windows creates a temporary lock file with that prefix. If Power Query tries to parse ~$Northeast_Q3.xlsx as an Excel workbook, it fails with a cryptic binary error. Always filter those out.
The AddWorkbook step calls Excel.Workbook() on each file's binary content, adding a nested table column. We then expand it to get the sheet metadata, filter to just the SalesData sheet, and expand the actual data. Notice we're explicitly naming the columns we want in the final ExpandTableColumn call — this makes the query resilient to extra columns being added to the source files.
Adding the Source File column before the final select is good defensive practice. When something goes wrong (and it will), knowing which file a problematic row came from saves significant debugging time.
Tip
If the structure of your source files might evolve — columns being added, renamed, or reordered — read Handling Dynamic Schema Changes in Power Query: Strategies for Evolving Source Data Structures before you finalize this pattern. The explicit column expansion shown above is the first line of defense, but there are more sophisticated strategies available.
For a deeper dive on the general folder combination pattern, Combining Multiple Files from a Folder covers the sample file approach and automatic schema detection that Power Query's built-in Combine Files dialog offers — which can save you writing much of this by hand when your files are well-structured.
Once you've got the data flowing, the transformation layer is standard Power Query work. But there are a few patterns worth highlighting specifically in the SharePoint context.
A common issue with SharePoint workbooks is that contributors don't always keep data in a clean table format starting at row 1. You might get a workbook where the actual headers are in row 3 and rows 1-2 are a title block.
let
// ... connection steps above ...
RawSheet = WorkbookData{[Item="Q3 Sales", Kind="Sheet"]}[Data],
PromoteHeaders = Table.PromoteHeaders(
Table.Skip(RawSheet, 2), -- Skip the title rows
[PromoteAllScalars = true]
),
CleanTypes = Table.TransformColumnTypes(
PromoteHeaders,
{
{"Date", type date},
{"Units", Int64.Type},
{"Revenue", Currency.Type}
}
)
in
CleanTypes
The Table.Skip(RawSheet, 2) discards the first two rows before promoting headers. Always pair PromoteHeaders with an explicit TransformColumnTypes call — Power Query infers types from raw sheets less reliably than from named tables.
SharePoint workbooks often accumulate years of history. If you only need the last 12 months, filter early to keep your query fast:
FilteredDates = Table.SelectRows(
CleanTypes,
each [Date] >= Date.AddMonths(Date.From(DateTime.LocalNow()), -12)
and [Date] <= Date.From(DateTime.LocalNow())
)
This dynamically calculates a rolling 12-month window on every refresh. The Date.From(DateTime.LocalNow()) pattern is important — comparing a date column against a datetime value will cause type mismatch errors, so we cast explicitly.
For more sophisticated date filtering and manipulation techniques, Working with Dates and Text in Power Query: Advanced Transformation Techniques covers the full range of M date functions in production-ready depth.
Workbooks maintained by humans rather than systems tend to have gaps — blank rows that people use as visual separators, partial entries, and cells left empty when a value "hasn't changed from last month."
RemoveBlankRows = Table.SelectRows(
FilteredDates,
each not List.IsEmpty(
List.RemoveMatchingItems(
Record.ToList(_),
{null, "", " "}
)
)
),
FillDownRegion = Table.FillDown(RemoveBlankRows, {"Region"})
The FillDown on Region handles the common pattern where someone enters the region name once and leaves the cells below blank, implicitly meaning "same region as above." Reshaping Data with Transpose, Fill Down, and Fill Up in Power Query goes deep on this family of operations.
OneDrive for Business is technically SharePoint — your personal OneDrive is a SharePoint site with the path pattern /personal/username_domain_com. This means the same SharePoint.Files connector works, with a different base URL:
let
Source = SharePoint.Files(
"https://contoso-my.sharepoint.com/personal/jsmith_contoso_com",
[ApiVersion = 15]
)
in
Source
Warning
Be careful about connecting to other people's OneDrive files unless they've explicitly shared them with you and you have the appropriate permissions. Even if you can manually navigate to the file in a browser, Power Query's service account (used during scheduled refresh in Power BI Service) may not have the same access. Files in team SharePoint sites are almost always more reliable for shared pipelines than individual OneDrive files.
There's another approach some practitioners use: the Web.Contents function with a direct OneDrive download URL. You can get this URL by opening the file in OneDrive, clicking Download, and capturing the resulting URL.
let
FileUrl = "https://contoso-my.sharepoint.com/personal/jsmith_contoso_com/_layouts/15/download.aspx?SourceUrl=/personal/jsmith_contoso_com/Documents/Budget_2024.xlsx",
FileContent = Web.Contents(FileUrl),
WorkbookData = Excel.Workbook(FileContent, null, true)
in
WorkbookData
This works for simple personal use, but it has two significant weaknesses. First, the download URL can change if the file is moved or renamed, breaking your query silently. Second, Web.Contents with dynamic URLs can cause privacy firewall issues and doesn't support scheduled refresh in Power BI Service without additional configuration. Stick to SharePoint.Files for anything you plan to run on a schedule.
This is where many SharePoint/Power Query setups break down in production. The query works perfectly on your machine during development, then fails when you schedule it in Power BI Service or when a colleague tries to refresh it on their machine.
If your SharePoint query combines data from two different sources — say, SharePoint files plus a SQL Server database — Power Query may refuse to combine them and throw a "Formula.Firewall" error. This is the M privacy firewall protecting against accidental data leakage across sources with different sensitivity levels.
The fix is to set appropriate privacy levels for your data sources. In Power BI Desktop, go to File → Options → Current File → Privacy and set "Ignore the Privacy Levels." In a production Power BI Service deployment, configure privacy levels explicitly in your gateway settings.
Warning
Setting "Ignore Privacy Levels" is a development convenience, not a production recommendation. In any pipeline handling sensitive data, configure explicit privacy levels so the firewall's protections remain active. This is covered in depth at Implementing Custom Privacy Levels and Data Source Credentials Management in Power Query for Secure Enterprise ETL Pipelines.
When you publish a report to Power BI Service and schedule refresh, the service needs credentials to access SharePoint. Navigate to the dataset's settings in Power BI Service, expand Data source credentials, and authenticate the SharePoint connection using OAuth2 with an organizational account that has persistent access to the SharePoint library.
If that account is a personal user account, the refresh will fail when that person leaves the organization. Use a service account or an account explicitly managed for automation.
If someone renames or moves the SharePoint folder you're connecting to, your query breaks. This is because the Folder Path filter in your query is hardcoded to the old path.
The best mitigation is to filter on folder path as a parameter rather than a hardcoded string. Parameterized Queries and Dynamic Data Sources in Power Query covers this pattern thoroughly — the short version is: define a Power Query parameter for your SharePoint site URL and folder path, then reference the parameter in your filter step. When the path changes, you update one parameter instead of hunting through query steps.
// Define a parameter: SharePointSiteUrl = "https://contoso.sharepoint.com/sites/SalesTeam"
// Define a parameter: ReportsFolder = "Shared Documents/Regional_Reports/"
let
SiteUrl = SharePointSiteUrl, // Reference to parameter
FolderPath = SiteUrl & "/" & ReportsFolder,
Source = SharePoint.Files(SiteUrl, [ApiVersion = 15]),
FilteredFolder = Table.SelectRows(
Source,
each Text.StartsWith([Folder Path], FolderPath)
and Text.EndsWith([Name], ".xlsx")
)
in
FilteredFolder
Using Text.StartsWith on the folder path rather than an exact match also makes the query resilient to subfolder structures — files in subdirectories of your target folder will still be included.
Let's build a complete, production-ready query that consolidates quarterly sales reports from a SharePoint document library.
Setup: Create a SharePoint document library folder called Q4_Reports. Upload three Excel files (North_Q4.xlsx, South_Q4.xlsx, East_Q4.xlsx), each containing a named Excel Table called SalesData with columns: Date (date), Salesperson (text), Product (text), Units (number), Revenue (number), Returns (number).
Step 1: Create a parameter for the site URL
In Power Query, go to Manage Parameters → New Parameter. Name it SPSiteUrl, type Text, default value = your SharePoint site URL. Create a second parameter ReportsFolderPath = the full path to your Q4_Reports folder.
Step 2: Build the base file list query
Create a new blank query called Q4_FileList:
let
Source = SharePoint.Files(SPSiteUrl, [ApiVersion = 15]),
FilterFolder = Table.SelectRows(
Source,
each Text.StartsWith([Folder Path], SPSiteUrl & "/" & ReportsFolderPath)
and Text.EndsWith([Name], ".xlsx")
and not Text.StartsWith([Name], "~$")
),
SelectColumns = Table.SelectColumns(
FilterFolder,
{"Name", "Date modified", "Content"}
)
in
SelectColumns
Step 3: Build the data extraction function
Create a new blank query called fn_ExtractSalesData:
let
fn_ExtractSalesData = (fileContent as binary, fileName as text) as table =>
let
Workbook = Excel.Workbook(fileContent, null, true),
SalesTable = Workbook{[Item="SalesData", Kind="Table"]}[Data],
AddSource = Table.AddColumn(SalesTable, "Source File", each fileName, type text),
TypedColumns = Table.TransformColumnTypes(AddSource, {
{"Date", type date},
{"Salesperson", type text},
{"Product", type text},
{"Units", Int64.Type},
{"Revenue", Currency.Type},
{"Returns", Currency.Type}
})
in
TypedColumns
in
fn_ExtractSalesData
Step 4: Apply the function and combine
Create the final query called Q4_SalesConsolidated:
let
FileList = Q4_FileList,
AddData = Table.AddColumn(
FileList,
"ExtractedData",
each fn_ExtractSalesData([Content], [Name]),
type table
),
CombineData = Table.Combine(AddData[ExtractedData]),
FilterCurrentQ = Table.SelectRows(
CombineData,
each [Date] >= #date(2024, 10, 1)
and [Date] <= #date(2024, 12, 31)
),
RemoveNulls = Table.SelectRows(
FilterCurrentQ,
each [Revenue] <> null and [Salesperson] <> null
),
NetRevenue = Table.AddColumn(
RemoveNulls,
"Net Revenue",
each [Revenue] - [Returns],
Currency.Type
)
in
NetRevenue
The function-based extraction approach (Steps 3-4) is cleaner and more maintainable than embedding the Excel.Workbook logic directly in the main query. When something changes in the extraction logic, you update the function once and it propagates everywhere. For more on this architectural pattern, see Building Reusable Power Query Function Libraries: Parameters, Recursion, and Modular M Code Patterns.
Step 5: Verify and load
Refresh Q4_SalesConsolidated and confirm you see rows from all three source files in the Source File column. Load to a table or data model. Add a new regional file to the SharePoint folder, refresh again, and confirm it's automatically included.
"We couldn't authenticate with the credentials provided"
This almost always means you authenticated with one Microsoft account but the SharePoint site belongs to a different tenant. Check Data Source Settings, clear the existing credentials, and re-authenticate. If you have multiple Microsoft 365 accounts (personal + work, for example), make sure you're picking the right one.
"Expression.Error: The key didn't match any rows in the table"
This error from WorkbookData{[Item="SalesData", Kind="Table"]} means no object named "SalesData" with Kind "Table" exists in the workbook. Either the table was renamed, the sheet name changed, or someone accidentally converted the table back to a range. Navigate to the WorkbookData step in the Applied Steps pane to inspect what's actually in the workbook, and update your filter accordingly. Making this lookup dynamic — trying a table first, falling back to a sheet — is a defensive pattern worth implementing for contributor-maintained workbooks.
Rows from some files are missing after combine
Add an error-handling step around your fn_ExtractSalesData function call. Without it, one malformed file will cause the entire query to fail silently or throw an error that masks the successful rows. Wrap the function call in try ... otherwise:
AddData = Table.AddColumn(
FileList,
"ExtractedData",
each try fn_ExtractSalesData([Content], [Name])
otherwise null
),
RemoveErrors = Table.SelectRows(
AddData,
each [ExtractedData] <> null
)
This way, one bad file doesn't blow up the entire pipeline. You'll want to log which files failed — Debugging and Error Handling in M: Building Robust try-otherwise Logic and Diagnostic Workflows in Power Query covers exactly that.
Refresh works in desktop but fails in Power BI Service
This is almost always an authentication or gateway issue. For SharePoint Online, you typically don't need an on-premises gateway — Power BI Service can connect to SharePoint directly using OAuth. But you do need to configure the data source credentials in the Service under the dataset settings. If you're using SharePoint on-premises (not SharePoint Online), you will need a gateway.
Performance is slow with many files
Each Excel.Workbook() call makes a network request and parses a file. With 50+ files in a folder, this becomes slow. Consider: (1) archiving older files to a separate folder and only pointing your query at the active folder, (2) adding a date filter on the Date modified column in your FilteredFolder step to skip files that haven't changed since your last refresh, or (3) restructuring the source data so contributors update a single shared workbook rather than separate files.
You've now got a complete toolkit for working with SharePoint-hosted Excel files in Power Query. The key principles to carry forward:
SharePoint.Files with the site base URL, not file sharing links~$ lock files before attempting Excel.Workbook parsingThe natural next step from here is thinking about what to do when the data you're pulling isn't perfectly consistent across files. Cleaning Messy Data with Power Query: Nulls, Errors, and Type Conversions is the right companion lesson for hardening the transformation layer once your connections are solid.
If your SharePoint workbooks contain data that's already largely clean and you're ready to focus on combining and relating them to other sources, Combining Data from Multiple Sources with Append and Merge Queries in Power Query covers how to relate your consolidated SharePoint data to SQL Server tables, API responses, and other sources in a single data model.
Power Query Essentials