Data rarely comes in one file. Monthly sales arrive as separate sheets, and customer details live in another system. Power Query, in both Power BI and Excel, combines them with two operations that beginners often confuse: Append and Merge.
Append: stack tables on top of each other
Use Append when tables have the same columns and you want more rows, for example January, February and March sales files. In Power Query choose Home, then Append Queries, and pick the tables (or three or more).
Columns are matched by name, not position. If one file calls a column “Amount” and another “amount “, you get two separate columns with blanks. Clean and rename columns before appending.
For many files in a folder, use Get Data from Folder and Combine Files instead; new monthly files are then picked up automatically on refresh.
Merge: join tables side by side
Use Merge when you want more columns from a related table, such as adding customer city to each order using Customer ID. Choose Home, then Merge Queries, select the matching column in both tables, and pick a join kind. After merging, expand the new column to choose which fields to bring in.
Join kinds
- Left outer: all rows from the first table, matched details where found. The most common choice.
- Inner: only rows that match in both.
- Left anti: rows in the first table with no match, ideal for finding orders with missing customers.
- Full outer: everything from both tables.
Common mistakes
- Matching columns with different data types (text “101” vs number 101), so nothing matches.
- Extra spaces or different capitalisation in keys; use Trim and Lowercase first.
- Duplicate keys in the lookup table, which multiply rows after merging. Check row counts before and after.
- Merging in Power Query when a model relationship in Power BI would be simpler and lighter.
Related: Power Query basics, SQL JOINs explained and Power BI relationships.
Power Query and Power BI are taught with real datasets in our 5-month Data Analytics course in Bangalore.
Book a free demo class at our BTM Layout centre, and ask a counsellor for batch details.
