Connecting Data in Power BI: Connectors, Import vs DirectQuery, and Power Query

What You Will Learn

• Connect to the most common data sources (files, databases, cloud, web)
• Use Power Query Editor to clean and transform data
• Apply essential transformations: rename, filter, split, merge, pivot
• Understand data types and why they matter
• Combine data from multiple sources with Append and Merge queries
• Write basic M code for custom transformations

---

2.1 How Data Gets Into Power BI

Every report starts with Get Data. Power BI supports over 100 connectors split into categories:

Import Mode vs. DirectQuery vs. Live Connection

This is one of the most important choices you will make:

Beginner tip: Start with Import mode. It is the easiest to work with and gives the best report performance.

---

2.2 Connecting to Common Sources

Connecting to an Excel File

1. Home → Get Data → Excel workbook
2. Browse to your .xlsx file and click Open
3. In the Navigator dialog check the sheets/tables you want
4. Click Transform Data to open Power Query, or Load to import directly

Connecting to a CSV File

1. Home → Get Data → Text/CSV
2. Select your file — Power BI automatically detects delimiters and data types
3. Preview the data, then click Transform Data or Load

Connecting to a SQL Server Database

1. Home → Get Data → SQL Server
2. Enter the Server name (e.g., myserver.database.windows.net)
3. Enter the Database name (optional — leave blank to browse all databases)
4. Choose Import or DirectQuery
5. Select your authentication method (Windows, Database, Microsoft Account)
6. In the Navigator select your tables or write a custom SQL query

Connecting to a Web Page / API

