Power Query Problems

How to Automate Excel Inventory Reordering with Power Query

Guest Writer Sep 28, 2026 870 views

Question details

The user needs to automate the consolidation of daily inventory data by UPC to calculate available quantities, dynamic reorder amounts, and carryover stock.

How to Automate Excel Inventory Reordering with Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Consolidating daily product usage logs to track stock levels and automatically generate restocking requirements.
Observed behavior: Requires a dynamic data transformation setup that groups records by UPC, retains stock sizes, and carries leftover inventory into the next day's calculations.

Before you start

Ensure all your daily product usage data files are stored in a single designated folder and share the exact same structural format (identical column names and data types) before loading them into Power Query.

Solution 1Recommended

Consolidate and Calculate Inventory Using Power Query

Use Power Query's 'Get Data from Folder' feature to automatically combine daily exports, group them by UPC, and apply custom calculation columns.

Power Query is highly effective for appending daily inventory reports. By pointing it to a folder, any new daily file added will automatically be included in your calculations the next time you refresh the data.

Combine Daily Files

In Excel, navigate to the Data tab, click 'Get Data', select 'From File', and then 'From Folder'. Browse to your daily exports folder and click 'Combine & Transform Data'.

Group Data by UPC

In the Power Query Editor, select your UPC column. Go to the Home tab and click 'Group By'. Create a new column to sum the quantities for each UPC to find the total product usage.

Create Custom Reorder Logic

Go to the Add Column tab and click 'Custom Column'. Write a formula to calculate reorder quantities based on your box counts and current stock (e.g., subtracting daily usage from starting inventory).

Load the Automated Table

Once your transformations and carryover calculations are set, click 'Close & Load' on the Home tab. The consolidated inventory table will be inserted into your worksheet.

Consolidate and Calculate Inventory Using Power Query

Advanced Carryover Logic: Carrying inventory over from one specific day to the next across multiple files often requires custom M code logic. For highly complex setups, consider consulting the Power Query forum on the Microsoft Fabric Community.

Solution 2

Automate with Excel Formulas and Power Automate

If Power Query carryover logic is too complex, use standard Excel functions for calculations and Power Automate to run daily updates.

Free Microsoft Office alternative

Manage Inventory Seamlessly with WPS Spreadsheets

If you find Power Query's M code too complex for managing inventory carryover, WPS Office provides a free, lightweight, and highly compatible alternative. You can easily manage large datasets, consolidate daily UPC data using PivotTables, and apply familiar lookup functions without steep learning curves.

1. Download and Install: Download the WPS Office suite for free from the official website.
2. Open Your Inventory File: Launch WPS Spreadsheets and open your existing .xlsx inventory tracker.
3. Use Data Consolidation: Navigate to the Data tab and use the 'Consolidate' feature to merge your daily usage quantities quickly.

Fully compatible with Microsoft Excel (.xlsx) file formatsRobust built-in PivotTable and data consolidation toolsFamiliar user interface ensuring seamless migrationFree, lightweight, and fast performance on any device

microsoft office alternative - wps office

Frequently Asked Questions

Will Power Query automatically update my inventory when a new day's file is added?

Yes. If you set up Power Query using the 'Get Data From Folder' method, you only need to drop the new day's file into that folder and click 'Refresh All' in Excel. The new data will automatically be processed.

How do I fix UPC codes losing their leading zeros in Power Query?

Power Query often automatically detects UPCs as numbers and removes leading zeros. To fix this, find the 'Changed Type' step in your Applied Steps list, and change the data type of the UPC column from Whole Number to Text.

Can I use Power Query to calculate closing stock for the next day's opening stock?

Yes, but it requires advanced logic. You typically need to sort your data chronologically, add an Index column, and use custom M code to reference previous rows, or self-merge queries to align yesterday's closing balance with today's opening balance.

Excel & Data Problems