Most reporting problems do not start with charts, formulas, or DAX. They start earlier, when the data is not shaped properly. A table may contain only IDs instead of meaningful details, monthly files may arrive separately, and some reports may come in a wide format that looks good to humans but is difficult for Power BI or Excel to analyse.

This is where Power Query becomes extremely useful. Instead of fixing the same issues manually every month, you can create a repeatable workflow that cleans, combines, reshapes, and prepares your data before it gets to your report.

In this demo, we will use one workbook and follow the same kind of process analysts use in real projects: Merge to add missing business details, Append to combine similar sales tables, Unpivot to fix a wide monthly report, and Pivot to create a clean summary view.

Think of the workflow like this: start with raw sales data → enrich it with lookup details → combine similar records → reshape wide reports → summarise the prepared data.

Demo Dataset Used in This Guide

To keep the walkthrough practical, we will use one Excel workbook with four sheets: Sales, Products, Customers, and Monthly_Wide. Each sheet has a specific role in the demo, so readers can follow the same workbook from start to finish instead of jumping between unrelated examples.

Download the sales dataset → Here

Figure 1: The demo workbook contains four sheets—Sales, Products, Customers, and Monthly_Wide—so each transformation can be demonstrated with the same business scenario.

Sheet	What it contains	Role in the demo
Sales	The main transaction table with TransactionID, Date, CustomerID, ProductID, Quantity, UnitPrice, and Revenue.	This is the starting table. We enrich it with product and customer details, split it into monthly queries for append, and later use it for the pivot summary.
Products	A lookup table with ProductID, ProductName, Category, and Subcategory.	This provides product context. We merge it with Sales so the report can show product names and categories instead of only ProductID values.
Customers	A lookup table with CustomerID, CustomerName, Region, and Segment.	This provides customer context. We merge it with Sales to bring in customer names, segments, and Region, which we later use for the pivot example.
Monthly_Wide	A wide-format table with ProductID and monthly revenue columns such as Jan, Feb, Mar, and Apr.	This is intentionally shaped like a report. We use it to show how Unpivot converts month columns into a cleaner Month-and-Revenue structure.

Why Data Shape Matters Before Analysis

A good report is not built only with attractive visuals. It starts with data that is structured correctly. If your source data is disconnected, spread across multiple files, or arranged like a report instead of a table, your analysis becomes harder to build, refresh, and trust.

Workflow	What problem are we solving?	What Power Query does	What the reader should see
1. Merge	Sales has ProductID and CustomerID, but those IDs are not meaningful enough for reporting.	Match Sales with Products and Customers using the key columns.	Sales becomes richer, with product names, categories, customer names, regions, and segments.
2. Append	Similar sales records may come as separate monthly or regional tables.	Stack the similar queries into one combined table.	One Sales_Append table contains all selected rows.
3. Unpivot	Monthly_Wide stores Jan, Feb, Mar, and Apr as separate columns.	Convert those month columns into rows.	The result becomes ProductID, Month, and Revenue.
4. Pivot	After preparation, we need a simple summary for comparison.	Turn Region values into columns and summarise Revenue.	A compact revenue-by-region summary table.

1. Merge: Enrich Your Main Table with Related Details

Let’s start with Merge because our Sales table is not very useful on its own. It tells us the customer ID and product ID, but it does not tell us the customer name, region, product name, or category. Those details are sitting in the Products and Customers lookup tables. Merge helps us bring that missing context into Sales.

Before: Sales contains transaction values but mostly uses IDs. After: Sales becomes an enriched table with meaningful business fields that readers can filter, group, and analyse.

Figure 2: Before the merge, Sales contains ProductID and CustomerID, which will be used as matching keys to bring in product and customer details.

Demo: Merge Sales with Products, then Customers

1. Select the Sales query.
2. Go to Home and choose Merge Queries or Merge Queries as New.
3. Select Products as the second table.
4. Select ProductID in both tables and use Left Outer as the join kind so all Sales rows are preserved.

Figure 3: Select ProductID in both tables and use a Left Outer join so every Sales row is kept while matching product records are added.

• Click OK, then expand the new Products column. Keep only ProductName, Category, and Subcategory. Do not expand ProductID again because it already exists in Sales.
• Repeat the process with Customers, matching on CustomerID, then expand CustomerName, Region, and Segment.

At this point, Sales has moved from a basic transaction table to a more useful analysis table. You can now see what was sold, who bought it, where the customer belongs, and how each transaction should be grouped for reporting.

Figure 4: After merging Products and Customers, the Sales table now includes ProductName, Category, CustomerName, Region, and Segment.

Use merge when: you need to enrich a table with related information from another table, validate unmatched records, or replace manual lookup work with a repeatable Power Query step.

2. Append: Combine Similar Tables into One Dataset

After enriching Sales, the next common problem is combining similar tables. In real work, sales data may arrive as separate monthly files or separate queries. For this demo, we create filtered queries such as Sales(Jan_Feb), Sales(Mar), and Sales(Apr). They have the same columns, but each contains a different set of rows.

Before: the data is split across separate monthly queries. After: the rows are stacked into one combined Sales_Append table.

Figure 5: The monthly sales queries have the same structure, which makes them suitable for Append.

When you append these queries, Power Query does not add new columns for each month. Instead, it adds the rows together into one longer table. This is the right approach when the tables describe the same type of data and share the same structure.

Demo: Append the monthly sales queries

1. Right-click the Sales query and choose Duplicate or Reference.
2. Rename the new queries as Sales(Jan_Feb), Sales(Mar) and Sales(Apr).
3. Filter the Date column in each query so each one contains only the relevant month.
4. Confirm that all three queries still have the same column names and compatible data types.
5. Go to Home and choose Append Queries as New.
6. Select Three or more tables, add Sales(Jan_Feb), Sales(Mar), and Sales(Apr), then click OK.

