Power QueryExercises

Stock on hand from inventory movements in Power Query

Use case · By Tahir Nas · Examples run in our simulator · Updated

12 min read

Stock on hand is the sum of every movement for an item and location, once each movement carries the right sign: receipts and transfers in add, issues and transfers out subtract. In Power Query you give each movement type a sign from a small mapping table, multiply, and group by item and warehouse; everything else (stock at a date, running balances, month-end snapshots) is a variation on that one step.

What an inventory movements table looks like

Most warehouse and ERP systems can export a movements (or transactions) log: one row per event, with a date, an item code, a location, a movement type and a quantity. The export often has no stock column at all. That isn't a gap: stock is derived from movements, so a report that sums them correctly should agree with the system that produced them.

The awkward part is that the quantity column is usually unsigned. An issue of 120 units appears as 120, not -120, and the movement type tells you which way it went. Some systems sign the quantity already; check a few issues before you build anything, because applying a sign twice turns every issue into a receipt.

The examples below use a small log with five movement types. Each block is a complete query with its data written inline, so you can paste it into a blank query and compare.

Give every movement type a sign

The sign belongs in a table of its own: one row per movement type, with 1 for movements that add stock and -1 for movements that remove it. A merge brings the sign onto each movement, a custom column multiplies, and Table.Group sums by item and warehouse.

