Getting Data

Every Power BI project begins with data. Power BI provides over 200 native connectors that let you connect to virtually any data source — from simple Excel files on your desktop to massive cloud data warehouses. Understanding how to connect to data sources, choose the right connection mode, and manage credentials and privacy settings is a critical skill for any Power BI developer.

In this chapter, you will learn how to connect to the most common data sources, understand the differences between Import, DirectQuery, and Live Connection modes, manage data source settings and privacy levels, use parameters for dynamic connections, and configure incremental refresh for large datasets.

Data Sources Overview

Power BI organizes its 200+ connectors into several categories. When you click Home → Get Data, you will see these categories in the left panel of the Get Data dialog.

Categories of Data Sources

Category	Examples	Description
File	Excel, CSV, Text, JSON, XML, PDF, Parquet, SharePoint Folder	Local or network files containing structured data
Database	SQL Server, PostgreSQL, MySQL, Oracle, IBM Db2, SAP HANA, Amazon Redshift, Snowflake	Relational and analytical databases
Power Platform	Dataverse, Dataflows, Power BI Datasets (Semantic Models)	Microsoft Power Platform services
Azure	Azure SQL Database, Azure Synapse Analytics, Azure Blob Storage, Azure Data Lake Storage, Azure Cosmos DB, Azure Databricks	Microsoft Azure cloud services
Online Services	SharePoint Online List, Microsoft Exchange Online, Dynamics 365, Salesforce, Google Analytics, Adobe Analytics, GitHub, Zendesk	SaaS applications and cloud services
Other	Web, OData Feed, ODBC, OLE DB, R Script, Python Script, Blank Query, Spark, FHIR, Active Directory	Miscellaneous and custom connectors

Most Common Connectors

In everyday work, you will use a handful of connectors most frequently:

Excel Workbook — The most common starting point for many analysts
CSV / Text File — Simple flat files with delimiter-separated values
SQL Server — Microsoft's relational database, widely used in enterprise environments
Web — Scraping data from web pages or calling REST APIs
SharePoint Online List — Accessing data stored in SharePoint lists
Folder — Combining multiple files from a directory into a single table
Power BI Dataflows — Reusing centrally defined data preparation logic
Azure SQL Database — Cloud-hosted SQL Server databases
OData Feed — Consuming standardized API endpoints
Dataverse — Microsoft's low-code data platform (formerly Common Data Service)

Import vs DirectQuery vs Live Connection

One of the most important decisions you make when connecting to a data source is the connection mode. Power BI offers three modes, each with distinct trade-offs.

Comparison Table

Feature	Import	DirectQuery	Live Connection
Data Location	Data is loaded into Power BI's in-memory engine (VertiPaq)	Data remains in the source; queries are sent on demand	Data remains in the source (SSAS or Power BI Dataset)
Supported Sources	All connectors	Selected connectors (SQL Server, Azure SQL, Synapse, Oracle, SAP, etc.)	SQL Server Analysis Services (SSAS), Power BI Datasets
Data Freshness	As of last refresh	Real-time (queries the source live)	Real-time
Performance	Very fast (in-memory)	Depends on source query performance	Depends on source performance
DAX Support	Full	Full (calculated tables not supported)	Measures only (model is remote)
Power Query	Full transformation capabilities	Limited (some transforms break query folding)	Not available (model is predefined)
File Size	Can be large (data stored in .pbix)	Small (no data stored locally)	Small
Max Dataset Size (Service)	1 GB (Pro), 100 GB (PPU), 400 GB (Premium)	No local limit (source-dependent)	Source-dependent
Refresh	Scheduled or on-demand refresh	No refresh needed (always live)	No refresh needed
Composite Models	Can combine with DirectQuery	Can combine with Import	Cannot combine with other modes
Ideal For	Small to medium datasets (<1 GB); best performance	Large datasets that cannot be imported; real-time needs	Connecting to centralized enterprise semantic models

