SharePoint for Excel & Power BI: Best Practices, Permissions, and Real-World Hacks
- Admin
- Dec 14, 2025
- 4 min read
Updated: Feb 24
SharePoint can be either your best “single source of truth”… or the reason your refreshes fail on Monday morning.
This guide shows how to structure SharePoint properly (sites, libraries, lists), set permissions that scale, and connect everything cleanly to Excel and Power BI—with practical “do this / don’t do that” patterns.
PRACTICE MATERIAL BELOW!👇
1) SharePoint basics that matter for BI people
Sites, Libraries, Lists (the “3 boxes” model)
Site = the container (security boundary + navigation)
Document Library = files (Excel, CSV, Parquet, PDFs, PBIX, templates)
List = structured rows + metadata (like a lightweight database)
The golden rule
Use Lists for metadata and workflow, Libraries for the actual files.Example: a list stores “Dataset Name, Owner, Refresh Frequency, Source System, Sensitivity” and the library stores the PBIX / Excel / export files.
2) A SharePoint structure that won’t break your refresh
A simple pattern that scales
Site: AnalyticsHubLibraries:
Data (raw extracts, curated files)
Reports (PBIX, documentation, release notes)
Templates (Excel templates, standards)
Archive (read-only, locked)
Folders inside Data:
01_Raw
02_Curated
03_Output
99_Archive
Naming conventions (boring = powerful)
Use a predictable naming standard, e.g.:
Sales_Extract_YYYY-MM-DD.csv
GLLines_DACH_YYYY-MM.xlsx
Rates_ECB_Daily.parquet
Why this is a hack: Power Query can filter by name patterns reliably, and you can always pick “latest file” without manual clicks.
3) Permissions: the clean setup (and the trap to avoid)
The trap: “unique permissions everywhere”
Breaking inheritance on hundreds/thousands of folders/files becomes painful to maintain and can hurt performance. Microsoft’s guidance includes a hard ceiling and a practical recommendation for uniquely secured items per library. Microsoft Learn+1
Best practice permission model
Give access via SharePoint Groups / Microsoft 365 Groups, not individuals
Keep permissions inherited as much as possible
If you must separate access, prefer:
separate libraries, or even separate sites
rather than thousands of individually secured folders/files
Practical “BI-ready” roles
AnalyticsHub Owners (admins)
AnalyticsHub Contributors (upload/edit)
AnalyticsHub Readers (view only)
Optional: Data Stewards (curation + approvals)
💡 Pro Tip (governance hack): Create a SharePoint List called Data Catalog with columns like Owner, Data Source, Refresh SLA, Sensitivity, and Link to Folder. That becomes your living documentation.
4) Connecting SharePoint to Excel (the reliable way)
Option A: Excel + Power Query “From SharePoint Folder”
Use this when you want Excel to pull files (CSV/XLSX) from a library.
Steps
Excel → Data → Get Data → From Online Services → From SharePoint Folder
Enter the site URL (not the folder URL)
Filter files by Folder Path / Name
Combine or load as a table
Mini Excel trick: If users paste SharePoint links with long tracking parameters, clean them first:
=TEXTBEFORE(A2;"?")Option B: Excel files stored in SharePoint = collaboration + versioning
Co-authoring and version history are the “quiet superpower” for team-owned Excel templates.
5) Connecting SharePoint to Power BI: pick the right connector
Quick decision table
You have… | Use… | Why |
Many files in a library (CSV/XLSX) | SharePoint Folder | Great for file-based ingestion and combining |
Structured rows (tasks, mapping, catalog) | SharePoint Online List | Treat lists like a mini database |
PBIX collaboration | Power BI Desktop + OneDrive/SharePoint integration | Better versioning + sharing workflows |
6) Power Query M code patterns you’ll actually reuse (no HTML)
Pattern 1: SharePoint Folder → filter → take latest file
Use this when your files follow a naming convention like Sales_Extract_YYYY-MM-DD.csv.
let
SiteUrl = "https://contoso.sharepoint.com/sites/AnalyticsHub",
Source = SharePoint.Files(SiteUrl, [ApiVersion = 15]),
// Keep only a specific library/folder + file type
Filtered = Table.SelectRows(
Source,
each Text.Contains([Folder Path], "/Data/02_Curated/Sales/")
and [Extension] = ".csv"
and Text.StartsWith([Name], "Sales_Extract_")
),
// Pick the newest file by modified date
Sorted = Table.Sort(Filtered, {{"Date modified", Order.Descending}}),
Latest = Table.FirstN(Sorted, 1),
// Read the file content
Bin = Latest{0}[Content],
Csv = Csv.Document(Bin, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
Promoted = Table.PromoteHeaders(Csv, [PromoteAllScalars=true])
in
Promoted💡 Hack: Your “latest file” logic is only as good as your naming + folder discipline. Don’t fight SharePoint—standardize it.
Pattern 2: SharePoint List connector (and an important limitation)
Power Query has dedicated connectors for SharePoint lists. If you use the OData feed approach, be aware of URL-length constraints. Microsoft Learn+1
Recommended workflow
Connect via Get Data → SharePoint Online List (UI)
Then open Advanced Editor and parameterize the Site URL.
Example skeleton (IDs depend on your tenant/list, so copy from your own query first):
let
SiteUrl = "https://contoso.sharepoint.com/sites/AnalyticsHub",
Source = SharePoint.Tables(SiteUrl, [ApiVersion = 15]),
MyList = Source{[Name="Data Catalog"]}[Items],
Clean = Table.TransformColumnTypes(MyList, {{"ID", Int64.Type}})
in
Clean7) “Hacks” that make SharePoint + Power BI smoother
Hack 1: Use Views + Metadata (instead of deep folders)
Put “Year”, “Region”, “Confidentiality”, “Source System” as columns
Create Views to mimic folders (“2025 DACH”, “Finance Only”, etc.)This keeps data discoverable and avoids folder chaos.
Hack 2: Avoid “moving target” file paths
Don’t refresh from someone’s personal OneDrive path. Use a team site library with stable paths.
Hack 3: Keep refresh predictable
Store “refresh-ready” files in a dedicated folder like 02_Curated
Only publish files there after validation (manual or automated)
Hack 4: Permissions by design, not by exception
If a dataset is sensitive, put it in a separate library/site. Keep unique permissions minimal.
8) Common issues (and quick fixes)
“Access denied” in Power BI Service: confirm you used the site URL, and credentials are set correctly in the Service.
You can’t find the folder easily: in SharePoint Folder connector, filter by Folder Path and use consistent naming. Microsoft Learn
List is “too big / slow”: consider indexing columns, using views, or moving heavy analytics data to a proper database (SharePoint lists are great—until they aren’t).
How to use: upload the CSVs into a SharePoint library folder like Data/01_Raw/, then test the “latest file” M pattern in Excel Power Query or Power BI.
Do you need a starter Power Query Toolkit with some how-to guide, best functions and how to appy them in Excel and Power BI reports? Just with a slight adjustment to the quick and efficient flows. Check it out.
Comments