"The report was pulling data from four different tables. Sales came in one shape, returns in another, products in a third. Getting them to talk to each other — that is what this lesson is about." — Power BI Advanced batch, Week 2 practicals
Most enterprise datasets do not arrive in a single perfect table. Customer data lives in CRM. Sales data lives in ERP. Products live in a spreadsheet. Power Query is where you bring them together — merging, appending, pivoting, and cleaning — until the data model is ready for DAX and visuals.
You have three jigsaw boxes: left half of the picture, right half, and some duplicate pieces. Before you see the full image you need to combine matching pieces (Merge), stack additional rows (Append), flip wrongly oriented pieces (Unpivot), and discard duplicates. Power Query's structural operations do exactly this — and they repeat automatically every time the data refreshes.
The 6 Merge (Join) Types
When you merge two queries (Home → Merge Queries), you choose a Join Kind that determines which rows from each table appear in the result — identical to SQL JOIN types.
| Join Kind | Rows Kept | SQL Equivalent | Enterprise Use Case |
|---|---|---|---|
| Left Outer | All from left; matching from right (null if no match) | LEFT JOIN | All customers + their orders (customers with no orders still appear) |
| Right Outer | Matching from left; all from right (null if no match) | RIGHT JOIN | All products + any sales (products with zero sales still appear) |
| Full Outer | All rows from both tables | FULL OUTER JOIN | All employees AND all departments — even unmatched on either side |
| Inner | Only rows with matches in both tables | INNER JOIN | Only customers who have placed at least one order |
| Left Anti | Only rows from left with NO match in right | LEFT JOIN WHERE right IS NULL | Customers who have NEVER placed an order — find churned/lapsed accounts |
| Right Anti | Only rows from right with NO match in left | RIGHT JOIN WHERE left IS NULL | Products in the catalogue that have never appeared in any order |
Profile Data in Power BI: Know Your Data Before You Transform It
Before transforming data, enable the View tab profiling options in Power Query Editor to understand what you are working with:
- Column Quality (View tab): Shows percentage of valid, error, and empty values per column. A column that is 40% empty is a signal — decide whether to fill, remove, or flag those rows.
- Column Distribution (View tab): Shows distinct vs. unique counts. If "CustomerID" shows 900 distinct but 800 unique, there are 100 duplicate IDs — a data quality issue that corrupts COUNT DISTINCT measures.
- Column Profile (View tab): Detailed statistics including min, max, average, null count, error count, and value histogram. Critical for numeric columns before writing DAX measures.
Key Shaping Operations
Once you understand the data, use these Power Query operations to reshape it for your model:
- Remove Columns: Eliminate columns not needed for analysis — reduces model size and simplifies the field list.
- Rename Columns: Use clear, consistent names. "CustID" becomes "CustomerID" — critical for readable DAX and self-documenting reports.
- Filter Rows: Remove rows outside your analytical scope — test orders, cancelled-and-reversed transactions, future-dated records.
- Split Columns: Divide a combined address column into Street, City, PostCode for independent filtering.
- Create Dimension Tables: Extract repeating descriptive attributes (product name, customer city, region) into separate dimension tables. This is the foundation of a star schema.
Data Types — Why They Matter
Always verify column data types after loading. Power BI auto-detect is frequently wrong for date columns and numeric IDs stored as text. Wrong types cause: mathematical operations to fail on text columns, incorrect date sorting, DAX time intelligence functions to return blank, and relationship joins to fail between mismatched types.
By mastering these structural operations in Power Query, you build a data model that is easier to work with, performs better, and produces accurate measures in DAX.
3 Structural Mistakes That Break Data Models
- Using Left Anti when you want Left Outer: If your goal is "enrich every sales row with product name", use Left Outer — all sales rows are kept, product attributes are added. Left Anti gives you only rows with NO product match — producing empty visuals with zero totals.
- Profiling only the first 1,000 rows then assuming data is clean: Power Query defaults to sampling the top 1,000 rows for performance. In a large fact table, edge-case nulls and type errors typically live below row 1,000. Always switch to full-dataset profiling before declaring a transformation complete.
- Appending queries with mismatched column names: If one query has "SaleDate" and another has "SalesDate", append creates two separate null-filled columns. Standardise column names with a Rename Columns step in each source query before appending.
Quick Knowledge Check
Q1. You want all customers with their order details where available, and null for order columns where the customer has no orders. Which join type should you use?
Show Answer
Left Outer join. Keeps all rows from the Customers (left) table and brings in matching order data. Customers with no orders appear with null values in order columns. Inner join would exclude them entirely.
Q2. A monthly report has columns: Store, Jan, Feb, Mar … Dec. You need time intelligence charts. Which Power Query operation is required first?
- A) Pivot the month columns
- B) Unpivot the month columns to get Store | Month | Sales rows
- C) Append 12 separate queries
- D) Merge with a Date table
Show Answer
B — Unpivot. Converting the 12 month columns to Store | Month | Sales (long format) is required before the table can relate to a Date table and support DAX time intelligence functions.
Q3. Column Distribution shows "CustomerID: 900 distinct, 800 unique." What does this indicate?
Show Answer
100 duplicate CustomerID values exist. "Distinct" counts values appearing at least once; "Unique" counts values appearing exactly once. The gap of 100 means those IDs appear in more than one row — a data quality issue that will affect COUNT DISTINCT measures and relationship integrity.
- Merge = JOIN (adds columns), Append = UNION (adds rows) — using the wrong one produces silently incorrect results.
- Left Outer is the most common enterprise merge — keeps all fact rows, enriches with dimension attributes on a matching key.
- Left Anti finds orphans — rows that exist in the left table but have no match in the right. Essential for data quality audits.
- Profile the full dataset, not just 1,000 rows — Power Query defaults to top-1,000 sampling. Switch to full-dataset profiling to catch all edge cases.
- Standardise column names before appending — mismatched names create parallel null columns. Rename in each source query first.