Import Mode (Default)

Import mode loads a snapshot of the data into Power BI's in-memory columnar engine (VertiPaq). This is the default and most commonly used mode.

How it works:

Power BI executes queries against the source to extract data
Data is compressed and stored in the .pbix file
All visuals query the in-memory engine, which is extremely fast
To get updated data, you must refresh the dataset (manually or on a schedule)

Advantages:

Fastest query performance (in-memory)
Full Power Query transformation support
Full DAX functionality
Works with all connectors
No dependency on source availability after loading

Disadvantages:

Data can become stale between refreshes
File size grows with data volume
Dataset size limits apply (1 GB for Pro)
Full data is stored in the file (security consideration)

When to use: Most scenarios, especially when your data is under 1 GB and does not need real-time freshness.

DirectQuery Mode

DirectQuery sends queries directly to the source database each time a user interacts with a visual.

How it works:

Power BI generates DAX queries for each visual
These are translated into the source's native query language (e.g., SQL)
Queries are sent to the source, results are returned and rendered
No data is stored in the .pbix file

Advantages:

Always shows current data (no refresh needed)
No dataset size limits
Small file size
Data stays in the source (security benefit)

Disadvantages:

Slower performance (depends on source speed)
Limited Power Query transformations (must maintain query folding)
Some DAX functions are not supported or perform poorly
Requires the source to be always available
Can put heavy load on the source database
Each user interaction generates queries

When to use: Large datasets (>1 GB) that cannot be imported; scenarios requiring real-time data; regulatory requirements to keep data in the source system.

Live Connection

Live Connection connects to an existing Analysis Services model (SSAS Tabular/Multidimensional) or a Power BI Dataset in the Service.

How it works:

Power BI connects to a remote semantic model
The data model (tables, relationships, measures) is defined in the remote source
You build report visuals on top of the remote model
No Power Query or local modeling is available

Advantages:

Leverages centralized enterprise models
Consistent metrics across all reports
No data duplication

Disadvantages:

Cannot modify the data model
Cannot use Power Query
Can only add measures (in some scenarios)
Fully dependent on the remote model's design

When to use: When your organization has a centralized Analysis Services model or a shared Power BI dataset that serves as the "single source of truth."

Choosing the Right Mode

Is your data < 1 GB and does not need real-time freshness?
├── YES → Use Import Mode
└── NO
    ├── Is the data in SSAS or an existing Power BI Dataset?
    │   ├── YES → Use Live Connection
    │   └── NO → Continue
    └── Does the data need to be real-time and/or is too large to import?
        ├── YES → Use DirectQuery
        └── NO → Use Import with incremental refresh

Connecting to Excel Files

Excel is the most common starting point for Power BI projects. Here is the step-by-step process.

Step-by-Step: Connect to an Excel File

Open Power BI Desktop and click Home → Get Data → Excel Workbook (or Get Data → More → File → Excel Workbook)
Browse to your Excel file using the file explorer dialog and click Open
The Navigator dialog appears — This is a crucial dialog that shows you all available data objects in the Excel file:
- Sheets — Each worksheet appears as an option (e.g., "Sheet1", "SalesData")
- Tables — Any named Excel tables appear separately (e.g., "Table_Sales")
- Named Ranges — Any defined named ranges also appear
Select the data you want — Check the boxes next to the tables/sheets you want to load. A preview of the data appears on the right side.
Choose Load or Transform Data:
- Load — Immediately loads the data into the model as-is
- Transform Data — Opens Power Query Editor so you can clean and shape the data first (recommended)

Best Practices for Excel Data

Use named Excel tables — Always format your data as a named table in Excel (Ctrl+T). Named tables are more reliable than raw sheets because they handle row additions dynamically.
Keep headers in the first row — Power BI expects column headers in row 1
Avoid merged cells — Merged cells cause import errors and data misalignment
Remove summary rows — If your sheet has total/subtotal rows, they will be imported as data rows. Remove them or handle them in Power Query.
Clean up the file — Remove empty columns, charts, and other non-data elements from the sheet
One table per sheet — Avoid putting multiple unrelated tables on a single sheet

