Connecting Data in Power BI¶

A Power BI report is only as good as its data pipeline. Power BI's "Get Data" covers 150+ connectors, and Power Query Editor lets you transform messy source data into clean, analysis-ready tables — without touching the original file.

Learning Objectives¶

Connect to common data sources: Excel, CSV, SQL Server, web pages
Navigate the Power Query Editor and apply basic transformations
Understand Applied Steps and why they matter for reproducibility
Know when to use Power Query vs DAX for data transformations

Get Data¶

Click Home → Get Data to see the full connector list. The most common sources for analysts:

Source	When to use
Excel Workbook	Shared files, manual input data, existing reports
Text/CSV	Exports from databases or legacy systems
SQL Server	Direct database connection (requires credentials)
Web	Scraping public tables (e.g., Wikipedia, government data)
SharePoint Folder	Multiple files in a folder — combine them automatically
Azure SQL Database	Cloud database — same as SQL Server but over the internet
Google Analytics	Website traffic data (via connector)
Salesforce	CRM data
OData Feed	REST APIs that support the OData protocol

Get Data search

The Get Data dialog has a search box at the top. Type "postgres" or "snowflake" or "bigquery" and the matching connector appears. You do not need to browse the full list.

Power Query Editor¶

When you click Transform Data (or connect and choose to transform), Power Query Editor opens. Think of it as Excel's Power Query — if you have used that, the interface is identical.

Power Query is a separate application layer that runs before data enters the model. Every transformation here happens in memory during load/refresh — the source data is never modified.

Why Power Query and Not DAX?¶

Task	Use Power Query (M)	Use DAX
Rename columns	Yes	No
Fix data types	Yes	No
Filter out bad rows	Yes	Sometimes
Merge two tables	Yes	No (use relationships)
Create a calculated ratio	Sometimes	Yes
Year-over-year comparison	No	Yes
Dynamic measure based on slicer	No	Yes

Decision rule

Transform the shape of data in Power Query. Calculate business metrics in DAX. If a column will never change based on user interaction, it belongs in Power Query.

Basic Transformations¶

Rename Columns¶

Right-click the column header → Rename. Or double-click the header.

Why it matters: Source column names like cust_id_fk or col_A are unacceptable in a report. Rename to Customer ID, Region, Revenue before anything else.

Change Data Types¶

Click the data type icon on the left of the column header: - ABC = text - 123 = whole number - 1.2 = decimal number - 📅 = date/datetime - True/False = boolean

The silent date problem

If your date column imports as text (common with CSV exports formatted as "2024-01-15"), every time intelligence DAX function will return an error or blank. Always verify date columns show the calendar icon before closing Power Query.

Remove Columns¶

Right-click the column header → Remove Column. Or Home → Choose Columns to keep only the ones you want (safer when the source might add new columns).

Filter Rows¶

Click the dropdown arrow on any column header → Filter rows by value. Useful for: - Removing test data (WHERE environment = 'production') - Limiting date range during development (speeds up refreshes) - Removing blank rows at the bottom of Excel files

Split Columns¶

Home → Split Column — split by delimiter or by number of characters. Common use: a "Full Name" column that needs to become "First Name" and "Last Name".

Replace Values¶

Right-click a column → Replace Values. Use for: - Standardizing category labels: "USA", "U.S.A.", "United States" → "United States" - Replacing nulls with a default: blank → 0 (for numeric columns only where 0 is meaningful)

Replacing nulls with zeros

Only replace nulls with 0 if a null truly means "zero quantity" or "zero revenue". If null means "data not collected", replacing with 0 will corrupt your averages. Document your decision.

Merge Queries (Join)¶

Home → Merge Queries — equivalent to a SQL JOIN. Choose the two tables, the matching columns, and the join type (Inner, Left Outer, Right Outer, Full Outer, Anti).

After merging, expand the resulting column to bring in the columns you need from the right table.

Append Queries (Union)¶

Home → Append Queries — equivalent to SQL UNION ALL. Stacks one table on top of another. Useful for combining monthly CSV exports into a single dataset.

The Applied Steps Pane¶

Every transformation you make is recorded as a step in the Applied Steps pane on the right. This is one of Power Query's most powerful features:

Reproducibility: every step is documented and runs in sequence
Editing: click any step to return to that state and modify it
Deletion: remove a bad step without starting over
Auditing: stakeholders can review exactly what was done to the data

For mid-level analysts

Name your steps descriptively. Click the gear icon next to a step name and rename "Replaced Value" to "Standardize US country label". When someone else opens the file six months later, named steps are self-documenting.

For senior analysts

Applied Steps generate M code behind the scenes. View it in View → Advanced Editor. Understanding M lets you parameterize queries (e.g., a dynamic date range filter), build reusable functions, and handle connector-level query folding for performance.

M Language — Brief Overview¶

Power Query transformations generate M (Mashup) code automatically. You do not need to write it, but reading it helps when debugging.

// Example: M code for a simple transformation chain
let
    Source = Csv.Document(File.Contents("C:\data\sales.csv"), [Delimiter=",", Encoding=65001]),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    ChangedTypes = Table.TransformColumnTypes(PromotedHeaders, {
        {"order_date", type date},
        {"revenue", type number},
        {"region", type text}
    }),
    RenamedColumns = Table.RenameColumns(ChangedTypes, {
        {"order_date", "Order Date"},
        {"revenue", "Revenue"},
        {"region", "Region"}
    }),
    RemovedBlanks = Table.SelectRows(RenamedColumns, each [Region] <> null and [Region] <> "")
in
    RemovedBlanks

Key M concepts: - let ... in — like a series of SQL CTEs; each line defines a named step - Source — always the first step, pointing to the data origin - Steps reference the previous step by name - The last line after in is what Power Query returns to the model

Common Connector Gotchas¶

Excel sheets vs named tables

When connecting to Excel, prefer named Tables (Ctrl+T in Excel) over named sheets. Tables refresh correctly when rows are added. Sheet connections sometimes miss new rows added below the original range.

SQL Server — Import vs DirectQuery

Import mode copies data into Power BI's in-memory engine — fast queries, requires scheduled refresh. DirectQuery queries the database live — always fresh, but slower and limited DAX support. Choose Import unless you need real-time data or the dataset is too large (>1GB compressed).

Web connector limitations

The Web connector scrapes HTML tables. It breaks if the webpage changes its structure. Use it for prototyping only, not production reports that stakeholders rely on daily.

Previous: 01-power-bi-interface | Next: 03-data-model-basics