Menu
×
   ❮   
HTML CSS JAVASCRIPT SQL PYTHON JAVA PHP C C++ C# AWS W3.CSS HOW TO BOOTSTRAP REACT MYSQL JQUERY EXCEL XML DJANGO NUMPY PANDAS NODEJS DSA TYPESCRIPT ANGULAR ANGULARJS GIT POSTGRESQL MONGODB ASP AI R GO KOTLIN SWIFT SASS VUE GEN AI SCIPY CYBERSECURITY DATA SCIENCE INTRO TO PROGRAMMING HTML & CSS BASH RUST TOOLS MATH

Power BI Power Query Merge Queries


Share

What Is Missing

This page continues the cleanup from Append Queries.

Open pq_cleanup.pbix, the report you saved there, and select Home > Transform data.

The Sales query now holds all 2,740 orders of 2024 and 2025. It has the sales amount, but no product category and no cost.

That information is in a small file with one row per product:

Download poke_mart_products.csv

ABCProductABCCategory123UnitCost
Poke BallBalls110
Great BallBalls330
Ultra BallBalls700
PotionMedicine150
Super PotionMedicine380
Hyper PotionMedicine680
ReviveMedicine900
AntidoteStatus Healers45
Paralyze HealStatus Healers95
AwakeningStatus Healers120

To bring Category and UnitCost into Sales, you merge the two queries.

A merge matches the rows of two tables on a column they have in common, here Product, and adds columns from the second table to the first.

Append and merge both combine queries.

Append adds rows: it stacks tables on top of each other.

Merge adds columns: it puts matching information side by side.


Load the Products File

  1. In the Power Query Editor, on the Home tab, select New Source > Text/CSV.
  2. Find poke_mart_products.csv and select Open.
  3. In the preview window, select OK.

A query called poke_mart_products appears, with 10 rows and 3 columns.

Check that UnitCost has the 123 icon (Whole Number).


Merge Queries

  1. Select the Sales query.
  2. On the Home tab, in the Combine group, select the arrow next to Merge Queries, and then select Merge Queries.
  3. The Merge dialog shows a preview of Sales at the top. Select the Product column header in that preview.
  4. In the dropdown list below it, select poke_mart_products. Then select its Product column header.
  5. Set Join Kind to Left Outer (all from first, matching from second). This is the default.
  6. At the bottom of the dialog, Power Query shows how many rows match. All rows of Sales should match: The selection matches 2740 of 2740 rows from the first table.
  7. Select OK.

The Merge dialog with the Product column selected in the Sales preview and in the poke_mart_products preview, Join Kind set to Left Outer, and the match message at the bottom

Sales gets a new column named poke_mart_products.

Every cell in it says Table: it holds the matching row of the products table, packed into one cell.

The next step unpacks it.


Expand the New Column

  1. Select the expand icon (two arrows) at the right of the poke_mart_products column header.
  2. Clear the Product check box, so only Category and UnitCost are selected. Sales already has a Product column.
  3. Clear the Use original column name as prefix check box. If you leave it selected, the new columns are called poke_mart_products.Category and poke_mart_products.UnitCost.
  4. Select OK.

The expand menu of the poke_mart_products column, with Category and UnitCost selected, Product cleared, and Use original column name as prefix cleared

Sales now has Category and UnitCost at the end.

After the merge, the preview no longer shows the rows in OrderID order, so your first rows may be other orders.

Here are orders 1001 to 1006 (OrderDate, Store and Region are left out to save space):

123OrderIDABCProduct123Quantity123UnitPrice123SalesABCCategory123UnitCost
1001Hyper Potion112001200Medicine680
1002Poke Ball192003800Balls110
1003Great Ball136007800Balls330
1004Antidote9100900Status Healers45
1005Potion193005700Medicine150
1006Paralyze Heal1200200Status Healers95
.....................


Why the Cleaning Mattered

A merge finds only exact matches.

Every order finds its product, because you cleaned the Product column in Replace Values and Fill Down.