Common Issues with Excel Files

Issue	Cause	Solution
Column headers are wrong	First row is not the header	In Power Query: Home → Use First Row as Headers
Extra blank rows at the bottom	Empty formatted cells in Excel	In Power Query: Remove blank rows
Numbers stored as text	Excel formatting issue	In Power Query: Change column type to Whole Number or Decimal
Dates showing as numbers	Serial date format	In Power Query: Change column type to Date
#N/A or #REF! errors in cells	Excel formula errors	In Power Query: Replace errors or remove error rows
File path changed	File moved or renamed	Update the data source path in Data Source Settings

Connecting to CSV/Text Files

CSV (Comma-Separated Values) and other delimited text files are another common data source.

Step-by-Step: Connect to a CSV File

Click Home → Get Data → Text/CSV
Browse to your CSV file and click Open
Power BI displays a preview dialog with automatic detection of:
- Delimiter — Comma, semicolon, tab, pipe, or custom delimiter
- Data Type Detection — Based on first 200 rows
- Encoding — UTF-8, UTF-16, ASCII, etc.
Review the preview and adjust settings if needed
Click Load or Transform Data

Key Settings for CSV Files

Setting	Description	When to Change
File Origin	Character encoding (UTF-8, Windows-1252, etc.)	When you see garbled characters (é, ñ, ü rendering incorrectly)
Delimiter	The character that separates columns	When columns are not split correctly (e.g., semicolons used instead of commas)
Data Type Detection	How Power BI detects column types	"Based on first 200 rows" is default; change to "Based on entire dataset" for more accurate detection
Skip rows	Number of rows to skip from the top	When the file has metadata rows before the actual data

CSV vs Excel: Key Differences

Feature	CSV	Excel
Multiple sheets	No (single table per file)	Yes
Data types	Everything is text (types are inferred)	Data types are preserved
File size	Generally smaller	Can be larger due to formatting
Formatting	None	Cell formatting, formulas, charts
Unicode support	Depends on encoding	Full support
Best for	Data exports, log files, API outputs	Structured business data, reports

Connecting to SQL Server

SQL Server is the most common database connector used with Power BI, especially in enterprise environments.

Step-by-Step: Connect to SQL Server

Click Home → Get Data → SQL Server (or Get Data → More → Database → SQL Server Database)
Enter the Server name — This can be:
- A server name: SERVERNAME
- A named instance: SERVERNAME\INSTANCENAME
- An IP address: 192.168.1.100
- An Azure SQL server: yourserver.database.windows.net
Enter the Database name (optional) — If left blank, you will see all databases on the server
Expand Advanced options (optional) to set:
- Command timeout — Maximum query wait time in minutes
- SQL statement — Write a custom SQL query instead of selecting tables
- Include relationship columns — Include foreign key columns
- Navigate using full hierarchy — Show schema organization
- Enable SQL Server Failover support — For high-availability setups
Choose Data Connectivity mode:
- Import (default) — Load data into Power BI
- DirectQuery — Query the database live
Click OK
Authentication dialog — Choose the authentication method:
- Windows — Uses your Windows login (Kerberos/NTLM)
- Database — SQL Server username and password
- Microsoft Account — For Azure SQL (Azure Active Directory)
Navigator dialog — Browse the database structure:
- Expand schemas (dbo, sales, hr, etc.)
- Select tables and views
- Preview data on the right side
- Select multiple objects by checking their boxes
Click Load or Transform Data

Writing Custom SQL Queries

Instead of selecting tables from the Navigator, you can write custom SQL in the advanced options:

