Power BI: Chapter 2 – Connecting to Data

---

What You’ll Learn in Chapter 2

By the end of this chapter, you will be able to:

• Connect Power BI to various types of data sources
• Understand the difference between Import and DirectQuery modes
• Use Power Query Editor to explore and preview data
• Apply basic transformations like renaming, changing data types, and removing columns

---

1️⃣ Power BI: Connecting to Data Sources

Power BI can connect to a wide range of data sources. Here are a few common types:

Source Type	Examples
File	Excel, CSV, XML, JSON
Database	SQL Server, MySQL, PostgreSQL, Oracle
Online Services	SharePoint, OneDrive, Salesforce
Web	Web pages, APIs
Other	Azure, Power Platform, Python/R scripts

🔍 Navigate to Home → Get Data to explore all available connections.

---

2️⃣ Import vs DirectQuery

When you load data into Power BI, you choose how to connect to it.

Mode	Import	DirectQuery
Data Storage	Copies data into Power BI	Queries data live from the source
Performance	Fast (in-memory engine)	Depends on source/database speed
Refresh	Needs scheduled refresh	Always live
Limits	Full modeling and DAX	Limited features and slower performance
Best Use	Most business dashboards	Real-time dashboards, huge datasets

✅ Use Import mode unless you specifically need live or real-time data.

---

3️⃣ Introducing Power Query Editor

Once you connect to a data source and choose a table, you can click Transform Data to open the Power Query Editor.

Here’s what you can do in Power Query:

Task	Example
Rename Columns	Change Column1 to Customer Name
Remove Columns	Delete fields not required like IDs
Change Data Types	Ensure “Date” and “Amount” are correctly typed
Filter Rows	Keep only rows where Sales > 1000
Split Columns	Split Full Name into First and Last Name
Merge Columns	Combine City and Country
Sort Rows	Sort by Date or Revenue

🧠 All actions are saved as steps (like a recipe). You can undo, reorder, or edit them.

---

4️⃣ Step-by-Step: Load and Clean Excel Data

Let’s walk through an example:

🧪 Step-by-Step:

1. Open Power BI Desktop
2. Click Home → Get Data → Excel
3. Select a file (e.g., SalesData.xlsx)
4. Choose the desired worksheet (e.g., Orders)
5. Click Transform Data to open Power Query
6. In Power Query:
   • Rename fields (e.g., Col1 → Order ID)
   • Remove unnecessary columns (right-click → Remove)
   • Ensure columns like Order Date, Revenue, Units have correct data types
7. Click Close & Apply to load the cleaned data into Power BI

🎉 Your clean data is now available in Report, Data, and Model Views.

---

5️⃣ Best Practices When Connecting Data

• ✅ Always rename columns clearly (avoid “Column1”, “Field2”)
• ✅ Set correct data types early (e.g., numbers as numbers)
• ✅ Remove extra header rows or blank columns
• ✅ Don’t load every table — load only what you need
• ✅ Clean the data once in Power BI, not back in Excel

---

🧪 Practice Exercise

Try it yourself:

• Connect to an Excel or CSV file
• Rename at least 3 columns
• Change 2 columns to appropriate data types
• Remove 1 unneeded column
• Load the cleaned dataset

---

📌 Chapter Summary

Concept	You Now Understand…
Connecting to Data	Power BI can pull from Excel, Web, DBs
Import vs DirectQuery	Import = fast; DirectQuery = live
Power Query Editor	A place to clean and shape your data
Data Cleaning Basics	Rename, change types, remove, transform