Power Query Problems

How to Merge Power Query Tables by Stock Quantity and Article ID

WPS Content Manager Sep 27, 2026 869 views

Question details

The user wants to combine an inventory stock table with a purchase history table using Power Query by matching the Article ID, retaining available stock, and calculating eligible quantities.

How to Merge Power Query Tables by Stock Quantity and Article ID

Product: Microsoft Excel
Device & OS: not provided
Scenario: Merging inventory data with purchase history to determine available stock limits and calculate eligible purchase quantities chronologically.
Observed behavior: Needs to output a combined table that accurately maps stock quantity, purchased quantity, purchase date, and purchase price without duplicating unresolved data.

Before you start

Ensure both your stock table and purchase history table are formatted as official Excel Tables (Ctrl+T) and contain a matching 'Article ID' column with no leading or trailing spaces.

Solution 1Recommended

Use Left Outer Merge and Calculate Available Quantity

This is the most robust method to combine your stock and purchase history tables based on Article ID while maintaining accurate chronological stock calculations.

A Left Outer Merge keeps all rows from your primary table (Stock) and brings in matching rows from the related table (Purchases). Sorting by date before performing calculations ensures that your running stock totals remain accurate.

Load Both Tables into Power Query

Select your Stock table, go to the Data tab on the ribbon, and click 'From Table/Range'. Repeat this process for your Orders table so both are loaded in the Power Query Editor.

Perform a Left Outer Merge

In the Power Query Editor, go to the Home tab and click 'Merge Queries'. Select your Stock table as the primary table and Orders as the secondary. Click the 'Article ID' column in both previews to link them, and select 'Left Outer' as the Join Kind.

Expand Purchase Data

Click the double-arrow expand icon on the newly created merged column header. Select the specific columns you want to extract, such as Purchase Quantity, Purchase Date, and Purchase Price, and uncheck 'Use original column name as prefix'.

Sort by Date and Calculate

Click the drop-down arrow on the 'Purchase Date' column and select 'Sort Ascending'. If you need to calculate remaining stock, go to Add Column > Custom Column to subtract the purchase quantity from the stock limit.

Clean Data and Load

Filter out any rows where the 'Delivered' value (or target quantity) is null. Remove any helper columns you no longer need, then go to Home > Close & Load to output the merged data back to your spreadsheet.

Use Left Outer Merge and Calculate Available Quantity

Performance Tip: If you experience slow loading times during complex custom calculations, wrap your specific table steps in the Table.Buffer() function within the Advanced Editor to lock the data into memory.

Free Microsoft Office alternative

Need to manage complex inventory data? Try WPS Office

While Power Query is a specific feature of Microsoft Excel, WPS Office provides a powerful, lightweight alternative with advanced built-in data processing tools. You can easily merge, filter, and analyze your stock and purchase history using familiar features like XLOOKUP, PivotTables, and Data Consolidation for free.

1. Download and Install WPS Office: Visit the official WPS Office website and download the free software suite for your operating system.
2. Open Your Inventory Workbook: Launch WPS Spreadsheets and open your existing .xlsx file containing the stock and purchase data.
3. Merge Data Using Built-in Tools: Use XLOOKUP or the Consolidate feature under the Data tab to instantly match and merge your data by Article ID.

100% compatible with Microsoft Excel (.xlsx) file formats.Includes advanced formulas like XLOOKUP and VLOOKUP to merge data without complex M-code.Lightweight design ensures incredibly fast performance even with large inventory datasets.Familiar user interface makes transitioning from Microsoft Excel completely seamless.

microsoft office alternative - wps office

Frequently Asked Questions

Why are my merged rows duplicating in Power Query?

Rows duplicate during a merge if there are multiple matching records in the secondary table, such as multiple purchases for a single Article ID. When you expand the merged column, Power Query naturally creates a separate row for each match. You may need to group your data by Article ID first if you only want summary totals.

What does Table.Buffer do in this Power Query scenario?

The Table.Buffer function loads a table directly into memory, preventing the Power Query engine from re-evaluating the underlying steps multiple times. This is especially useful when sorting purchases chronologically by date before performing cumulative stock calculations, ensuring the sort order is preserved during evaluation.

Can I perform a left outer merge without using Power Query?

Yes, you can achieve a similar result directly in standard spreadsheet software by using VLOOKUP, XLOOKUP, or INDEX MATCH functions to pull the corresponding stock quantities into your purchase history table based on the matching Article ID.

Excel & Data Problems