How to Merge Tables in Power BI Power Query (Left Outer vs Inner Join)

By Ravindra Bagale · Last updated: 16 October 2026

Practice files: blinkit-products.csv (9 products) and the 10-row sales query from How to Stop Power BI Changing Data Types Automatically and Fix Slow Loading. Continue with the products query from How to Clean Text Columns in Power Query with Split, Trim, Clean and Lowercase. For the practice task, use the 401-row sales built from blinkit-orders-2023.csv, blinkit-orders-2024.csv and blinkit-orders-2025.csv.

Made-up data: fictional orders, products and brands, for practice only. Amounts are in ₹, sizes in cm.

1. The problem

sales has 10 orders with product_id P101, P102, P103, P104, P105 and P109. The cleaned products table has 9 products, and P109 is not in it (a new product that has not been added to the product list yet).

You want a chart of revenue by brand and a column that tells the delivery team how big each item is. Neither is possible until the two tables are joined, and if you join the wrong way, order BLK4003 (P109, ₹75) disappears from your totals without any warning.

2. The idea in simple words

A merge looks up each sales row's product_id in the product table and brings back that product's columns. The join kind decides what happens when there is no match:

• Left Outer: keep every row of the first table (sales). If there is no match, the new columns are null.
• Inner: keep only rows that match in both tables. Unmatched sales rows are dropped.

For a sales table, Left Outer is the safe default: every order stays, and the nulls show you which products are missing from the product list.

3. The data

sales (the columns you need here):

Sizes in the product table and the volume you should get (length × depth × width, cm³):

Revenue by brand after a Left Outer merge, by hand:

4. Step by step

Finish the product table: volume

1. In products, select size (L x D x W cm). Home › Split Column › By Delimiter › Custom x (space, x, space) › Each occurrence of the delimiter › Quote Character None › OK.
2. Rename the three new columns length, depth, width. For P104, width is null.
3. Set all three to Decimal Number.
4. Add Column › Custom Column. New column name volume. Formula: [length] * [depth] * [width]. OK.
5. Set volume to Decimal Number. P104 shows null.
6. Ctrl+click length, depth and width › right-click › Remove Columns. You only need volume.
7. Rename product_id_ to product_id.

Merge into sales

8. Select the sales query. Home › Merge Queries (the plain one, which adds a step to sales).
9. Top table sales: click the product_id header. Bottom drop-down: products, then click its product_id header.
10. Join Kind: Left Outer (all from first, matching from second). Read the message at the bottom: The selection matches 9 of 10 rows from the first table. OK.
11. A new column products appears with Table in each cell. Click the expand icon (two arrows) in its header.
12. Untick product_id (sales already has it) and untick Use original column name as prefix. Keep product, brand, category, sub_category and volume. OK.
13. Right-click products in the Queries pane › untick Enable load: its columns now live inside sales.
14. Home › Close & Apply. Build a bar chart: brand on the Y-axis, revenue on the X-axis.

5. Your result should look like this

sales still has 10 rows and now has product, brand, category, sub_category and volume. BLK4003 shows null in all five.

The bar chart shows: mauli sweets 1,998 · krishna dairy 1,350 · sahyadri foods 720 · godavari mills 270 · unknown brand 120 · (Blank) 75. Total revenue is still ₹4,533.

Revenue by category: sweets 1,998 · dairy 1,350 · snacks 840 · staples 270 · (Blank) 75.

6. What happened

• Split by " x " gave three numbers per product; P104 had only two parts, so width became null.
• In the volume formula any null makes the whole result null: 15 × 6 × null = null, not 0 and not 90. Null honestly says "size not known"; inventing a width of 1 cm would give a volume of 90 that looks real and is wrong.
• Left Outer kept every sale. P109 had no partner in products, so its new columns are null and Power BI shows it as (Blank) in the chart. The total did not change.
• Expanding only the needed columns, and turning off load for products, keeps the model small.

What Inner would have done: keep 9 rows, drop BLK4003, and show total revenue ₹4,458 (75 less), with nothing on the report to tell you an order is missing.

7. Explore further

• Why volume? Delivery apps and couriers price a parcel partly by its size. With volume next to each sale you can sum the volume of an order or flag bulky items.
• Other join kinds: Right Outer (all products, matching sales), Full Outer (everything from both), Left Anti (sales rows with no product: a quick way to list unmatched IDs, here P109), Right Anti (products never sold).
• Match message. Always read "matches X of Y rows" before clicking OK. It is the fastest check that IDs are clean on both sides (same type, no spaces, same case).
• Merge vs relationship. You can also keep products as its own loaded table and create a relationship on product_id in Model view. For a small lookup that you always need with sales, merging is simple; for bigger models, a separate product table with a relationship (a star schema) is the professional pattern.
• Text keys need the text steps. If product_id had a trailing space in one table, nothing would match. Trim and Clean key columns in both tables before merging.

8. Common mistakes

9. Practise on your own

10. Think like a professional

• Business view. Never let a join silently change a total. Left Outer plus a "rows without product" count gives the business both the correct total and a to-do list (add P109 to the product master).
• Performance. Expand only the columns you will use, and remove helper columns such as length, depth and width once volume exists.
• Maintenance. Name the merge step ("Merged products") and keep the product table's cleaning in its own query, so a new product file needs no change in sales.
• Data quality. A Left Anti merge from sales to products is a ready-made check you can rerun every month.

Interview questions

1. What is the difference between Left Outer and Inner join? Left Outer keeps all rows of the first table and fills nulls where there is no match; Inner keeps only matching rows.
2. When do you use Merge instead of Append? When you add columns from a different table using a common key, not rows from a table with the same columns.
3. What is 15 × 6 × null in Power Query? null.
4. How do you list sales rows whose product is missing from the product table? Merge with Join Kind Left Anti.

Recap

Ravi Sir's Cards for this lesson

Revision cardComparison card

मराठी cards