Wicked Smart Data
LearnInsightsAboutContact
Sign InLet's Build
LearnInsightsAboutContact
Sign InLet's Build
Wicked Smart Data

Intelligence, automation, and expert execution — plus an elite library of free knowledge. We turn complexity into competitive advantage.

Start a conversation

Platform

  • Learning Paths
  • Insights
  • RSS Feed

Company

  • About
  • Contact
  • Work With Us

Legal

  • Privacy Policy
  • Terms of Service

© 2026 Wicked Smart Data. All rights reserved.

Intelligence · Automation · Advantage

All Insights
Power Query

Connecting to and Querying SharePoint Excel Files in Power Query: Accessing, Filtering, and Refreshing Workbooks Stored in SharePoint and OneDrive

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.

⚡ Practitioner21 min readNov 21, 2026Updated Nov 21, 2026
Connecting to and Querying SharePoint Excel Files in Power Query: Accessing, Filtering, and Refreshing Workbooks Stored in SharePoint and OneDrive
On this page
  • Introduction
  • Prerequisites
  • Understanding the Two Connection Patterns
  • Connecting to a Single SharePoint Excel File
  • Finding the Right URL
  • Using the SharePoint.Files Connector
  • Filtering Down to Your Target File
  • Accessing a Specific Sheet or Table
  • Using the SharePoint Folder Connector for Multiple Files
  • The SharePoint.Contents vs SharePoint.Files Distinction
  • Building the Folder-Based Combination Query
  • Filtering and Transforming the Extracted Data
  • Promoting Headers When Sheets Are Messy
  • Filtering to Relevant Date Ranges
  • Handling Null Values in Contributor-Maintained Workbooks
  • OneDrive for Business: What's Different
  • The Web.Contents Alternative for OneDrive
  • Making Refresh Work: Common Pitfalls
  • The Privacy Firewall
  • Credentials in Power BI Service
  • The "DataSource.NotFound" Error After File Moves
  • Hands-On Exercise
  • Common Mistakes & Troubleshooting
  • Summary & Next Steps

Connecting to and Querying SharePoint Excel Files in Power Query: Accessing, Filtering, and Refreshing Workbooks Stored in SharePoint and OneDrive

Introduction

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:

  • The difference between SharePoint folder connectors and direct file URL connections, and when to use each
  • How to construct robust, refresh-safe connection strings for SharePoint and OneDrive
  • How to access specific sheets, Excel tables, and named ranges within connected workbooks
  • How to filter and transform the extracted data using standard Power Query techniques
  • How to combine multiple Excel workbooks from a SharePoint folder into a single query
  • How to diagnose and fix the most common authentication and refresh failures

Prerequisites

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:

  • A Microsoft 365 account with access to a SharePoint site or OneDrive for Business
  • Excel 2016+ or Power BI Desktop
  • At least one Excel workbook stored in SharePoint or OneDrive

Understanding the Two Connection Patterns

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.


Connecting to a Single SharePoint Excel File

Finding the Right URL

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).

Using the SharePoint.Files Connector

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.

Filtering Down to Your Target File

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.

Accessing a Specific Sheet or Table

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.


Using the SharePoint Folder Connector for Multiple Files

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.

The SharePoint.Contents vs SharePoint.Files Distinction

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.

Building the Folder-Based Combination Query

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.


Filtering and Transforming the Extracted Data

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.

Promoting Headers When Sheets Are Messy

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.

Filtering to Relevant Date Ranges

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.

Handling Null Values in Contributor-Maintained Workbooks

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: What's Different

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.

The Web.Contents Alternative for OneDrive

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.


Making Refresh Work: Common Pitfalls

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.

The Privacy Firewall

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.

Credentials in Power BI Service

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.

The "DataSource.NotFound" Error After File Moves

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.


Hands-On Exercise

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.


Common Mistakes & Troubleshooting

"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.


Summary & Next Steps

You've now got a complete toolkit for working with SharePoint-hosted Excel files in Power Query. The key principles to carry forward:

  • Use SharePoint.Files with the site base URL, not file sharing links
  • Parameterize site URLs and folder paths so your queries survive file moves
  • Always filter out ~$ lock files before attempting Excel.Workbook parsing
  • Prefer named Excel Tables over raw sheets for stable, header-reliable connections
  • Wrap file extraction logic in functions to keep your main query clean
  • Handle errors at the file level so one bad workbook doesn't kill the pipeline
  • Configure credentials in Power BI Service explicitly with a persistent service account

The 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.

Work With Us

From insight to implementation

Reading is the start. When you're ready to build the data, automation, or AI systems behind it, our team turns strategy into shipped results.

Let's Build

Power Query Essentials

Previous

Promoting Queries to the Data Model vs. Connection Only in Power Query: Controlling What Loads and Why

Related Insights

Power QueryFoundation

Understanding Value Types and Primitive Operations in M: Numbers, Text, Logical, and Null Foundations

16 min
Power QueryFoundation

Promoting Queries to the Data Model vs. Connection Only in Power Query: Controlling What Loads and Why

16 min
Power QueryExpert

Implementing Custom Connection Retry and Backoff Logic in Power Query M: Building Fault-Tolerant Data Source Connectors for Unreliable Networks and Throttled APIs

28 min

On this page

  • Introduction
  • Prerequisites
  • Understanding the Two Connection Patterns
  • Connecting to a Single SharePoint Excel File
  • Finding the Right URL
  • Using the SharePoint.Files Connector
  • Filtering Down to Your Target File
  • Accessing a Specific Sheet or Table
  • Using the SharePoint Folder Connector for Multiple Files
  • The SharePoint.Contents vs SharePoint.Files Distinction
  • Building the Folder-Based Combination Query
  • Filtering and Transforming the Extracted Data
  • Promoting Headers When Sheets Are Messy
  • Filtering to Relevant Date Ranges
  • Handling Null Values in Contributor-Maintained Workbooks
  • OneDrive for Business: What's Different
  • The Web.Contents Alternative for OneDrive
  • Making Refresh Work: Common Pitfalls
  • The Privacy Firewall
  • Credentials in Power BI Service
  • The "DataSource.NotFound" Error After File Moves
  • Hands-On Exercise
  • Common Mistakes & Troubleshooting
  • Summary & Next Steps