Without Trim and Capitalize Each Word, 592 of the 2,740 orders (285 from 2024 and 307 from 2025) would match nothing, and their Category and UnitCost would be null.

With Trim alone, 431 orders would still have no match.


Add the Cost Column

The cost of an order is the quantity times the unit cost:

  1. On the Add Column tab, select Custom Column.
  2. In New column name, type Cost.
  3. In Custom column formula, after the equal sign, enter the formula below.
  4. Select OK.
  5. The new column has the type Any, with the ABC123 icon. Select that icon in the Cost header and choose Whole Number.

Custom column formula

[Quantity] * [UnitCost]
123OrderIDABCProduct123QuantityABCCategory123UnitCost123Cost
1001Hyper Potion1Medicine680680
1002Poke Ball19Balls1102090
1003Great Ball13Balls3304290
1004Antidote9Status Healers45405
1005Potion19Medicine1502850
1006Paralyze Heal1Status Healers9595
..................

The Sales query is finished: 2,740 rows and 11 columns.


Close & Apply

The products query is a helper too, because its columns are now in Sales.

In the Queries pane, right-click poke_mart_products and select Enable load to clear its check mark, as you did for the year queries.

Then, on the Home tab, select Close & Apply.

Power BI runs all the queries, loads the results into the report, and closes the Power Query Editor.

The Data pane shows two tables: Sales and Targets.

Select File > Save to save pq_cleanup.pbix.


Check the Result

The numbers must be the same as in the clean file poke_mart_sales.xlsx that you used in First Report.

Add a table visual with Category, Sales and Cost from the Sales table, and compare:

CategorySum of SalesSum of Cost
Balls4,447,2002,477,560
Medicine4,413,6002,386,540
Status Healers556,000260,505
Total9,416,8005,124,605

Power BI shows these numbers without thousands separators, for example 4447200.

To check the number of rows, switch to Table view and select the Sales table.

The status bar at the bottom shows Table: Sales (2,740 rows).

The match is exact: every one of the 2,740 orders has the same date, store, region, product, quantity, price, sales and cost as in the clean file.

The only differences are the name OrderDate (Date in the clean file) and the extra UnitCost column.

Good job! You started with two messy exports and built the clean sales table yourself.

Every step is saved in the queries, so a refresh repeats the whole cleanup on new data.


Join Kinds

The Join Kind decides which rows the merge keeps.

The first table is the one at the top of the Merge dialog:

Join kindKeeps
Left OuterAll rows from the first table, and the matching rows from the second. Used in this chapter.
Right OuterAll rows from the second table, and the matching rows from the first.
Full OuterAll rows from both tables, matching or not.
InnerOnly the rows that match in both tables.
Left AntiOnly the rows from the first table that have no match in the second.
Right AntiOnly the rows from the second table that have no match in the first.

Left Anti is a quick way to find problems.

A Left Anti merge of Sales with poke_mart_products returns the orders whose product is not in the products file.

After your cleaning, it returns no rows.

Before the cleaning, it would have returned 592.


Merge and XLOOKUP

If you know Excel, a Left Outer merge works like XLOOKUP or VLOOKUP: for each row, find a value in another table and bring back information from it. There are some differences:

  • A merge can bring back many columns in one step.
  • A merge is a step in the query, so it runs again at every refresh. There is no formula to copy down.
  • XLOOKUP returns only the first match. If the second table has the same key twice, a merge returns both rows, and the order is repeated. Make sure the key column of the lookup table is unique. In poke_mart_products, each product appears once.

Merging is not the only way to use a products table.

You can also load it as a separate table and connect it to Sales with a relationship.

That is the idea of the star schema, which you learn in the Data Modeling chapter.



Free account

Track your progress

XP earned 0 Day streak 0 Spaces 0 League —

New

Learn in Adventure Mode W3Schools Adventure App

Coding fundamentals as bite-sized lessons and challenges.

×

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
sales@w3schools.com

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail:
help@w3schools.com

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookies and privacy policy.

Copyright 1999-2026 by Refsnes Data. All Rights Reserved. W3Schools is Powered by W3.CSS.