How can I make a chart show inventory counts by location but still include items with zero?
Quick answer
Start with a table listing every location, including those with no inventory. Separately group inventory records by location and count the rows, then left-merge those counts into the complete location list. Set missing counts for locations with no match to zero, and build the chart from that summary—not directly from the inventory records. A row count measures records, not necessarily units.
The question, the details, the next step.
My chart only shows locations where inventory exists. How do I make it include every location, even those with a count of zero?
Create the summary from a complete list of locations rather than from the inventory records alone. In Power Query, group the inventory records by location and use Count rows to get a count for each group. Then merge that result into your complete location list using a left outer join, with the location list on the left. This join keeps every row from the left table, including locations with no matching inventory record. [S1] [S2]
Your summary should have one row per location, for example:
Location | Inventory record count North | 4 South | 0 West | 2
For a location with no match, set its missing count to 0 in the completed summary before charting. The cited Power Query guidance explains grouping, row counts, and join behavior, but not the exact steps for replacing missing values or configuring a chart.
What should the two input tables look like? I have a location list and an inventory export.
Keep them separate until you assemble the summary.
- Location list: one row for every location you want represented, with a consistent key such as
LocationIDand, optionally, a display name. - Inventory export: rows for inventory records, with the same location key and any item details you need.
For example, the location list might contain N1 — North, S1 — South, and W1 — West. The inventory export might have four rows for N1, two for W1, and none for S1. Grouping inventory by LocationID and counting rows gives counts for N1 and W1; merging those counts into the location list retains S1 as well. [S1] [S2]
Use matching key values in both inputs. For example, S1 in one table will not match South in the other. Check the completed summary for duplicate location rows; the intended layout has one row per location.
Does Count rows tell me how many units I have? Some inventory lines have a quantity greater than one.
No—not if a record can represent more than one unit. Count rows counts inventory records in each location group; it does not, by itself, tell you the total quantity of units. [S2]
Decide which measure answers your question: number of inventory records or number of units. If you want units, use a measure based on the quantity values rather than treating a row count as a unit total. Check that the quantity data is suitable for your intended calculation, and label the chart clearly so readers know what it counts.
Once the summary is ready, what should I use as the chart data, and how do I check that zero locations stayed in?
Use the completed summary as the chart’s data: locations as categories and your chosen count or quantity measure as the values. Don’t use only the grouped inventory results, because locations absent from those results would not be represented there. The cited guidance establishes how to preserve locations in the merged summary, but does not specify chart setup; use the chart options available in your application.
Before charting, compare the summary with your complete location list. Check that each intended location appears once, that locations with no matching records have a value of 0 rather than a missing value, and that the nonzero counts agree with your chosen measure. If you later change or refresh the data, repeat these checks. They confirm the summary structure, not whether the underlying inventory export is complete or accurate.
A practical checklist
List every location that should appear, including those with no inventory records.
Use matching location keys in the location list and inventory data.
Group inventory by location and choose a record count or quantity-based measure intentionally.
Left-merge the grouped results into the complete location list, keeping that list on the left. [S1]
Set missing counts to 0 and check for one summary row per location.
Build the chart from the completed summary and compare its categories with the full location list.
A few more useful answers
Why use a left outer join here?
It keeps every row from the left table. Put the complete location list on the left and merge the grouped inventory counts into it, so locations without matches are retained. [S1]
What if a location is missing from the complete location list?
Merging counts into that list will not add the missing location. Update the list first if the location should appear in the summary and chart.
Sources
References used for this guide.
- [S1] Merge queries overview: Power Query join kinds learn.microsoft.com · Checked
- [S2] Group or summarize rows in Power Query learn.microsoft.com · Checked