let
    Movements = #table(
        type table [Date = date, Item = text, Warehouse = text, Type = text, Qty = number],
        {
            {#date(2026, 9, 1), "BOLT-10", "A", "Receipt", 500},
            {#date(2026, 9, 3), "BOLT-10", "A", "Issue", 120},
            {#date(2026, 9, 5), "BOLT-10", "A", "Transfer out", 100},
            {#date(2026, 9, 5), "BOLT-10", "B", "Transfer in", 100},
            {#date(2026, 9, 8), "NUT-10", "A", "Receipt", 300},
            {#date(2026, 9, 9), "NUT-10", "A", "Issue", 310},
            {#date(2026, 9, 12), "BOLT-10", "B", "Adjustment", -4},
            {#date(2026, 10, 2), "NUT-10", "A", "Receipt", 200}
        }
    ),
    Signs = #table(type table [Type = text, Sign = number],
        {{"Receipt", 1}, {"Issue", -1}, {"Transfer in", 1}, {"Transfer out", -1}, {"Adjustment", 1}}),
    Joined = Table.NestedJoin(Movements, {"Type"}, Signs, {"Type"}, "S", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Joined, "S", {"Sign"}),
    Signed = Table.AddColumn(Expanded, "Signed Qty", each [Qty] * [Sign], type number),
    OnHand = Table.Group(Signed, {"Item", "Warehouse"}, {{"On hand", each List.Sum([Signed Qty]), type number}})
in
    OnHand

Result

ItemWarehouseOn hand
BOLT-10A280
BOLT-10B96
NUT-10A190

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The result has three rows: BOLT-10 holds 280 in warehouse A and 96 in warehouse B, and NUT-10 holds 190 in warehouse A. The transfer appears twice in the log, once as an outbound row from A and once as an inbound row to B, so it moves stock between locations without changing the company total of 376 bolts.

Adjustments carry their own sign in the export (a stock-count loss of four is -4), so their mapping sign is 1. In this export, count corrections are signed and everyday movements are not. The mapping table is where that knowledge lives, and anyone can read it.

Microsoft's Table.NestedJoin reference notes that a left outer join is the default when no join kind is given; the query states JoinKind.LeftOuter anyway so the intent is visible. If you'd rather not merge, the same idea works as a lookup; the guide on mapping tables instead of nested if compares the options.

Why not a conditional column?

A conditional column such as if [Type] = "Issue" or [Type] = "Transfer out" then -[Qty] else [Qty] works on day one. It fails the day the system adds "Return to supplier" or "Write-off", because the new type falls into the else branch and is counted as a receipt. A mapping table fails differently, and more usefully, as the next section shows.

Stop the refresh when a movement type is unmapped

When a type is missing from the mapping table, the left join returns null for its sign, [Qty] * null is null, and List.Sum ignores nulls. The movement disappears from stock on hand without an error.

let
    Movements = #table(
        type table [Date = date, Item = text, Type = text, Qty = number],
        {
            {#date(2026, 9, 1), "BOLT-10", "Receipt", 500},
            {#date(2026, 9, 3), "BOLT-10", "Issue", 120},
            {#date(2026, 9, 4), "BOLT-10", "Return to supplier", 20}
        }
    ),
    Signs = #table(type table [Type = text, Sign = number],
        {{"Receipt", 1}, {"Issue", -1}, {"Transfer in", 1}, {"Transfer out", -1}, {"Adjustment", 1}}),
    Joined = Table.NestedJoin(Movements, {"Type"}, Signs, {"Type"}, "S", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Joined, "S", {"Sign"}),
    Signed = Table.AddColumn(Expanded, "Signed Qty", each [Qty] * [Sign], type nullable number),
    OnHand = Table.Group(Signed, {"Item"}, {{"On hand", each List.Sum([Signed Qty]), type number}})
in
    OnHand

Result

ItemOn hand
BOLT-10380

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The query reports 380 bolts. The true figure is 360, because 20 went back to the supplier. Nothing in the output hints that a row was ignored.

The fix is a check step that compares the movement types in the data with the types in the mapping table, and raises an error naming the ones it doesn't know:

let
    Movements = #table(
        type table [Date = date, Item = text, Type = text, Qty = number],
        {
            {#date(2026, 9, 1), "BOLT-10", "Receipt", 500},
            {#date(2026, 9, 3), "BOLT-10", "Issue", 120},
            {#date(2026, 9, 4), "BOLT-10", "Return to supplier", 20}
        }
    ),
    Signs = #table(type table [Type = text, Sign = number],
        {{"Receipt", 1}, {"Issue", -1}, {"Transfer in", 1}, {"Transfer out", -1}, {"Adjustment", 1}}),
    Unknown = List.Difference(List.Distinct(Movements[Type]), Signs[Type]),
    Checked = if List.IsEmpty(Unknown) then Movements
        else error Error.Record("Unmapped movement type", "Add these types to the Signs table", Text.Combine(Unknown, ", "))
in
    Checked

Result: an error

Unmapped movement type: Add these types to the Signs table

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Put the check before the merge and build the rest of the query on Checked. A failed refresh with the message "Unmapped movement type" is a quick fix; a stock figure that is quietly 20 units out can go unnoticed for months. The lesson on validating data and failing loudly covers this pattern in more depth.

Stock on hand at any date

Stock at a past date is the same group, run on the movements up to and including that date. Keep the date in one step (or a parameter) so the report can be re-run for any month end.

let
    AsAt = #date(2026, 9, 30),
    Movements = #table(
        type table [Date = date, Item = text, Warehouse = text, Signed Qty = number],
        {
            {#date(2026, 9, 1), "BOLT-10", "A", 500},
            {#date(2026, 9, 3), "BOLT-10", "A", -120},
            {#date(2026, 9, 8), "NUT-10", "A", 300},
            {#date(2026, 9, 9), "NUT-10", "A", -310},
            {#date(2026, 10, 2), "NUT-10", "A", 200}
        }
    ),
    UpToDate = Table.SelectRows(Movements, each [Date] <= AsAt),
    OnHand = Table.Group(UpToDate, {"Item", "Warehouse"}, {{"On hand", each List.Sum([Signed Qty]), type number}}),
    Sorted = Table.Sort(OnHand, {{"Item", Order.Ascending}})
in
    Sorted

Result

ItemWarehouseOn hand
BOLT-10A380
NUT-10A-10

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

At 30 September the result shows BOLT-10 at 380 and NUT-10 at -10. The October receipt of 200 nuts is excluded by the filter. The sort step is there because, as the Table.Group documentation says, the function does not guarantee the order of the rows it returns.

Negative stock is not something a warehouse can hold, so -10 is a data problem, not a result. To find where it starts you need a running balance.

Running balance per item to find negative stock

A running balance needs the rows in date order within each item. Group by item with an all-rows aggregation (the Group By article on Microsoft Learn describes the All rows operation), sort each group by date, then add the balance inside it. The sort sits inside the group because the grouped rows' order isn't guaranteed. To keep the code short, this example and the next use a single warehouse; with several, group by item and warehouse so a shortfall in one location isn't hidden by stock in another.

let
    Movements = #table(
        type table [Date = date, Item = text, Signed Qty = number],
        {
            {#date(2026, 9, 1), "NUT-10", 300},
            {#date(2026, 9, 9), "NUT-10", -310},
            {#date(2026, 10, 2), "NUT-10", 200},
            {#date(2026, 9, 1), "BOLT-10", 500},
            {#date(2026, 9, 3), "BOLT-10", -120}
        }
    ),
    Sorted = Table.Sort(Movements, {{"Item", Order.Ascending}, {"Date", Order.Ascending}}),
    Grouped = Table.Group(Sorted, {"Item"}, {{"Rows", each
        let
            t = Table.AddIndexColumn(Table.Sort(_, {{"Date", Order.Ascending}}), "i", 1, 1),
            q = List.Buffer(t[Signed Qty])
        in
            Table.AddColumn(t, "Balance", each List.Sum(List.FirstN(q, [i])), type number),
        type table}}),
    Combined = Table.Combine(Grouped[Rows]),
    Negative = Table.SelectRows(Combined, each [Balance] < 0),
    Result = Table.RemoveColumns(Negative, {"i"})
in
    Result

Result

DateItemSigned QtyBalance
2026-09-09NUT-10-310-10

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

One row comes back: NUT-10 on 9 September, an issue of 310 that took the balance to -10. Either a receipt was posted late, or the issue quantity is wrong. That is the row to send to the warehouse team.

Inside each group, Table.AddIndexColumn numbers the rows from 1, and List.FirstN takes that many quantities to sum. List.Buffer holds the quantities in memory as a stable list, so each row reads the same list instead of re-evaluating the group. The post on when buffering helps and when it hurts explains the trade-off. For long histories, the guide to running totals and cumulative sums compares faster methods.

Two cautions apply to running balances:

  • Same-day order matters. If a receipt and an issue share a date, a sort on date alone can place the issue first and show a false negative. Sort on a timestamp or the transaction number too, when the export has one.
  • Opening balances must be in the log. If the export starts on 1 September but stock existed before that, add one opening-balance row per item and location, dated the day before, with its own movement type in the mapping table.

Month-end stock for every item

A month-end stock report needs a row for every item at every month end, including months with no movements. This example starts the log in August, so the first month end has stock for one item but not the other. Build the grid of items and dates first, then sum the movements up to each date:

let
    Movements = #table(
        type table [Date = date, Item = text, Signed Qty = number],
        {
            {#date(2026, 8, 20), "NUT-10", 300},
            {#date(2026, 9, 9), "NUT-10", -110},
            {#date(2026, 10, 2), "NUT-10", 200},
            {#date(2026, 9, 1), "BOLT-10", 500},
            {#date(2026, 10, 3), "BOLT-10", -120}
        }
    ),
    MonthEnds = {#date(2026, 8, 31), #date(2026, 9, 30), #date(2026, 10, 31)},
    Items = List.Sort(List.Distinct(Movements[Item])),
    Grid = Table.FromRows(List.Combine(List.Transform(Items, (i) => List.Transform(MonthEnds, (m) => {i, m}))), type table [Item = text, Month End = date]),
    WithStock = Table.AddColumn(Grid, "On hand", (r) =>
        List.Sum(Table.SelectRows(Movements, each [Item] = r[Item] and [Date] <= r[Month End])[Signed Qty]) ?? 0, type number)
in
    WithStock

Result

ItemMonth EndOn hand
BOLT-102026-08-310
BOLT-102026-09-30500
BOLT-102026-10-31380
NUT-102026-08-31300
NUT-102026-09-30190
NUT-102026-10-31390

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Six rows come back. BOLT-10 shows 0 at the end of August (it had no movements yet), 500 at the end of September and 380 at the end of October. NUT-10 shows 300, 190 and 390. The ?? 0 turns "no movements yet" into zero instead of null, which keeps the column numeric for charts and pivot tables.

In a real report the month ends would come from a date table rather than a hard-coded list; the guide to building a date table shows how, and Date.EndOfMonth gives the last day of any month. The monthly HR headcount report counts people at month ends in the same way. This version re-scans the movements for each grid row, which is fine for a small log. For a large one, group movements by item and month first, then run a cumulative sum over the months.

A checklist before you trust the numbers

Stock figures get questioned, so the query should answer the questions before anyone asks them:

CheckHow
Every movement type has a signList.Difference against the mapping table, error if not empty
Quantities are not already signedLook at a few issues in the raw export
Opening balances existOne row per item and location before the first movement
No negative running balanceRunning balance per item, filter < 0
Total agrees with the systemCompare one item and one date with the stock screen in the source system

The last check matters most. Pick an item, pick a date, and compare the query's figure with what the warehouse system reports for that date. If they agree for a handful of items, the logic is right; if they don't, the difference is usually an unmapped type, a missing opening balance or a double-signed quantity.

Practise the steps

The skills in this post are grouping, merging and validation. The lesson on Group By with several aggregations and the lesson on All Rows grouping and per-group calculations cover the grouping steps. To try them on stock data, work through the exercise Find stock held in warehouse B and running low, then Add a running total in date order. The inventory and supplier merge project on Power Query Exercises puts the same steps into a full report, and every example there runs in the browser.

Frequently asked questions

Should stock on hand be calculated in Power Query or in DAX?

Both work. Power Query suits a fixed snapshot table (stock by item and month end) that loads into Excel or a simple model. In Power BI, if users need stock at any date they pick in a slicer, load the signed movements and calculate the balance with a DAX measure. The guide on Power Query M vs DAX explains where each kind of calculation belongs. For the database side of that choice, see Transform in SQL, Power Query or DAX?.

How do I value the stock as well as count it?

Merge the stock-on-hand table with an item price or standard cost table on the item code, then multiply. Costing methods such as FIFO or weighted average need the full movement history with costs, which is a bigger job than a single merge.

What if the export splits receipts and issues into two columns?

Some systems export Qty In and Qty Out instead of a type column. Then the signed quantity is [Qty In] - [Qty Out], after replacing nulls with zero, and no mapping table is needed.

Sources

About the author

Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.

Keep reading

Practise Power Query in the browser with graded exercises checked against fresh data.

Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.