Transforming and Cleaning Data in Power Query: Merging Queries, Grouping Rows, and Pivoting Columns for a Clean Data Model

Introduction

You've connected to your data sources, pulled in a few tables, and you're staring at a mess. Your sales data is in one query, your product catalog is in another, and your regional targets are stored in a wide table where every month is its own column. Nothing joins cleanly, nothing summarizes correctly, and your report hasn't even started yet.

This is the moment Power Query was built for. Not just for removing blank rows or changing data types — though it does those too — but for fundamentally reshaping how your data is structured before it ever reaches your model. Merging two queries into a single denormalized table, rolling up thousands of transaction rows into grouped summaries, and collapsing wide pivot formats into clean, tall analytical tables: these are the operations that separate a working data model from one that actually performs and scales.

By the end of this lesson, you'll have a confident, practical command of three of Power Query's most powerful transformation patterns. We'll work through a realistic business scenario involving sales transactions, a product catalog, and regional sales targets — the kind of data you'll encounter in almost any commercial environment.

What you'll learn:

• How to merge two queries using different join types, and when to choose each one
• How to group rows to create aggregated summary tables from raw transactional data
• How to unpivot wide columnar data into a clean analytical format
• How to pivot data when you need to turn row-level categories into columns
• How the M language records each step and how to read and lightly edit those steps yourself

---

Prerequisites

This lesson picks up where Mastering Power Query in Power BI: Transforming, Cleaning, and Shaping Data Before It Hits Your Model leaves off. You should already be comfortable loading data into Power Query, applying basic column-level transformations (renaming, changing types, filtering), and understanding what the Applied Steps pane does.

If you haven't loaded data into Power BI yet, start with Connecting to Data Sources in Power BI Desktop: Excel, CSV, SQL, and Web — A Complete Beginner's Guide first, then come back here.

---

The Scenario: A Messy Sales Dataset

Throughout this lesson we'll work with three tables that represent a typical mid-size retail analytics setup:

SalesTransactions — one row per individual sale, ~50,000 rows:

OrderID	OrderDate	CustomerID	ProductID	Quantity	UnitPrice	RegionCode
1001	2024-01-03	C-884	P-012	3	49.99	NW
1002	2024-01-03	C-221	P-047	1	129.00	SE

ProductCatalog — one row per product:

ProductID	ProductName	Category	SubCategory	CostPrice
P-012	Trail Running Shoes	Footwear	Trail	22.00
P-047	GPS Watch	Electronics	Wearables	58.00

RegionalTargets — wide format, one row per region, one column per month:

RegionCode	RegionName	Jan_2024	Feb_2024	Mar_2024	...	Dec_2024
NW	Northwest	85000	90000	92000	...	110000
SE	Southeast	72000	76000	80000	...	98000

The goal: merge product details into the sales table, create a grouped monthly summary by region and category, and unpivot the RegionalTargets table so you can compare actuals against targets in a single DAX measure.

---

Merging Queries: Combining Tables Without Losing Control

What a Merge Actually Does

A merge in Power Query is a join operation. You're telling Power Query: "For each row in Table A, find matching rows in Table B using these key columns, and bring columns from Table B alongside." This is conceptually identical to a SQL JOIN, and the join type terminology maps directly.

The reason you do this in Power Query rather than in DAX (as a calculated column or RELATED function) comes down to performance and model design. When you need a column from another table to be used as a filter or slicer, it should live in a dimension table and join through a relationship. But when you need to create a single flat table that's already enriched — say, for a specific reporting query or an export — merging in Power Query is the right move.

Performing the Merge: SalesTransactions + ProductCatalog

Open Power Query Editor (Home tab → Transform Data). Select the SalesTransactions query in the left panel.

Go to Home → Merge Queries (use the dropdown arrow to choose "Merge Queries as New" if you want to preserve the original — a good habit while learning).

In the Merge dialog:

1. The top table defaults to SalesTransactions. Click the ProductID column header to select it as your join key.
2. In the second dropdown, choose ProductCatalog.
3. Click the ProductID column header in the lower preview to select it as the matching key.
4. Set the Join Kind to Left Outer (the default).

