Tutorials›Excel›Power Query Transforms
🟢 Free Demo
Excel TutorialTopic 13 of 31

Power Query Transforms

Advanced data shaping in Power Query — merge, append, unpivot, group, and split columns.

✅ What You Will Learn

›How to merge two queries (equivalent of a JOIN in SQL)
›How to append tables from multiple files into one
›How to unpivot columns to convert wide tables to long format
›How to group rows and aggregate with Group By
›How to split a combined column into parts

Once you understand the basics of Power Query, the advanced transforms let you do the kind of data reshaping that previously required SQL or manual work. Merge combines two tables based on a matching column — equivalent to VLOOKUP but more powerful. Append stacks tables from multiple files — useful for combining 12 monthly files into one annual dataset. Unpivot converts wide data (months as columns) into long data (all months in one column) — the format required by Pivot Tables and charts.

These transforms run as recorded steps, so every operation is transparent, editable, and automatically re-applied on refresh. The visual interface makes these operations accessible without writing any code — though the underlying M language is visible in the Advanced Editor if you want to customise it.

Examples

Example 1Merge Queries — join orders with a product master table
Steps:
1. Have two queries: "Orders" (with ProductID) and "Products" (master table)
2. In the Orders query: Home tab → Merge Queries
3. Select the ProductID column in Orders
4. Select the Products query, then its ProductID column
5. Choose Join Kind: Left Outer (all orders, matching product data)
6. Click OK → a new column "Products" appears
7. Click the expand icon → select columns to bring in (Name, Category, Price)
OUTPUT
Orders table now has Name, Category, and Price columns from the Products table — no VLOOKUP needed, and it refreshes automatically.
💡

Merge Queries is Power Query equivalent of SQL LEFT JOIN. Left Outer keeps all rows from the left table and adds matching data from the right.

Example 2Unpivot — convert month columns to rows
Before unpivot:
Product  | Jan    | Feb    | Mar
Laptop   | 45000  | 38000  | 52000
Mobile   | 18000  | 22000  | 19500

Steps:
1. Select Jan, Feb, Mar columns
2. Transform tab → Unpivot Columns

After unpivot:
Product  | Attribute | Value
Laptop   | Jan       | 45000
Laptop   | Feb       | 38000
Laptop   | Mar       | 52000
Mobile   | Jan       | 18000
💡

Long format (one row per observation) is what Pivot Tables, Power BI, and most analysis tools require. Unpivot is the standard way to convert wide reports into analysis-ready data.

📌 Key Points to Remember

  • ✓Merge = JOIN (combine columns from two tables). Append = UNION (stack rows from multiple tables).
  • ✓Unpivot converts column headers into row values — the opposite of a Pivot Table
  • ✓Group By aggregates rows — Sum, Count, Average, Min, Max by category
  • ✓Split Column (by delimiter or by position) replaces complex LEFT/MID/RIGHT formulas
  • ✓All transforms are steps — delete any step to undo that transformation without starting over

🏢 Real-World Application

An HR team receives 12 separate monthly headcount files (one per month, same structure). Using Power Query Append, all 12 files are combined into one annual dataset automatically — and adding the 13th month just requires refreshing. Finance teams use Unpivot to convert budget vs. actual comparison tables (months as columns) into the long format needed for variance analysis charts.

⚠️ Common Mistakes to Avoid

WRONGMerging on columns with mismatched data types (e.g. text ID vs number ID)
FIXIn Power Query, right-click the key column in each table and set both to the same type (both Text or both Number) before merging.
WRONGUsing Append from the wrong starting query — extra rows appear in the wrong table
FIXAlways start Append from the table that will be the "primary" table. Use Append Queries As New to create a separate combined query without modifying either original.
WRONGForgetting to rename the generic "Attribute" and "Value" columns after Unpivot
FIXAfter Unpivot, double-click the "Attribute" and "Value" column headers to rename them to meaningful names like "Month" and "Sales Amount".
✏️Test Yourself

You have a table with columns: Product, Jan, Feb, Mar, Apr. After Unpivoting the month columns, how many rows will the result have if the original table had 5 product rows?

❓ Frequently Asked Questions

What is the difference between Merge and Append in Power Query?

Merge combines two tables horizontally — adding columns from one table to another based on a matching key (like VLOOKUP or SQL JOIN). Append combines two tables vertically — stacking rows on top of each other (like SQL UNION ALL).

Can Power Query combine all files in a folder automatically?

Yes — Get Data → From Folder → select the folder. Power Query loads all files, applies the same transformation steps to each, and appends them into one table. New files added to the folder are included on the next Refresh.

Is there a limit to how many tables I can Append?

No practical limit. Use Append Queries → Append More Tables to combine three or more tables in one operation. Large appends may slow down refresh time depending on total row count.

✏️ Practice Exercise

Create two separate Excel tables: one with Order data (OrderID, ProductID, Amount) and one with Product master data (ProductID, ProductName, Category, Price). Load both into Power Query. Use Merge to join them on ProductID. Then create a separate query that Unpivots a monthly sales table (products as rows, months as columns) into long format. Load both results to separate sheets.

← PreviousPower Query BasicsNext →Macros Introduction
🎓 Level Up Faster

Learn Excel with Live Trainer Guidance

These tutorials give you the foundations. Our live Excel course at EVIKA Academy, Noida teaches you to build real dashboards on actual business data — with a trainer who uses Excel professionally every day.