Connecting to Data Sources

What You'll Learn

• Connect to different types of data sources
• Import vs DirectQuery modes
• Understand data source connectors
• Best practices for connecting data

Power BI Data Connectors

Power BI can connect to 100+ data sources including:

Files:

• Excel workbooks
• CSV/Text files
• XML files
• JSON files
• PDF files

Databases:

• SQL Server
• MySQL
• PostgreSQL
• Oracle
• Azure SQL Database

Cloud Services:

• SharePoint
• Google Analytics
• Salesforce
• Dynamics 365
• Azure services

Other Sources:

• Web pages
• OData feeds
• Blank query (manual entry)

Connecting to Excel

Most common for beginners. Let's walk through it:

Steps:

1. Open Power BI Desktop
2. Click Home > Get Data > Excel
3. Browse to your Excel file
4. Select it and click Open
5. Navigator window appears showing sheets/tables
6. Check the tables you want
7. Click Load or Transform Data

Load vs Transform:

• Load: Imports data as-is
• Transform Data: Opens Power Query Editor for cleaning

Connecting to SQL Database

For enterprise data:

Steps:

1. Home > Get Data > SQL Server
2. Enter server name
3. Enter database name
4. Choose authentication (Windows or Database)
5. Click OK
6. Select tables from Navigator
7. Click Load or Transform Data

Import vs DirectQuery

Two ways to connect to data:

Import Mode

How it works:

• Data is copied into Power BI
• Stored in .pbix file
• Refreshes on schedule

Pros:

• Fast performance
• Works offline
• All features available

Cons:

• File size grows
• Data not real-time
• Refresh limits

Best for:

• Small to medium datasets
• When you need all features
• Static or scheduled data

DirectQuery Mode

How it works:

• No data copied
• Queries database in real-time
• Always shows latest data

Pros:

• No file size issues
• Real-time data
• Large datasets

Cons:

• Slower performance
• Some features unavailable
• Requires database connection

Best for:

• Very large datasets
• Real-time requirements
• Sensitive data (keep in source)

Connecting to Web Data

Scrape data from websites:

Steps:

1. Home > Get Data > Web
2. Enter URL
3. Click OK
4. Power BI detects tables on page
5. Select data and click Load

Example: Wikipedia tables, stock prices, weather data

Connecting to CSV Files

Simple text files with data:

Steps:

1. Home > Get Data > Text/CSV
2. Browse to your .csv file
3. Preview appears
4. Check delimiter (comma, tab, etc.)
5. Click Load or Transform Data

Connecting Multiple Sources

You can connect to multiple sources in one report:

Example:

• Sales data from SQL Server
• Budget data from Excel
• Customer data from Salesforce

Power BI combines them in one model!

Data Source Settings

Manage your connections:

Access Settings:

1. Home > Transform Data > Data source settings
2. See all connections
3. Edit credentials
4. Change server/file paths

Edit Permissions:

• Change authentication
• Update passwords
• Remove old connections

Common Connection Issues

Problem: "Couldn't find file"

• File moved or deleted
• Update path in data source settings

Problem: "Authentication failed"

• Wrong credentials
• Update in data source settings

Problem: "Data source not supported"

• Use custom connector
• Export data to supported format

Best Practices

Use Transform Data: Always click "Transform Data" instead of "Load" to clean first

Limit Data: Filter early to reduce data size

Consistent Sources: Keep data sources in stable locations

Document Connections: Add notes about where data comes from

Security: Never share files with embedded credentials

Try This Exercise

Connect to Sample Data:

1. Create a simple Excel file with:
   • Product names
   • Prices
   • Categories
2. Save it
3. Connect to it in Power BI
4. Load the data
5. Verify it appears in Fields pane

Next Steps

Now that you can connect to data, let's learn Power Query to clean and transform it!

Tip: Start with familiar sources like Excel. Graduate to databases as you gain confidence.