• Rename the final query Sales_Append and check the row count to confirm that the rows from all selected queries were combined.

Figure 6: After appending, Sales_Append contains the combined rows from all selected monthly queries.

Use append when: you are combining monthly files, regional datasets, multiple CSV exports, survey responses, or similar tables from different systems.

3. Unpivot: Turn Wide Reports into Analysis-Ready Rows

Now let’s fix a different kind of problem. Some reports are arranged for reading, not analysis. The Monthly_Wide sheet is a good example: each month is stored as a separate column. That may look neat in Excel, but it is not the best structure for filtering, charting, or refreshing over time.

Before: Jan, Feb, Mar, and Apr are separate columns. After: those month names become values in a single Month column, with the corresponding figures stored in Revenue.

Figure 7: Monthly_Wide is in a wide report format, with each month stored as a separate column.

Unpivot changes the structure from wide to long. Instead of creating more month columns, Power Query creates a cleaner table with ProductID, Month, and Revenue. This is easier to analyse and more scalable when new months are added later.

Demo: Unpivot the monthly columns

1. Open the Monthly_Wide query in Power Query.
2. Select the ProductID column because this column should remain fixed.
3. Right-click ProductID and choose Unpivot Other Columns. This converts Jan, Feb, Mar, and Apr into rows.
4. Rename the new Attribute column to Month.
5. Rename the new Value column to Revenue.
6. Set the data types correctly: ProductID as whole number, Month as text, and Revenue as whole number or decimal number.

Figure 8: After unpivoting, the month columns become rows, creating a cleaner ProductID, Month, and Revenue table.

Use unpivot when: similar values are spread across multiple columns, especially months, years, products, survey questions, KPIs, or cost categories.

4. Pivot: Create a Summary View from Prepared Data

Finally, after the data has been prepared, you may want a compact summary for quick comparison. Since the Sales table has already been enriched through Merge, it now contains both Region and Revenue. That means we can pivot Region to compare total revenue across each region.

Before: Region values appear repeatedly down the rows. After: each region becomes a separate column with total revenue summarised underneath.

Figure 9: Before pivoting, keep the fields needed for the summary, such as Region and Revenue.

Demo: Pivot Region to summarise Revenue

Important: before pivoting, remove unnecessary columns and keep only what the summary needs. If extra columns remain, Power Query may group by those columns and produce a more detailed result than expected.

1. Start with the enriched Sales query that already contains Region and Revenue.
2. Keep only the fields needed for the summary, such as Region and Revenue.
3. Select the Region column. This is the column whose unique values will become new column headers.
4. Go to Transform and choose Pivot Column.
5. In the Pivot Column dialog box, set Values Column to Revenue.
6. Under Advanced options, choose Sum as the aggregation method.
7. Click OK, then review the output to confirm that each region appears as a separate column and the values represent total revenue.

Figure 10: After pivoting, each region appears as a column and Revenue is summarised using Sum.

Use pivot when: you need a compact summary table, a cross-tab style output, or a side-by-side comparison across categories.

Quick Decision Guide: Which Transformation Should You Use?

• Use Merge when you need to add related details from another table using a matching key.
• Use Append when you need to combine rows from similar tables into one dataset.
• Use Unpivot when similar values are spread across multiple columns and need to become rows.
• Use Pivot when you need a summary view that turns repeated row values into columns.

Common Mistakes Analysts Should Avoid

• Pivoting with too many columns still in the table: Power Query groups by the remaining columns, so remove unnecessary columns before pivoting if you want a clean summary.
• Forgetting to unpivot report-style exports: Month, year, or metric columns often need to become rows before analysis.
• Merging on messy keys: Extra spaces, inconsistent casing, duplicate IDs, and different data types can lead to missing, duplicated, or unexpected matches.
• Appending tables with inconsistent headers: Power Query aligns columns by name, so spelling differences can create extra columns or null values.
• Ignoring data types after transformation: Always check dates, numbers, text, and currency fields before loading to the model.
• Not checking row counts: Always compare row counts before and after merge or append operations so you can spot unexpected duplicates, missing rows, or null-filled columns.

Pro Tips for Better Power Query Transformations

• Use clear query names such as Sales_Merged, Sales_Append, Products, and Customers.
• Keep transformation steps readable by renaming important steps in Applied Steps.
• Use Append Queries as New or Merge Queries as New when you want to preserve your original queries.
• Before merging, confirm that the matching key columns have the same data type.
• Use Unpivot Other Columns when future month columns may be added to the source file.
• Check row counts before and after merge or append operations so you can spot unexpected duplicates, missing rows, or null-filled columns.

Final Thoughts

Merge, Append, Unpivot, and Pivot are more than Power Query buttons. They are practical data preparation skills. Merge helps you enrich your data, Append helps you combine similar records, Unpivot helps you fix wide report layouts, and Pivot helps you create summary views after the data has been prepared.

The next time a dataset looks difficult to analyse, do not rush straight into formulas, visuals, or DAX. First, ask what shape the data needs to be in. Should you enrich it with a merge, combine it with an append, fix its layout with unpivot, or summarise it with pivot? That simple workflow can turn messy source data into a clean, repeatable reporting process.

Ready to build stronger data skills?

At UrBizEdge, we help professionals and teams turn raw data into clean, reliable, and business-ready insights using Excel, Power BI, Power Query, SQL, and analytics best practices.

If you have followed along, try recreating this workflow using your own dataset—it’s the fastest way to build confidence.

If you want to stop struggling with messy data and start building solutions that refresh correctly and scale with your business, reach out today via info@urbizedge.com.