TECH **Power BI Data Source Connections: Zero-Fluff, Hands-On Guide**

Power BI Data Source Connections: Zero-Fluff, Hands-On Guide

(Excel, SQL, Web, SharePoint – Production-Ready & Exam-Proof)

1. What This Is & Why It Matters

You’re building a Power BI report for your sales team. The data lives in four places: - Excel files (daily sales dumps from a legacy system).
- SQL Server (ERP database with customer orders).
- A public API (weather data to correlate with sales).
- SharePoint (team documents with manual adjustments).

If you don’t connect to these sources correctly, your report will: ❌ Break when the Excel file moves.
❌ Time out on large SQL queries.
❌ Fail to refresh when the API rate-limits you.
❌ Show stale data because SharePoint permissions are misconfigured.

This guide gives you the superpower to:
✅ Pull data reliably (no more "file not found" errors).
✅ Optimize performance (avoid 10-minute refreshes on 100K rows).
✅ Secure credentials (no hardcoded passwords in PBIX files).
✅ Automate refreshes (so your boss sees yesterday’s data, not last month’s).

Real-world scenario:
You inherit a Power BI report that "works" but: - The Excel file path is hardcoded to C:\Users\Bob\Desktop\Sales.xlsx.
- The SQL query runs in 20 seconds on your laptop but times out in the Power BI Service.
- The SharePoint connection uses a personal account that’s about to be deactivated.

Your mission: Fix it before the next board meeting.

2. Core Concepts & Components

? Data Source Types

? Key Power BI Components

3. Step-by-Step Hands-On: Connecting to Each Source

Prerequisites

• Power BI Desktop (latest version).
• Access to:
• An Excel file (e.g., Sales.xlsx).
• A SQL Server database (or Azure SQL Database free tier).
• A public API (e.g., JSONPlaceholder).
• A SharePoint site (or SharePoint trial).

? Task 1: Connect to Excel (Local & SharePoint)

Local Excel File

1. Open Power BI Desktop → Home → Get Data → Excel.
2. Browse to your file (e.g., C:\Data\Sales.xlsx) → Open.
3. Select the table/sheet → Transform Data (opens Power Query).
4. Rename the query (e.g., Sales_Excel) → Close & Apply.
5. Why? Avoid generic names like "Query1" – future-you will hate past-you.

SharePoint Excel File

1. Get the SharePoint file URL:
2. Navigate to the file in SharePoint → Copy link (use "People with existing access").
3. Example: https://yourcompany.sharepoint.com/sites/Sales/Shared%20Documents/Sales.xlsx.
4. In Power BI:
5. Get Data → SharePoint Folder → Paste the URL → OK.
6. Select the file → Transform Data.
7. Fix the path:
8. In Power Query, go to Advanced Editor and replace the hardcoded path with a parameter:
   powerquery-m
let
Source = SharePoint.Files("https://yourcompany.sharepoint.com/sites/Sales", [ApiVersion = 15]),
File = Source{[Name="Sales.xlsx"]}[Content],
Import = Excel.Workbook(File)
in
Import
9. Why? Hardcoded paths break when files move.

? Task 2: Connect to SQL Server (Import vs. DirectQuery)

Import Mode (Recommended for Performance)

1. Get Data → SQL Server → Enter:
2. Server: your-server.database.windows.net (or localhost for local).
3. Database: AdventureWorks (or your DB).
4. Data Connectivity Mode: Import.
5. Write a query (or select tables):
   sql
SELECT
OrderID,
CustomerID,
OrderDate,
TotalAmount
FROM Sales.Orders
WHERE OrderDate >= DATEADD(YEAR, -1, GETDATE())
6. Why? Filtering at the source (query folding) reduces data transfer.
7. Load → Rename the query to Sales_SQL.

DirectQuery Mode (For Real-Time Data)

1. Repeat steps 1–2, but select DirectQuery instead of Import.
2. Warning: DirectQuery is slower – only use if you must see live data.
3. Production insight: DirectQuery reports cannot use Power BI’s full DAX engine (e.g., no CALCULATE with complex filters).

? Task 3: Connect to a Web API (JSON)

Example: Pull User Data from JSONPlaceholder

1. Get Data → Web → Paste the API URL:
   https://jsonplaceholder.typicode.com/users.
2. Transform the JSON:
3. In Power Query, click the List → To Table → Expand columns.
4. Rename the query to Users_API.
5. Handle pagination (if needed):
6. For APIs with pagination (e.g., ?page=1), use a function:
   powerquery-m
(page as number) =>
let
Source = Json.Document(Web.Contents("https://api.example.com/users?page=" & Text.From(page))),
Data = Source[data]
in
Data
7. Why? Most APIs limit results per call (e.g., 100 rows/page).

? Task 4: Connect to SharePoint Lists

Example: Pull a SharePoint List (e.g., "Sales Adjustments")

1. Get Data → SharePoint List → Enter the site URL:
   https://yourcompany.sharepoint.com/sites/Sales.
2. Select the list → Transform Data.
3. Fix column types:
4. SharePoint often imports dates as text – change to Date/Time.
5. Rename the query to Sales_Adjustments_SharePoint.

4. ? Production-Ready Best Practices

? Security

• Never hardcode credentials:
• Use Power BI Service’s "Data Source Credentials" (for cloud) or Enterprise Gateway (for on-prem).
• For SQL, use Windows Authentication or Service Principal (Azure AD).
• SharePoint permissions:
• Use a service account (not a personal account) for connections.
• Grant read-only access to the SharePoint list/library.
• API keys:
• Store in Azure Key Vault or Power BI parameters (not in the M code).

⚡ Performance

