How to Merge Cancellation and Regular Orders in Power Query
Question details
The user needs to merge a table of cancellation orders with a table of regular orders to accurately identify and remove the cancelled items.
- Product
- Excel / Power Query
- Device & OS
- not provided
- Scenario
- Filtering out cancelled transactions from a master order dataset by matching specific criteria such as Person ID, Product Code, and Order Year.
- Observed behavior
- The goal is to align cancellation records with their corresponding regular orders using a proper join type so that matched cancelled rows can be successfully isolated and removed.
Ensure that the 'Person ID' and 'Product Code' columns in both your regular orders and cancellations datasets share the exact same data types before attempting the merge.
Duplicate Source Queries and Merge Using a Left Outer Join
This method isolates the regular and cancelled orders into separate queries, allowing you to merge them precisely using multiple matching criteria.
To properly exclude cancelled orders from your primary dataset, the best approach is to branch your main query into specialized queries. By merging the cancellations against the regular orders using a Left Outer join, you can easily flag and remove the rows that correspond to matched cancellations.
Right-click your main source query and select 'Duplicate'. Do this twice so you have three queries. Rename them to 'All', 'Regular', and 'Cancelled'.
Select the 'Regular' query and apply a filter to show only regular orders. Next, select the 'Cancelled' query and filter it to show only the cancellation records.
Go to the Home tab on the Power Query ribbon and click on 'Merge Queries', then choose 'Merge Queries as New'.
In the Merge dialog, choose the 'Cancelled' query as the first table and 'Regular' as the second. Hold down the Ctrl key and click 'Person ID', then 'Product Code' in both tables to ensure they are matched in the exact same order.
Set the Join Kind to 'Left Outer (all from first, matching from second)' and click OK. Finally, click the expand icon on the newly created column to reveal matched regular orders, allowing you to identify which rows need to be deleted.
Match and Filter Orders Easily in WPS Spreadsheet
If you prefer avoiding complex Power Query setups, you can seamlessly match cancellation records with regular orders directly using the powerful lookup and filtering tools built into WPS Spreadsheet.
- 1. Open Your Datasets: Launch WPS Spreadsheet and open the workbook containing your master order list and your cancellation records in separate sheets.
- 2. Create a Helper Column: In your Regular Orders sheet, insert a new column named 'Cancellation Match'.
- 3. Apply a Lookup Formula: Use the VLOOKUP or XLOOKUP function to search for the combined Person ID and Product Code from the regular orders against the cancellations list.
- 4. Filter the Results: Select your column headers and click 'Filter' under the Data tab. Filter the 'Cancellation Match' column to show only the matched records.
- 5. Remove Cancelled Rows: Highlight the visible filtered rows containing the matched cancellations, right-click, and choose 'Delete' to clean your final dataset.
Frequently Asked Questions
Why are my Person ID and Product Code columns not matching during the merge?
This usually happens when the data types differ between the two tables (e.g., one is formatted as Text and the other as a Whole Number). Ensure both columns share the exact same data type before initiating the merge.
Can I match orders strictly by year in Power Query?
Yes. You can extract the year from your existing date columns to create a new 'Year' column in both queries. Include this new 'Year' column in your Ctrl-click sequence when defining the merge criteria.
How do I handle duplicate cancellations after merging?
After expanding your merged columns, you can select the relevant identifier columns (like Person ID and Product Code), right-click the column header, and choose 'Remove Duplicates' to clean up redundant records.
