SharePoint is one of the most common data stores in the Microsoft ecosystem — and one of the trickiest to connect to cleanly. This lesson walks you through authenticating, constructing the right site URL, and extracting usable data from both SharePoint Lists and Document Libraries in Power Query.
Picture this: your team tracks project status in a SharePoint list. Someone updates it every day — adding new rows, changing statuses, uploading documents to an associated document library. Your job is to turn that living, breathing SharePoint data into a clean, reliable report that refreshes automatically. No manual exports. No copy-pasting into Excel. Just a connection that works, every time.
That scenario is exactly why the SharePoint connectors in Power Query exist, and why they're genuinely worth learning well. SharePoint has quietly become one of the most common data stores in the Microsoft ecosystem. Lists hold everything from project trackers and help desk tickets to inventory records and HR data. Document libraries hold files you might want to combine and process. Once you know how to connect Power Query to SharePoint correctly, you unlock a whole category of real-world data sources that would otherwise require manual effort to extract.
By the end of this lesson, you'll be able to connect Power Query to both SharePoint Lists and SharePoint Document Libraries, authenticate properly, navigate the quirks of the data structures you'll encounter, and extract clean, usable data from each source type. You'll also understand the common failure modes — broken credentials, wrong site URLs, nested metadata columns — so you can troubleshoot confidently when things don't work first time.
What you'll learn:
This lesson assumes you have a working installation of Power BI Desktop or Excel with Power Query. You should be comfortable with the basics of the Power Query Editor interface — navigating the Applied Steps pane, recognizing what a table looks like in the preview window, and understanding the general concept of a data source connection. If you're brand new to Power Query, spend some time with Power Query 101: Connect, Transform, Load before continuing here.
You'll also need access to a SharePoint site — either a personal Microsoft 365 account (OneDrive for Business includes SharePoint under the hood) or a corporate tenant where you have read access to at least one list or library.
Before touching Power Query, it helps to understand what SharePoint actually gives you, because the two connector types reflect two genuinely different things.
A SharePoint List is SharePoint's version of a structured table. It has columns (fields) and rows (items). You might use it to track sales leads, IT tickets, employee onboarding checklists, or vendor contacts. Each row is a "list item," and each list item can have many metadata columns attached to it. Crucially, SharePoint adds a large number of system columns to every list — things like modification timestamps, author information, workflow data, and internal IDs — that you didn't ask for and usually don't want in your report.
A SharePoint Document Library is a file storage area. Think of it like a folder on a file server, but hosted in SharePoint. Each "item" in a library is a file — an Excel workbook, a PDF, a CSV — and the library tracks metadata about each file (who uploaded it, when it was modified, what folder it lives in). When Power Query connects to a Document Library, it gives you a table where each row represents a file and one column contains the binary content of that file. This is the starting point for scenarios like combining multiple files from a folder — except your "folder" is SharePoint instead of a local directory.
The connectors you'll use are different for each:
This is where a surprising number of people get stuck. Both connectors ask you for a "Site URL," and it is not the full URL of your list or library. It's the URL of the site itself.
Here's the distinction. Suppose you're working with a list called "Project Tracker" and its URL in the browser looks like this:
https://yourcompany.sharepoint.com/sites/OperationsTeam/Lists/ProjectTracker/AllItems.aspx
The site URL — the one you give to Power Query — is only this part:
https://yourcompany.sharepoint.com/sites/OperationsTeam
Everything after /sites/OperationsTeam is the path within the site to the specific list or view. Power Query connects at the site level and then lets you select which list or library to load from inside the connector dialog.
Similarly, if you're working from OneDrive for Business, the site URL might look like:
https://yourcompany-my.sharepoint.com/personal/yourname_yourcompany_com
Warning: A very common mistake is pasting the full browser URL — including the list path, view name, and query parameters — into the connector's site URL field. This will either fail immediately or produce confusing errors. Always strip back to the site root before you paste.
If you're unsure what your site URL is, navigate to the site's homepage in your browser and copy just the address up to and including the site name segment.
In Power BI Desktop, go to the Home ribbon and click Get Data. In the search box, type "SharePoint" and you'll see two relevant options: "SharePoint Online List" and "SharePoint Folder." Choose SharePoint Online List.
In Excel with Power Query, go to the Data tab on the ribbon, click Get Data, hover over From Online Services, and select From SharePoint Online List.
A dialog box appears asking for your site URL. Paste in your site-level URL (not the list URL) and click OK.
The first time you connect to any SharePoint site, Power Query will prompt you to authenticate. You'll see a dialog with options on the left — typically "Anonymous," "Windows," "Basic," and "Microsoft Account."
For SharePoint Online (Microsoft 365), always choose Microsoft Account. Click Sign in and complete the sign-in flow in the browser window that appears. Once you're signed in, you'll see your account name displayed, and you can click Connect.
Note: "Windows" authentication is for on-premises SharePoint servers inside a corporate network, not SharePoint Online. If you're connecting to a
sharepoint.comURL, use Microsoft Account. If you accidentally choose the wrong authentication method and get errors, you can clear your saved credentials by going to File → Options and Settings → Data Source Settings, finding your SharePoint URL, and clicking Clear Permissions.
After authentication, Power Query will display the Navigator window — a panel showing everything it can find on that SharePoint site. You'll see a list of all the SharePoint lists and libraries available to you. Don't be surprised if there are many items here; SharePoint sites often have dozens of internal system lists that power site features behind the scenes.
Look for the name of the list you want to load. You can click on it to preview the data in the right-hand panel. When you're satisfied you've found the right one, click Transform Data (not Load) to open it in the Power Query Editor. You almost always want to transform before loading, because the raw SharePoint List output is messy.
When your SharePoint list first opens in the Power Query Editor, prepare yourself. You'll see your columns — but you'll also see dozens of system columns you didn't create. SharePoint adds columns like:
ID — the internal integer ID of each list itemModified — when the item was last changedCreated — when it was createdAuthor — a Record value containing the creator's detailsEditor — another Record, for the last editor_UIVersionString — internal versioning infoOData_ or ending in IdSome of these are genuinely useful (like Modified and Created if you're doing time-series analysis). Most are noise. This is a perfect moment to apply the discipline from renaming, reordering, and removing columns — go through the columns systematically, remove what you don't need, rename what you keep, and reorder to match your intended output structure.
Person columns and Lookup columns deserve special attention because they come through as Record values rather than simple text. You'll see the word "Record" in those cells instead of a name or a value.
For a Person column like "Assigned To," SharePoint returns a Record containing the person's display name, email, login name, and other profile data. To extract just the display name, you can expand the record. Click the expand icon (two opposing arrows) in the column header. A dialog will appear listing the fields inside the record — choose "Title" or "Name" for the display name, and "EMail" if you need the email address. Uncheck everything else to keep the output tidy.
Lookup columns behave similarly. They return a Record with the referenced item's ID and value. Expand them the same way, choosing only the field you actually want.
Tip: When expanding a Record column, Power Query will offer to prefix the new column names with the original column name (e.g., "AssignedTo.Title"). This is helpful if you're expanding multiple record columns, as it prevents name collisions. If you're only expanding one field, you can rename it to something cleaner immediately after.
The M code behind an expansion looks like this in the formula bar:
= Table.ExpandRecordColumn(
#"Previous Step",
"AssignedTo",
{"Title", "EMail"},
{"AssignedTo.Name", "AssignedTo.Email"}
)
You can read and edit this directly if you're comfortable with the M formula language.
SharePoint dates come through as date-time values, which is usually what you want, but text columns that contain numbers may come through as text. After removing unwanted columns and expanding records, do a final pass to set data types explicitly on each column. Right-click a column header and choose "Change Type," or use the data type dropdown in the Transform ribbon.
This is especially important if you're going to use these dates for filtering or calculations. You can learn more about handling dates robustly in working with dates and text in Power Query.
When your goal isn't to read list item data but to process files stored in SharePoint — Excel workbooks, CSVs, PDFs — you use a different connector: SharePoint Folder.
In Power BI Desktop, go to Get Data, search for "SharePoint," and this time choose SharePoint Folder. In Excel, it's under Get Data → From File → From SharePoint Folder.
Enter your site URL the same way as before — just the site-level URL, not the library path.
If you've already authenticated to this site, Power Query will reuse your credentials and connect immediately. If not, you'll see the same Microsoft Account authentication dialog. Sign in and connect.
After connecting, the Navigator (or a preview table) shows you a flat list of every file in every library on that SharePoint site. Each row represents one file, and the columns include:
Name — the filenameExtension — the file extension (.xlsx, .csv, etc.)Date accessed, Date modified, Date created — timestampsAttributes — a Record with additional metadataFolder Path — the full path including the library name and any subfoldersContent — a binary column representing the file's actual contentThis is a lot of files if your site has multiple libraries. The Folder Path column is your key to filtering down to just the library you care about.
Click the dropdown arrow on the Folder Path column and filter to rows containing the name of your library. For example, if you want only files from a library called "Monthly Reports," filter where Folder Path contains "Monthly Reports."
Tip: The
Folder Pathvalue will typically look likehttps://yourcompany.sharepoint.com/sites/OperationsTeam/Monthly Reports/. Use a "contains" filter with just the library name portion to keep things readable and stable even if the site URL changes in the future.
After filtering, you should have only the files from your target library. If you want a specific file format, filter on Extension as well.
At this point, you're looking at metadata about the files. To actually read the files, you use the Content column. The approach depends on your scenario:
If you want to combine all files (e.g., all Excel workbooks in the library): This is exactly the scenario covered in depth by combining multiple files from a folder. The binary content in the Content column can be passed through Power Query's file combining feature. Click the "Combine Files" button (the double-arrow icon) in the Content column header, and Power Query will guide you through extracting data from each file and stacking the results.
If you want a specific single file: Filter the table down to the exact row for that file using the Name column, then navigate to the Content value and use the appropriate function to read it — Excel.Workbook(), Csv.Document(), etc.
For a single CSV file, the M code would look something like:
let
Source = SharePoint.Files(
"https://yourcompany.sharepoint.com/sites/OperationsTeam",
[ApiVersion = 15]
),
FilteredToLibrary = Table.SelectRows(
Source,
each Text.Contains([Folder Path], "Monthly Reports")
),
FilteredToFile = Table.SelectRows(
FilteredToLibrary,
each [Name] = "sales_data_october.csv"
),
FileContent = FilteredToFile{0}[Content],
ParsedCSV = Csv.Document(
FileContent,
[Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.None]
),
PromotedHeaders = Table.PromoteHeaders(ParsedCSV, [PromoteAllScalars = true])
in
PromotedHeaders
Key insight: The
SharePoint.Files()function (used by the SharePoint Folder connector) and theSharePoint.Contents()orSharePoint.Tables()functions (used by the SharePoint Online List connector) are the underlying M functions powering these connections. You'll see them at the top of your query's M code. Understanding this means you can modify the connection programmatically — for example, making the site URL a parameter so you can switch between environments easily. See parameterized queries and dynamic data sources in Power Query for how to do that cleanly.
Credentials are one of the more frustrating parts of SharePoint connections, especially in team environments. Here's what you need to know.
Power Query stores credentials keyed to the URL you entered. If you entered https://yourcompany.sharepoint.com/sites/OperationsTeam, that credential applies to all queries using that exact URL. If someone else builds a query using a slightly different URL — maybe they included a trailing slash or used a different site — they'll need to authenticate separately.
If you build a Power BI report on your laptop and share the .pbix file with a colleague, your credentials do not go with it. Your colleague will need to authenticate to the SharePoint site with their own Microsoft Account when they open the file.
If you publish a report to the Power BI Service and want scheduled refresh to work, you need to configure credentials in the dataset settings after publishing. Go to the dataset's settings in Power BI Service, navigate to Data Source Credentials, and sign in with an account that has access to the SharePoint site. The account you use here is what the scheduled refresh will use — so make sure it's a service account or a shared account that won't lose access if someone leaves the organization.
Warning: If you use your personal Microsoft Account to configure scheduled refresh credentials in Power BI Service and then leave the organization or change your password, all refreshes using that credential will fail. Use a dedicated service account for production scheduled refreshes whenever possible.
Let's put this together with a practical exercise you can work through on your own SharePoint site.
Scenario: You maintain a SharePoint list called "IT Help Desk Tickets" with columns for Ticket ID (number), Issue Description (text), Status (choice: Open, In Progress, Resolved), Priority (choice: Low, Medium, High), Assigned To (person), and Date Submitted (date/time).
Your task is to connect Power Query to this list and produce a clean table containing only: Ticket ID, Status, Priority, the assigned person's display name, and Date Submitted.
Steps:
Open Power BI Desktop. Click Get Data → SharePoint Online List. Enter your site URL (just the site root, not the list URL). Click OK.
Authenticate using Microsoft Account. Sign in with your Microsoft 365 credentials.
In the Navigator, find your "IT Help Desk Tickets" list (or whichever list you have access to). Click it and review the preview. Then click Transform Data.
In the Power Query Editor, examine all columns. You'll see your five data columns plus many system columns. Go to the Home ribbon and click Choose Columns. Check the boxes for ID (this is your Ticket ID), Title (if that's where Ticket ID lives in your list), Status, Priority, AssignedTo, and Created (for Date Submitted). Click OK to remove all others.
Click the expand icon on the AssignedTo column. Select only "Title" (the display name). Uncheck "Use original column name as prefix" and click OK. Rename the resulting column from "Title" to "Assigned To."
Rename the Created column to "Date Submitted."
Set data types: ID as Whole Number, Status and Priority as Text, Assigned To as Text, Date Submitted as Date/Time.
Click Close & Apply.
You now have a clean, refreshable table loaded from your SharePoint list.
"We couldn't connect to this URL" error on the connector dialog. Almost always caused by a wrong site URL. Double-check that you've used only the site root, not a list or library path. Also confirm the site URL works when you paste it into a browser.
Seeing "Expression.Error: The key didn't match any rows in the table." This often appears when you reference a specific list by name in M code but the list name has changed, or when you're trying to navigate into a file that no longer exists. Check the Navigator to see what's actually available.
Authentication keeps prompting even after signing in. Try clearing the cached credentials. In Power BI Desktop: File → Options and Settings → Data Source Settings. Find the SharePoint URL entry and click Clear Permissions. Then reconnect and re-authenticate from scratch.
The AssignedTo or similar column shows "Record" everywhere but expanding it gives nothing useful.
This can happen when the Person column allows multiple people. In that case, Power Query may return a List of Records rather than a single Record. You'll need to expand the List first (using the list expand icon), then expand the Records. This produces one row per person per ticket — you may need to aggregate or handle this based on your reporting requirements.
Scheduled refresh fails in Power BI Service with an authentication error. The credentials configured for the dataset have expired or the account doesn't have access. Go to the dataset settings in Power BI Service, find the data source under Data Source Credentials, and re-enter valid credentials. Also review scheduling and managing Power Query refresh failures in Power BI Service for a comprehensive guide to diagnosing refresh issues.
Performance is slow when loading large lists. SharePoint List queries do not support query folding — Power Query pulls the entire list first, then applies your filters locally. For very large lists (tens of thousands of rows), consider whether filtering at the SharePoint side (using views) before connecting is feasible. You can also learn more about why folding matters in Power Query performance: master folding, buffering, and optimization techniques.
You've covered a lot of ground. You now know:
SharePoint is rarely the last stop in a data workflow. Once your data is in Power Query, you'll usually need to join it with other tables — a lookup list from SQL Server, or a reference table from another SharePoint list. Combining data from multiple sources with append and merge queries covers exactly how to do that cleanly.
If you're working in an environment where the SharePoint list schema changes occasionally — new columns appear, column names change — you'll want to build some resilience into your query. Handling dynamic schema changes in Power Query has practical strategies for that exact scenario.
And if you're building this into a broader reporting system with multiple data sources and transformation layers, think about formalizing your architecture using the principles in building multi-stage staging architectures in Power Query — it will make your SharePoint queries just one clean, maintainable layer in a well-organized pipeline.