• Query folding:
• Always filter/sort in the source query (not in Power Query).
• Example: WHERE OrderDate > '2023-01-01' (good) vs. filtering after loading (bad).
• Incremental refresh:
• For large tables, set up incremental refresh to only load new data.
• Requires a date column (e.g., LastModifiedDate).
• Avoid "Select *":

• Only pull the columns you need. Example:
  ```sql
  -- Bad
  SELECT * FROM Sales.Orders

  -- Good SELECT OrderID, CustomerID, OrderDate FROM Sales.Orders ```

? Reliability & Maintainability

• Parameterize everything:
• Use Power BI parameters for:
  • File paths (e.g., Excel_Path = "https://...").
  • SQL server names (e.g., SQL_Server = "prod-db").
• Change parameters per environment (dev/prod).
• Gateway setup:
• Install the On-premises Data Gateway on a server (not a laptop).
• Use cluster mode for high availability.
• Naming conventions:
• Prefix queries with the source (e.g., SQL_Sales, Excel_Inventory).
• Avoid spaces/special characters (use Sales_Orders not Sales Orders).

? Observability

• Monitor refresh failures:
• Set up email alerts in Power BI Service for failed refreshes.
• Check Gateway logs (C:\Users\<user>\AppData\Local\Microsoft\On-premises data gateway\) for errors.
• Track query performance:
• In Power Query, go to View → Query Dependencies to see bottlenecks.
• Use DAX Studio to analyze slow measures.

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus (PL-300)

Typical Question Patterns

1. "Which data connectivity mode should you use for real-time data?"
2. ✅ DirectQuery (but know the trade-offs: slower, limited DAX).

3. ❌ Import Mode (not real-time).

4. "How do you secure SQL credentials in Power BI?"

5. ✅ Use Windows Authentication or Azure AD.
6. ✅ Store in Power BI Service’s data source credentials.

7. ❌ Hardcode in M code (security fail).

8. "What’s the best way to handle a 10GB Excel file?"

9. ✅ Use Power Query to filter before loading.
10. ✅ Split into multiple files (e.g., by year).

11. ❌ Load the entire file (will crash Power BI).

12. "How do you refresh a SharePoint list in Power BI Service?"

13. ✅ Install the On-premises Data Gateway (if SharePoint is on-prem).
14. ✅ Use "Organizational" credentials (not personal).
15. ❌ Manual refresh only (not scalable).

Key Trap Distinctions

7. ? Hands-On Challenge (With Solution)

Challenge:

You have an Excel file (Sales.xlsx) with 500K rows. The report refreshes slowly, and the file path is hardcoded to C:\Data\Sales.xlsx. Fix it so:
1. The file path is dynamic (works on any machine).
2. Only the last 30 days of data are loaded (for performance).

Solution:

1. Move the file to SharePoint/OneDrive (or use a parameter for the path).
2. In Power Query, add a filter step:
   powerquery-m
= Table.SelectRows(Source, each [OrderDate] >= Date.AddDays(DateTime.LocalNow(), -30))
3. Parameterize the file path:
4. Create a parameter Excel_Path (type: text).
5. Replace the hardcoded path with Excel.Workbook(Web.Contents(Excel_Path)).

Why it works:
- SharePoint/OneDrive paths are cloud-based (no local dependencies).
- Filtering before loading reduces data volume.
- Parameters make the report portable across environments.

8. ? Rapid-Reference Crib Sheet

Excel

• Best for: Small to medium datasets (<1M rows).
• Avoid: Hardcoded paths (C:\Data\file.xlsx).
• Pro tip: Use SharePoint/OneDrive for cloud-based paths.
• ⚠️ Default behavior: Power BI caches Excel data – clear cache if file changes.

SQL Server

• Best for: Large datasets, relational data.
• Query folding: Always filter/sort in SQL (not Power Query).
• DirectQuery: Only for real-time needs (slower, limited DAX).
• Gateway: Required for on-prem SQL in Power BI Service.
• ⚠️ Default timeout: 10 minutes (adjust in gateway settings).

Web (APIs)

• Best for: JSON/XML feeds, public data.
• Pagination: Most APIs limit results (e.g., 100 rows/page).
• Rate limits: Add delays between requests (e.g., Function.InvokeAfter in M).
• Authentication: Use API keys (store in Azure Key Vault).
• ⚠️ Default behavior: Power BI caches web data for 1 hour.

SharePoint

• Best for: Team collaboration, lists, and files.
• Permissions: Use a service account (not personal).
• Gateway: Required for on-prem SharePoint.
• Lists vs. Files:
• Lists: Use "SharePoint List" connector.
• Files: Use "SharePoint Folder" connector.
• ⚠️ Default behavior: SharePoint Online uses OAuth 2.0 (no gateway needed).

Power Query (M) Snippets

9. ? Where to Go Next

1. Power BI Documentation – Data Sources (Official guide).
2. Power Query (M) Language Reference (For advanced transformations).
3. Power BI Incremental Refresh (For large datasets).
4. DAX Studio (For performance tuning).
5. Power BI Community – Data Connectivity (For troubleshooting).

Final Checklist Before Deploying to Production

✅ All file paths are parameterized (no hardcoded C:\ paths).
✅ SQL queries use query folding (filters/sorts in the source).
✅ APIs handle pagination/rate limits (no "429 Too Many Requests").
✅ SharePoint uses a service account (not a personal account).
✅ Incremental refresh is set up (for large tables).
✅ Gateway is installed on a server (not a laptop).
✅ Credentials are stored securely (no hardcoded passwords).
✅ Refresh schedule is configured (daily/hourly as needed).
✅ Email alerts are set for failures (so you know before your boss does).

Now go build something awesome. ?