Click OK. Power Query adds a new column called ProductCatalog with the type "Table" — each cell contains a nested table of matching rows. To extract the columns you want, click the expand icon (two arrows) in the column header, then select the fields you want to bring in: ProductName, Category, SubCategory, and CostPrice. Uncheck "Use original column name as prefix" if you want cleaner column names.

The M code generated looks like this:

= Table.NestedJoin(
    SalesTransactions,
    {"ProductID"},
    ProductCatalog,
    {"ProductID"},
    "ProductCatalog",
    JoinKind.LeftOuter
)

After expanding:

= Table.ExpandTableColumn(
    #"Merged Queries",
    "ProductCatalog",
    {"ProductName", "Category", "SubCategory", "CostPrice"},
    {"ProductName", "Category", "SubCategory", "CostPrice"}
)

You now have a single, enriched SalesTransactions table with product details attached to every row.

Choosing the Right Join Type

Power Query supports six join types, and picking the wrong one silently corrupts your data. Here's the practical guide:

Join Type	What it keeps	When to use it
Left Outer	All rows from left; matching from right	Most common — keep all transactions, bring in lookup values
Right Outer	All rows from right; matching from left	Rare; mostly useful for checking what's in the lookup that's NOT in the fact
Full Outer	All rows from both	Auditing mismatches between two systems
Inner	Only matching rows from both	When unmatched rows are errors you want to exclude
Left Anti	Rows in left that have NO match in right	Finding orphan records — transactions with invalid ProductIDs
Right Anti	Rows in right that have NO match in left	Finding unused catalog entries

The Left Anti join deserves special attention as a data quality tool. Create a "Merge Queries as New" using Left Anti to find all transactions with ProductIDs that aren't in your catalog. If that result isn't empty, you have a referential integrity problem worth investigating before you build your model.

Merging on Multiple Keys

Sometimes a single column isn't enough to uniquely identify a match. Imagine your sales data has both a ProductID and a WarehouseID, and your pricing table has different prices per product per warehouse.

To merge on two columns, hold Ctrl while clicking both column headers in each table. Power Query will show "1" and "2" labels indicating the composite key order. The join order matters — make sure the key order matches between both tables.

= Table.NestedJoin(
    SalesTransactions,
    {"ProductID", "WarehouseID"},
    WarehousePricing,
    {"ProductID", "WarehouseID"},
    "WarehousePricing",
    JoinKind.LeftOuter
)

---

Grouping Rows: From Transactions to Summaries

Why Grouping Belongs in Power Query

You could always write a DAX measure to sum sales by month and region. In fact, for interactive reports with slicers and filters, that's exactly what you should do. But there are scenarios where you want a pre-aggregated table in your model:

• A calendar-granularity summary table to sit alongside your fact table for star schema design
• An aggregation table for performance optimization on large datasets
• A flattened export that a downstream system can consume

Performing a Group By

With the enriched SalesTransactions query selected in Power Query, go to Transform → Group By.

Switch the dialog to Advanced mode immediately. Basic mode only lets you group by one column; Advanced lets you define multiple group-by columns and multiple aggregations in a single step.

Configure it as follows:

Group by these columns:

• RegionCode
• Category
• OrderDate (you'll want to truncate this to month first — we'll cover that)

Add these aggregations:

• Total Revenue: Sum of a custom column [Quantity] * [UnitPrice]
• Total Cost: Sum of a custom column [Quantity] * [CostPrice]
• Order Count: Count Rows

Wait — Power Query's Group By dialog doesn't let you aggregate a formula directly. You have to first create an explicit Revenue column, then group on it.

Before opening Group By, add a custom column (Add Column → Custom Column):

= [Quantity] * [UnitPrice]

Name it Revenue. Add another for Cost:

= [Quantity] * [CostPrice]

Also, truncate OrderDate to month granularity using Transform → Date → Month → Start of Month (this replaces the daily date with the first day of the month, effectively giving you a "YearMonth" grouping key without losing the date type).

Now open Group By (Advanced mode):

Group by: RegionCode, Category, MonthStart
Aggregations:
  TotalRevenue  = Sum of Revenue
  TotalCost     = Sum of Cost
  OrderCount    = Count Rows

The M generated:

= Table.Group(
    #"Added Revenue Column",
    {"RegionCode", "Category", "MonthStart"},
    {
        {"TotalRevenue", each List.Sum([Revenue]), type number},
        {"TotalCost", each List.Sum([Cost]), type number},
        {"OrderCount", each Table.RowCount(_), type number}
    }
)

The each Table.RowCount(_) idiom is worth understanding. The underscore _ represents the sub-table of rows that belong to each group. List.Sum([Revenue]) pulls the Revenue column from that sub-table and sums it. This pattern becomes useful when you want to do more complex per-group calculations.

Creating a Calculated Margin Column After Grouping

Once the Group By step runs, you can add a custom column directly on the aggregated result:

= [TotalRevenue] - [TotalCost]

Name it GrossProfit, then add another:

= if [TotalRevenue] = 0 then null else ([TotalRevenue] - [TotalCost]) / [TotalRevenue]

Name it GrossMarginPct. The null guard is important — dividing by zero in Power Query returns an error, not null, and errors will propagate through your model in unexpected ways.

---

Unpivoting Columns: Turning Wide Data Tall

The Wide Data Problem

The RegionalTargets table is the most common pain you'll encounter in real-world data work. Someone built a planning spreadsheet where each month is a column. It's human-readable, but analytically useless:

• You can't filter by month with a slicer
• You can't join it to a calendar table
• You can't compare targets to actuals without writing twelve separate measures

The fix is unpivoting: rotating those month columns into rows, so you end up with one row per region per month, with a "Month" column and a "TargetAmount" column.

Performing Unpivot

Load the RegionalTargets table into Power Query. You'll see RegionCode, RegionName, and then twelve month columns (Jan_2024 through Dec_2024).

Select RegionCode and RegionName (the columns you want to keep as identifiers, not rotate). Then, right-click and choose Unpivot Other Columns.

This tells Power Query: "Keep these selected columns as-is. Everything else, rotate into rows."

The result:

RegionCode	RegionName	Attribute	Value
NW	Northwest	Jan_2024	85000
NW	Northwest	Feb_2024	90000
SE	Southeast	Jan_2024	72000

Rename Attribute to MonthKey and Value to TargetAmount.

Now parse the MonthKey into an actual date. Add a custom column:

= Date.FromText(
    Text.Replace([MonthKey], "_", " 01 "),
    [Format = "MMM yyyy dd", Culture = "en-US"]
)

Actually, a more robust approach is to use a manual mapping or split the column. Let's split:

1. Split MonthKey by the delimiter _ → produces two columns: month abbreviation and year
2. Add a custom column: = Date.From(DateTime.From(Text.Combine({[MonthKey.2], [MonthKey.1], "01"}, "-")))

An even cleaner approach that works reliably across locales:

= Date.FromText([MonthKey.2] & "-" & 
    Text.PadStart(
        Text.From(Date.Month(DateTime.FromText("01 " & [MonthKey.1] & " 2000", [Format="dd MMM yyyy", Culture="en-US"]))),
        2,
        "0"
    ) & "-01"
)

A simpler and equally valid approach: create a small lookup table mapping "Jan_2024" → a proper date, then merge it in. For production ETL with many files, that's often more maintainable than parsing logic.

Once you have a proper date column, rename it TargetMonth and set its data type to Date. Now your RegionalTargets table is analytically clean: it can join to a calendar table via TargetMonth, and you can compare TargetAmount to your actual revenue from the grouped SalesTransactions table using a simple DAX measure.

When to Use "Unpivot Columns" vs "Unpivot Other Columns"

Power Query offers three variants in the right-click menu:

• Unpivot Columns: Unpivots exactly the columns you've selected. If new columns appear in the source next month, they won't be unpivoted — use this when your column list is static and known.
• Unpivot Other Columns: Unpivots everything except the columns you selected. New columns in the source are automatically unpivoted — use this for dynamic month/date layouts where new columns appear over time.
• Unpivot Only Selected Columns: Identical to the first option but clarifies intent. Stick with "Unpivot Other Columns" for most planning spreadsheet scenarios.

---

Pivoting Columns: The Reverse Operation

When You Need to Go Wide

Pivoting is the reverse: you take a column of category values and turn each unique value into its own column, with another column providing the aggregated values. This is less common in analytical modeling but comes up in two real scenarios:

1. You need a crosstab-style output for a specific visualization or export
2. Your data source produces a key-value format (like an EAV database schema) and you need to normalize it into columns

Pivoting in Practice

Suppose after grouping, you have a table with RegionCode, Category, and TotalRevenue. You want to pivot it so each Category becomes a column, making it easier to compare categories side by side in a matrix visual.

Select the Category column, then go to Transform → Pivot Column.

In the dialog:

• Values Column: TotalRevenue
• Aggregate Value Function: Sum (important — if your data already has one row per RegionCode/Category, Don't Aggregate also works)

The result:

RegionCode	Electronics	Footwear	Apparel
NW	142000	87000	63000
SE	98000	71000	55000

The M for a pivot:

= Table.Pivot(
    #"Grouped Rows",
    List.Distinct(#"Grouped Rows"[Category]),
    "Category",
    "TotalRevenue",
    List.Sum
)

List.Distinct dynamically captures whatever values exist in the Category column at refresh time, which is more robust than hardcoding category names.

---

Reading and Editing M Code in the Formula Bar

Every UI action in Power Query generates M code. You don't have to write M from scratch, but you should be able to read it and make small edits — because sometimes the UI doesn't expose what you need.

Click any step in the Applied Steps pane and look at the formula bar. Each step is a function call that takes the previous step's result as its first argument. The whole query is a let ... in expression:

let
    Source = Excel.Workbook(File.Contents("C:\Data\Sales.xlsx"), null, true),
    SalesSheet = Source{[Item="Sales",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(SalesSheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(
        #"Promoted Headers",
        {{"OrderID", Int64.Type}, {"OrderDate", type date}, {"Quantity", Int64.Type}}
    ),
    #"Merged Queries" = Table.NestedJoin(
        #"Changed Type",
        {"ProductID"},
        ProductCatalog,
        {"ProductID"},
        "ProductCatalog",
        JoinKind.LeftOuter
    ),
    #"Expanded ProductCatalog" = Table.ExpandTableColumn(
        #"Merged Queries",
        "ProductCatalog",
        {"ProductName", "Category", "SubCategory", "CostPrice"},
        {"ProductName", "Category", "SubCategory", "CostPrice"}
    )
in
    #"Expanded ProductCatalog"

Each step references the previous one by name. This chain structure means you can insert, modify, or reorder steps by editing the M directly — but be careful: changing a step name breaks any subsequent step that references it.

One practical edit you'll make regularly: changing a hardcoded file path to a parameter. Instead of File.Contents("C:\Data\Sales.xlsx"), replace the path with a reference to a Power Query parameter:

Source = Excel.Workbook(File.Contents(FilePath), null, true)

Where FilePath is a text parameter you've defined via Manage Parameters. This makes your queries portable without changing the underlying M logic.

---

Hands-On Exercise

Work through this complete scenario using the three tables described above. If you don't have the actual files, create small versions in Excel with 10-20 rows each.

Step 1: Enrich SalesTransactions

• Load SalesTransactions and ProductCatalog into Power Query
• Merge ProductCatalog into SalesTransactions (Left Outer, on ProductID)
• Expand to bring in ProductName, Category, SubCategory, CostPrice
• Add custom columns: Revenue = Quantity × UnitPrice, Cost = Quantity × CostPrice

Step 2: Create a Monthly Summary Table

• Duplicate the enriched SalesTransactions query (right-click → Duplicate)
• On the duplicate, add a MonthStart column using Transform → Date → Month → Start of Month on the OrderDate column
• Open Group By (Advanced), group by RegionCode, Category, MonthStart
• Aggregate: TotalRevenue (Sum of Revenue), TotalCost (Sum of Cost), OrderCount (Count Rows)
• Add GrossProfit and GrossMarginPct custom columns
• Name this query MonthlySalesSummary

Step 3: Unpivot RegionalTargets

• Load RegionalTargets into Power Query
• Select RegionCode and RegionName, right-click → Unpivot Other Columns
• Rename Attribute → MonthKey, Value → TargetAmount
• Parse MonthKey into a proper Date column named TargetMonth
• Change TargetAmount to Decimal Number type

Step 4: Validate your work

• Check that MonthlySalesSummary has exactly one row per RegionCode/Category/MonthStart combination
• Check that RegionalTargets (unpivoted) has exactly 24 rows (2 regions × 12 months)
• In Power BI's Data view, spot-check a few numbers by manually summing rows from the original table

Step 5: Close and Apply, then review relationships

• Close Power Query and let the model load
• In the Model view, check whether Power BI auto-detected any relationships
• You should be able to draw a relationship between MonthlySalesSummary[MonthStart] and RegionalTargets[TargetMonth], and between both tables and a Date dimension — see Power BI Data Modeling: Master Star Schema Design and Relationship Optimization for how to build that properly

---

Common Mistakes & Troubleshooting

Merge produces more rows than expected

Symptom: After merging, your SalesTransactions table has more rows than before the merge.

Cause: The key column in the lookup table (ProductCatalog) isn't unique. There are multiple rows with the same ProductID, and the join creates one output row for every match — a many-to-many explosion.

Fix: Before merging, verify uniqueness. Add a step to group ProductCatalog by ProductID and count rows. If any count > 1, deduplicate using Remove Duplicates on ProductID, or investigate why duplicates exist. Alternatively, filter to the specific record you want (e.g., the most recent one) before merging.

Group By produces a single row

Symptom: After Group By, you have one row instead of many.

Cause: The columns you grouped by don't exist by those names — usually because a Rename step changed the column name after you configured the Group By, but because steps execute in order, the Group By step is referencing the old name.

Fix: Check the Applied Steps order. Group By should come after any Rename steps that affect the group-by columns. Reorder steps by dragging them in the Applied Steps pane, or edit the M to fix the referenced column names.

Unpivot creates unexpected "null" rows

Symptom: After unpivoting, some rows have null in the Value column.

Cause: The source spreadsheet had empty cells for some month/region combinations. Power Query unpivots those too, creating null-value rows.

Fix: After unpivoting, filter out rows where TargetAmount is null. Or, if nulls should be treated as zero, use Replace Values to replace null with 0 before filtering.

Pivot produces errors for some values

Symptom: After pivoting, some cells show "Error" instead of a number.

Cause: There are multiple rows for the same combination of group-by columns and the pivot value column — you haven't aggregated, or you chose "Don't Aggregate" when aggregation was needed.

Fix: Reopen the Pivot Column step (click its gear icon in Applied Steps) and set the Aggregate Value Function to Sum (or the appropriate function). Or go back and ensure your data is pre-aggregated before pivoting.

Date parsing fails after unpivoting

Symptom: Your parsed date column shows errors for some rows.

Cause: The MonthKey values aren't all in the same format. Spot-check the distinct values in the Attribute column before you start parsing. Look for typos ("Jne_2024"), trailing spaces, or merged cells from Excel that create unexpected strings.

Fix: Use the Column Distribution and Column Quality indicators (View tab → Column Quality, Column Distribution) to spot anomalies before parsing. You can filter to just the error rows after parsing to see which source strings are causing problems.

---

Summary & Next Steps

You now have three powerful transformation tools in your Power Query toolkit. Merging queries lets you enrich tables with attributes from lookups without duplicating data in your model. Grouping rows lets you build summary tables at the right grain for specific analytical needs. Unpivoting converts the wide spreadsheet formats that humans love into the tall analytical formats that Power BI loves — and pivoting reverses the process when you need it.

The deeper principle connecting all three operations is grain management: understanding the level of detail at which each table should exist, and deliberately shaping your data to that grain before it reaches the model. A merge keeps the same grain but adds columns. A group by reduces grain (fewer, denser rows). Unpivoting typically increases the row count while reducing the column count. When you think in terms of grain, you'll know instinctively which operation a given dataset needs.

From here, the natural next step is putting these clean tables to work. If you're building measures on top of the MonthlySalesSummary table, Introduction to DAX: Writing Your First Calculated Columns and Measures in Power BI will show you how to write the comparisons (Actuals vs Targets) that these clean tables now make straightforward. And when you're ready to build the visuals that expose these numbers to your users, Building Interactive Visuals: Advanced Charts, Maps, and Custom Formatting in Power BI covers how to wire up slicers, cross-filters, and chart types that take full advantage of a properly structured model.

The messy data you started with is now clean, shaped, and ready. That's the whole job of Power Query — and now you can do it well.