SELECT
    o.OrderID,
    o.OrderDate,
    c.CustomerName,
    c.City,
    p.ProductName,
    p.Category,
    od.Quantity,
    od.UnitPrice,
    od.Quantity * od.UnitPrice AS LineTotal
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
INNER JOIN OrderDetails od ON o.OrderID = od.OrderID
INNER JOIN Products p ON od.ProductID = p.ProductID
WHERE o.OrderDate >= '2025-01-01'

Advantages of custom SQL:

Pre-join tables to simplify the model
Filter data at the source (better performance)
Use SQL functions not available in Power Query
Reduce data volume before loading

Disadvantages:

Breaks query folding for subsequent Power Query steps
Harder to maintain than visual Power Query steps
Requires SQL knowledge
The query runs as a "black box" in Power Query

SQL Server Connection Tips

Always specify the database name — Avoids confusion when a server hosts multiple databases
Use views instead of tables — Database views can pre-filter and pre-join data, simplifying your model
Consider stored procedures — Use EXEC dbo.sp_GetSalesData for parameterized data extraction
Test your queries — Run custom SQL in SQL Server Management Studio (SSMS) first to verify results and performance
Use Windows authentication when possible — More secure and does not require storing passwords

Connecting to Web Data

Power BI can extract data from web pages, REST APIs, and online data sources using the Web connector.

Step-by-Step: Connect to a Web Page

Click Home → Get Data → Web
In the dialog, select Basic and enter the URL of the web page containing tabular data
Click OK
Authentication dialog — Select the appropriate method:
- Anonymous — No credentials needed (public web pages)
- Windows — Pass Windows credentials to the web server
- Basic — Username and password (HTTP Basic Auth)
- Web API — API key authentication
- Organizational Account — OAuth2 / Microsoft Entra ID authentication
The Navigator displays all tables detected on the web page
Select the table(s) you want and click Load or Transform Data

Connecting to REST APIs

For REST APIs that return JSON data:

Click Home → Get Data → Web
Select Advanced to configure:
- URL parts — Build the URL dynamically
- HTTP request header parameters — Add headers like Authorization, Content-Type, Accept
Enter the API endpoint URL (e.g., https://api.example.com/data)
Add required headers:

Authorization: Bearer your-api-token-here
Content-Type: application/json

Click OK
Power BI will fetch the JSON response and open it in Power Query
Use Power Query to parse the JSON structure:
- Convert JSON records to a table
- Expand nested columns
- Rename columns as needed

Example: Fetching JSON from an API

When Power BI receives JSON data like:

{
  "results": [
    {"id": 1, "name": "Alice", "department": "Engineering"},
    {"id": 2, "name": "Bob", "department": "Marketing"},
    {"id": 3, "name": "Carol", "department": "Finance"}
  ],
  "total": 3
}

In Power Query, you would:

The response loads as a Record — click on "results" to navigate into the list
Click To Table to convert the list to a table
Click the expand icon (double arrow) on the Column1 header to expand the records
Select the fields you want (id, name, department) and click OK
Rename and retype columns as needed

Web Connector Limitations

Power BI can only scrape data from HTML tables on web pages (not JavaScript-rendered content)
Complex websites with dynamic content may not work with the basic Web connector
APIs with pagination require custom M code or Power Query logic to fetch all pages
Rate limits on APIs may cause timeout errors during refresh

Connecting to Folders

The Folder connector is powerful for combining multiple files of the same structure into a single table.

Use Cases

Combining monthly sales reports stored as separate Excel files
Merging CSV log files from a directory
Consolidating regional data files into one dataset

Step-by-Step: Connect to a Folder

Click Home → Get Data → More → File → Folder
Enter or browse to the folder path (local, network share, or mapped drive)
Click OK
Power BI lists all files in the folder with metadata:
- File name, extension, date modified, size, folder path
Click Combine & Transform Data (recommended) or Combine & Load
The Combine Files dialog appears:
- Select a sample file to define the structure
- Choose the sheet/table within each file (for Excel files)
- Configure the delimiter (for CSV files)
Click OK

Power BI automatically:

Creates a function to extract data from each file
Applies the function to all files in the folder
Appends all results into a single table
Adds a "Source.Name" column identifying which file each row came from

Important Notes

All files in the folder must have the same structure (same columns in the same order)
When new files are added to the folder, they are automatically included on the next refresh
You can filter files by name or extension in Power Query to exclude unwanted files
Subfolders are included by default — filter the "Folder Path" column to exclude them if needed

Data Source Settings

After connecting to data sources, you may need to update credentials, change file paths, or modify connection details.

Accessing Data Source Settings

From Power BI Desktop:

Go to File → Options and settings → Data source settings
You will see a list of all data sources used in the current file
Select a source to:
- Edit Permissions — Change credentials, privacy level, or encryption settings
- Change Source — Update the file path, server name, or URL
- Clear Permissions — Remove stored credentials (forces re-authentication)
- Delete — Remove the data source reference

Changing Data Source Paths

When a file is moved or a server name changes:

Open Data Source Settings (File → Options and settings → Data source settings)
Select the data source
Click Change Source
Enter the new path, server name, or URL
Click OK
Refresh the data to verify the connection works

Alternative method: In Power Query Editor, click on the Source step in Applied Steps. Edit the file path or server name directly in the formula bar.

Managing Credentials

Credentials are stored per data source and per user. To update or change credentials:

Open Data Source Settings
Select the data source
Click Edit Permissions
Under Credentials, click Edit
Choose the authentication type and enter new credentials
Click Save

Tip: If you encounter "credentials are invalid" errors after a password change, clear the permissions and re-enter your credentials.

Privacy Levels

Privacy levels are a crucial but often confusing aspect of Power BI data connections. They control how data from different sources can be combined and whether query folding can send data between sources.

The Three Privacy Levels

Privacy Level	Description	Example Data Sources
Private	Data source contains sensitive data; no data from this source is sent to other sources	Employee salaries, medical records, financial data
Organizational	Data source contains data visible within the organization but not publicly	Internal sales data, CRM data, SharePoint lists
Public	Data source contains publicly available data; can be freely combined with any source	Census data, public APIs, web-scraped data

How Privacy Levels Work

When you combine data from multiple sources (e.g., merging an Excel file with a SQL Server table), Power BI checks the privacy levels to determine if the combination is allowed:

Source A	Source B	Result
Public	Public	Allowed
Public	Organizational	Allowed
Public	Private	Blocked
Organizational	Organizational	Allowed
Organizational	Private	Blocked
Private	Private	Blocked (from different sources)

Common Privacy Level Errors

Error: "Formula.Firewall: Query references other queries or steps, so it may not directly access a data source."

This error occurs when Power BI's privacy firewall blocks data from being sent between sources.

Solutions:

Set appropriate privacy levels — In Data Source Settings, edit permissions for each source and set the correct privacy level
Ignore privacy levels (development only) — Go to File → Options and settings → Options → Current File → Privacy, select "Ignore the Privacy levels and potentially improve performance." Warning: Only use this during development. It bypasses all privacy protections.
Restructure your queries — Avoid mixing source access and transformations in a single query. Use staging queries that load data first, then reference them in transformation queries.

Setting Privacy Levels

Go to File → Options and settings → Data source settings
Select a data source and click Edit Permissions
Under Privacy Level, select the appropriate level from the dropdown
Click OK

Parameters

Power Query parameters allow you to create dynamic, reusable values that can be used in data source paths, filter conditions, and other query settings.

Why Use Parameters?

Dynamic file paths — Change the data source path without editing queries
Environment switching — Switch between development, testing, and production databases
User-driven filtering — Let users specify a date range or region before loading data
Template files — When users open a .pbit template, they are prompted to enter parameter values

Creating a Parameter

In Power Query Editor, click Home → Manage Parameters → New Parameter
Configure the parameter:

Setting	Description	Example
Name	A descriptive name	`ServerName`
Description	Optional explanation	"The SQL Server to connect to"
Required	Whether the parameter must have a value	Yes
Type	Data type (Text, Decimal Number, Date, etc.)	Text
Suggested Values	Any value, list of values, or query	List of values
Default Value	The initial value	`prod-server.database.windows.net`
Current Value	The currently active value	`dev-server.database.windows.net`

Click OK

Using Parameters in Data Sources

Once created, you can use parameters in your data source connections:

Example: Parameterized SQL Server connection

In Power Query, modify the Source step to use the parameter:

let
    Source = Sql.Database(ServerName, DatabaseName),
    dbo_Sales = Source{[Schema="dbo", Item="Sales"]}[Data]
in
    dbo_Sales

Where ServerName and DatabaseName are parameters.

Example: Parameterized file path

let
    Source = Excel.Workbook(File.Contents(FilePath & "\SalesData.xlsx"), null, true),
    Sheet1 = Source{[Item="Sheet1", Kind="Sheet"]}[Data]
in
    Sheet1

Where FilePath is a parameter like C:\Data\2025.

Switching Parameter Values

To change parameter values:

In Power Query Editor, click Home → Manage Parameters → Edit Parameters
Update the current value for each parameter
Click OK
Refresh the queries

Or simply click on the parameter in the Queries pane and edit the Current Value in the parameter settings panel.

Incremental Refresh

Incremental refresh is a feature that optimizes data refresh for large datasets by only refreshing the most recent data, rather than reloading the entire dataset every time.

Why Use Incremental Refresh?

Without incremental refresh, a dataset with millions of rows must be fully reloaded on every refresh. This causes:

Long refresh times (minutes or hours)
Heavy load on the source database
High memory consumption
Risk of timeout errors

With incremental refresh:

Only new and changed data is refreshed
Historical data is cached and not re-queried
Refresh times are dramatically reduced
Source database load is minimized

How It Works

Incremental refresh divides your data into two ranges based on date:

Archive range — Historical data that rarely changes (e.g., data older than 30 days). This data is loaded once and not refreshed again.
Incremental range — Recent data that may change (e.g., the last 30 days). This data is refreshed on every cycle.

[========= Archive (cached) =========][== Incremental (refreshed) ==][New]
   2023         2024        Jan-Feb 2026      Mar 2026              Today

Prerequisites

The table must have a Date/DateTime column that defines the partition boundary
Two reserved parameters must be created in Power Query:
- RangeStart (type: Date/Time)
- RangeEnd (type: Date/Time)
The data source must support query folding so the date filter is pushed to the source
The dataset must be published to the Power BI Service (incremental refresh does not run in Desktop)

Setting Up Incremental Refresh

Step 1: Create the required parameters

In Power Query, create a parameter named RangeStart:
- Type: Date/Time
- Current Value: a sample date (e.g., 1/1/2025 12:00:00 AM)
Create a parameter named RangeEnd:
- Type: Date/Time
- Current Value: a date after RangeStart (e.g., 4/1/2025 12:00:00 AM)

Step 2: Filter the table using the parameters

Select your date column in Power Query
Apply a custom filter:
- Greater than or equal to RangeStart
- AND less than RangeEnd

The resulting M code will look like:

= Table.SelectRows(PreviousStep, each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd)

Step 3: Configure incremental refresh policy

In Power BI Desktop, right-click the table in the Fields pane
Select Incremental refresh and real-time data
Configure:
- Archive data starting — How far back to keep historical data (e.g., 3 years)
- Incrementally refresh data starting — How far back to refresh on each cycle (e.g., 30 days)
- Detect data changes (optional) — Use a column like LastModified to detect changed rows in the archive range
- Only refresh complete days/periods (optional) — Wait until a full day/period is complete before refreshing
Click Apply

Step 4: Publish and configure refresh

Publish the report to the Power BI Service
In the Service, configure a scheduled refresh
The first refresh loads all historical data (may take a long time)
Subsequent refreshes only process the incremental range

Incremental Refresh Summary Table

Setting	Description	Example Value
Archive period	How far back to retain data	3 years
Incremental period	How far back to refresh	30 days
Detect changes column	A column to identify changed rows	`LastModifiedDate`
Complete periods	Only refresh after period ends	Yes (avoid partial day data)

Common Connection Errors

When connecting to data sources, you may encounter various errors. Here is a troubleshooting guide for the most common issues:

Error	Likely Cause	Solution
"We couldn't find any data formatted as a table"	Excel file has no named tables or structured data	Format data as a table in Excel (Ctrl+T) or use Power Query to promote headers
"Unable to connect to the server"	Server name is wrong, server is down, or firewall is blocking	Verify server name, check server status, confirm firewall rules allow port 1433 (SQL Server)
"Login failed for user"	Wrong username/password or insufficient permissions	Verify credentials, check that the user has SELECT permissions on the required tables
"The credentials provided are invalid"	Stored credentials have expired or password changed	Clear permissions in Data Source Settings and re-enter credentials
"A firewall is blocking the connection"	Network firewall or Windows Firewall blocking outbound connections	Contact IT to open the required port; for Azure SQL, add your IP to the server's firewall rules
"The driver was not found"	Missing ODBC or OLE DB driver for the data source	Download and install the required driver (e.g., MySQL ODBC driver, Oracle client)
"Query timeout expired"	Query takes too long to execute	Increase timeout in advanced options; optimize the source query; use a smaller date range
"Access to the resource is forbidden"	OAuth token expired or insufficient API permissions	Re-authenticate; check API permissions and scopes
"Out of memory"	Dataset is too large for available RAM	Filter data at the source; increase system RAM; consider DirectQuery
"Formula.Firewall"	Privacy level conflict between sources	Set correct privacy levels for each source or restructure queries
"The column 'X' was not found"	Source schema has changed (column renamed or removed)	Update Power Query to reflect the new schema; check source for changes
"Data source error: Could not find file"	File has been moved, renamed, or deleted	Update file path in Data Source Settings or Power Query Source step
"Expression.Error: We cannot apply operator + to types Text and Number"	Data type mismatch in a transformation step	Convert columns to the correct type before performing operations
"Connection timed out"	Network issue or source server is overloaded	Retry; check network connectivity; contact the DBA to check server load

General Troubleshooting Steps

Check the error message carefully — It usually indicates the root cause
Test the connection outside Power BI — Use SSMS for SQL Server, a browser for web URLs, or file explorer for file paths
Verify credentials — Clear and re-enter in Data Source Settings
Check network connectivity — Can you ping the server? Is VPN connected?
Update Power BI Desktop — Some connector issues are fixed in newer versions
Check the Power BI community forums — Most errors have been encountered and solved by others
Review the Privacy levels — This is the most common cause of Formula.Firewall errors

Practice Exercises

Exercise 1: Connect to an Excel File

Objective: Practice loading data from Excel.

Instructions:

Create an Excel file called Products.xlsx with a table containing:
- ProductID (1–20)
- ProductName (various product names)
- Category (Electronics, Clothing, Food, Furniture)
- Price (various decimal numbers)
- InStock (TRUE/FALSE)
Save the file and open Power BI Desktop
Connect to the Excel file using Get Data → Excel Workbook
Preview the data in the Navigator dialog
Click Transform Data to open Power Query Editor
Verify that data types are correctly detected (especially Price as Decimal and InStock as Boolean)
Fix any data type issues, then click Close & Apply
Create a bar chart showing average Price by Category
Save the report

Exercise 2: Connect to a CSV File

Objective: Practice handling CSV data with different delimiters and encodings.

Instructions:

Create a CSV file called sales_log.csv with semicolons as delimiters:

Date;Region;Amount;Quantity
2025-01-15;North;1250.50;10
2025-01-16;South;890.25;7
2025-01-17;East;1540.00;12
2025-01-18;West;2100.75;15
2025-01-19;North;960.00;8

Connect to the CSV file in Power BI
Verify that Power BI detects the semicolon delimiter
If not, manually change the delimiter in the preview dialog
Load the data and create a line chart showing Amount by Date with Region as the Legend
Save the report

Exercise 3: Connect to Web Data

Objective: Practice extracting tables from web pages.

Instructions:

In Power BI Desktop, click Get Data → Web
Enter a URL for a web page that contains a table (e.g., a Wikipedia page with statistical tables)
Select Anonymous authentication
Browse the detected tables in the Navigator
Select a table and click Transform Data
Clean the data in Power Query:
- Remove unnecessary columns
- Fix data types
- Remove header/footer rows if present
Load the data and create a visual

Exercise 4: Import vs DirectQuery Comparison

Objective: Understand when to use Import vs DirectQuery.

Instructions: For each scenario below, recommend Import, DirectQuery, or Live Connection, and explain your reasoning:

A 500 MB sales dataset refreshed weekly from a SQL Server
A 50 GB transaction database where users need to see real-time data
An enterprise Analysis Services model shared across 20 reports
A small CSV file with 1,000 rows exported from a web application
A 2 GB dataset that changes every 5 minutes and is stored in Azure SQL
A Power BI dataset already published in the Service that other teams want to reuse

Exercise 5: Create Parameters

Objective: Practice creating and using Power Query parameters.

Instructions:

Open the report from Exercise 1 (or create a new one)
Open Power Query Editor
Create a parameter called MinPrice of type Decimal Number with a default value of 0
Create a parameter called SelectedCategory of type Text with a list of suggested values: Electronics, Clothing, Food, Furniture
Modify your Products query to filter:
- Price greater than or equal to MinPrice
- Category equals SelectedCategory
Test by changing parameter values and refreshing the preview
Close & Apply and verify the filtered data in your report

Exercise 6: Troubleshoot Connection Errors

Objective: Practice diagnosing and solving common connection errors.

Instructions: For each error scenario below, identify the cause and write the steps to fix it:

You moved your Excel file from C:\Data\Sales.xlsx to D:\Reports\Sales.xlsx and now the refresh fails
Your SQL Server connection fails with "Unable to connect" but you could connect yesterday
You are combining data from an Excel file and a SQL Server table, and you get a Formula.Firewall error
Your CSV file loads correctly but all numbers show as text

Summary

In this chapter, you learned how to connect Power BI to various data sources and manage those connections effectively:

Power BI offers 200+ connectors organized into categories: Files, Databases, Power Platform, Azure, Online Services, and Other
Three connection modes serve different needs:
- Import — Best performance, loads data into memory, requires refresh
- DirectQuery — Real-time data, queries the source live, depends on source performance
- Live Connection — Connects to existing Analysis Services or Power BI semantic models
Excel connections use the Navigator dialog to select sheets, tables, or named ranges
CSV connections require attention to delimiter, encoding, and data type detection
SQL Server connections support Windows/Database/Microsoft authentication and custom SQL queries
Web connections can extract HTML tables and consume REST APIs with various authentication methods
Folder connections combine multiple files of the same structure into a single table
Data Source Settings let you manage credentials, change paths, and set privacy levels
Privacy Levels (Private, Organizational, Public) control how data from different sources can be combined
Parameters enable dynamic, reusable connections that can switch between environments or accept user input
Incremental Refresh optimizes large dataset refreshes by only processing new and changed data
Common errors can usually be resolved by checking credentials, network connectivity, privacy levels, and data source paths

In the next chapter, you will learn how to use Power Query to clean, transform, and shape your data before loading it into the data model.