1. Home → Get Data → Web
2. Enter the URL (e.g., https://api.example.com/sales)
3. Power BI fetches and parses the response — HTML tables are detected automatically; JSON/XML can be expanded

Connecting to SharePoint Online

1. Home → Get Data → SharePoint Online List
2. Enter your SharePoint site URL (just the root, e.g., https://company.sharepoint.com/sites/MySite)
3. Select your list from the Navigator

---

2.3 Power Query Editor — Your Data Kitchen

Power Query Editor opens when you click Transform Data. Think of it as a data kitchen: raw ingredients (source data) go in; clean, shaped data comes out.

The Power Query Interface

┌──────────────────────────────────────────────────────────────────┐
│  Ribbon (Home / Transform / Add Column / View / Tools / Help)    │
├───────────┬──────────────────────────────────┬───────────────────┤
│  Queries  │                                  │  Query Settings   │
│  (left    │     Data Preview                 │  - Name           │
│  pane)    │     (rows and columns)           │  - Applied Steps  │
│           │                                  │    (every action  │
│           │                                  │     recorded)     │
└───────────┴──────────────────────────────────┴───────────────────┘
│  Formula bar  (M code for current step)                          │
└──────────────────────────────────────────────────────────────────┘

The Applied Steps Panel — Your Undo History

Every transformation you apply becomes a step in the Applied Steps panel. Steps are:

• Listed in order (most recent at the bottom)
• Editable — click any step to see its M formula
• Deletable — click the ✕ to undo a step
• Reorderable — drag steps up or down

This is a non-destructive process — your original source data is never changed.

---

2.4 Essential Transformations

Rename a Column

Right-click any column header → Rename → type the new name

Change Data Type

Click the data type icon to the left of the column name:

• ABC = Text
• 123 = Whole Number
• 1.2 = Decimal Number
• 📅 = Date/Time
• ☑️ = True/False

Always set data types correctly. A "date" stored as text cannot be used for time intelligence calculations.

Remove Columns

Select the columns you want to keep → Home → Remove Other Columns
Or select columns to remove → Home → Remove Columns

Filter Rows

Click the dropdown arrow on any column header → uncheck values to exclude, or use Text Filters / Number Filters / Date Filters for conditions.

Remove Duplicate Rows

Select the key column → Home → Remove Rows → Remove Duplicates

Replace Values

Right-click a column → Replace Values → enter the old value and new value
Example: Replace "N/A" with null

Split Column by Delimiter

Select a column → Transform → Split Column → By Delimiter
Example: Split "FirstName LastName" on space to get two columns

Trim and Clean Text

Transform → Format → Trim removes leading/trailing spaces
Transform → Format → Clean removes non-printable characters

Fill Down / Fill Up

When a column has blank cells that should inherit the value above:
Select column → Transform → Fill → Down

Unpivot Columns (Wide → Tall)

When months are column headers but should be rows:

1. Select the columns that should stay (e.g., Product, Region)
2. Transform → Unpivot Other Columns

This converts:

Into:

---

2.5 Combining Queries

Append Queries (Stack Rows — like UNION)

Use when you have the same structure in multiple files/tables (e.g., Jan sales + Feb sales + Mar sales):

1. Home → Append Queries → Append Queries as New
2. Choose two or more tables
3. Power BI stacks them into one table

Merge Queries (Join Columns — like SQL JOIN)

Use when you want to bring columns from one table into another based on a matching key:

1. Home → Merge Queries → Merge Queries as New
2. Select the two tables and the matching columns (hold Ctrl to select multiple key columns)
3. Choose the join type:
   • Left Outer — all rows from Table 1, matching from Table 2
   • Inner — only rows matching in both tables
   • Full Outer — all rows from both tables
4. Click OK, then expand the new column to select which fields to bring in

---

2.6 Creating Custom Columns

Conditional Column (GUI — no code)

Add Column → Conditional Column
Example: Create a "Sales Tier" column:

• If Sales >= 10000 → "High"
• If Sales >= 5000 → "Medium"
• Else → "Low"

Custom Column (M formula)

Add Column → Custom Column → enter an M expression

Common M expressions:

// Concatenate two columns
[FirstName] & " " & [LastName]

// Calculate days between two dates
Duration.Days([EndDate] - [StartDate])

// Extract year from a date column
Date.Year([OrderDate])

// Multiply two columns
[Quantity] * [UnitPrice]

// Replace nulls with zero
if [Revenue] = null then 0 else [Revenue]

---

2.7 Parameters — Make Your Queries Dynamic

Parameters let you change source paths, filter values, or server names without editing the query.

Home → Manage Parameters → New Parameter

Use in a filter step: right-click a date column filter → is after or equal to → select your parameter.

---

2.8 M Language Basics (for Technical Users)

Every step in Power Query generates M code. You can view and edit it in two places:

• Formula bar — the M expression for the currently selected step
• Advanced Editor (Home → Advanced Editor) — the full query as a complete M script

M Script Structure

let
    // Step 1: Define the source
    Source = Excel.Workbook(File.Contents("C:\Data\Sales.xlsx"), null, true),
    
    // Step 2: Navigate to the Sheet1 table
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    
    // Step 3: Promote first row to headers
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    
    // Step 4: Change column types
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", {
        {"Date", type date}, 
        {"Sales", type number}, 
        {"Region", type text}
    }),
    
    // Step 5: Filter to 2024 only
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", 
        each Date.Year([Date]) = 2024)
in
    #"Filtered Rows"

Useful M Functions

---

2.9 Best Practices

• Rename every query to something meaningful — dim_Products, fact_Sales, not Table1
• Disable "Load" for staging queries — right-click a query → uncheck "Enable load" for helper/intermediate queries that you don't want in your data model
• Avoid selecting all columns from large databases — only bring in columns you need
• Set data types in Power Query, not later in DAX — it's more efficient
• Use parameters for file paths and date ranges so reports are portable
• Document transformations — rename applied steps to describe what they do

---

Module 2 Summary

You can now:

• Connect to files, databases, and online services
• Choose between Import and DirectQuery appropriately
• Clean and reshape data in Power Query Editor
• Combine tables with Append and Merge
• Write basic M code for custom logic

---

Knowledge Check

1. What is the difference between Append and Merge in Power Query?
2. Why is it important to set data types in Power Query rather than leaving them as "Any"?
3. You have monthly sales files (Jan.csv, Feb.csv, Mar.csv) with identical columns. What Power Query feature would combine them into one table?
4. What does Unpivot Columns do and when would you use it?

---

Next Module

➡️ Module 3 – Data Modeling & Relationships
Turn your clean tables into a proper data model with relationships, star schema design, and calculated columns.