Your stock data in Excel or Power BI with Power Query

If your team already works in Excel or Power BI, you probably do not want another dashboard. You want your stock figures in the workbook or report you already have. OpenBoxes Lift supports this today with a Power Query recipe: two short queries you paste into Power BI Desktop or Excel for Windows, which read the Reporting API with an API key. There is nothing to download or install, and it works on every plan (Shared, Dedicated and Enterprise) with no add-on to buy.

This article covers what you get, the three decisions to make before you paste anything, and how to keep a refresh small and safe to share. The recipe itself, query by query, is in the docs: Power BI and Excel.

What the recipe gives you#

One table for each dataset you ask for. The Reporting API has seven:

• summary: the Reports page tiles as one row: stock-outs, below minimum, expiring lots, open orders and stock value.
• stock-on-hand: one row per product and location, with the minimum, reorder and maximum levels set in OpenBoxes, days at zero and months of stock.
• stock-history: quantities per product and location, by day for the last 90 days or by month for up to 13 months.
• stock-value-history: the value of your stock over time, by day and by month.
• lots-expiring: lots and their expiry dates.
• purchase-orders-open: open purchase orders, one row per order and currency.
• activity: received, issued, consumed, adjusted and transferred quantities per product and location, by day or by month.

The columns arrive with their types already set (numbers, dates, true or false), in the order the field reference lists them.

Daily figures, not live ones#

The Reporting API does not read your live OpenBoxes database. Once a day, after 04:00 in your account's time zone, we check your instance and keep the result, and the API serves that copy. Every row says which daily check it comes from.

Two things follow. Refresh once a day, after the check: refreshing more often brings in no newer figures. And for a figure as it stands right now, use OpenBoxes itself, its dashboard or its Reporting menu, which read the live data.

Three decisions before you paste anything#

1. A key for this report only#

Only the account owner can create API keys, on the API keys page of the portal. Give the key the reporting scope and use it for this one file. Scopes are recorded today but not yet enforced, so any key on your account would work; a reporting key is the one that keeps working once they are. A key of its own also means you can revoke it without breaking anything else. Revoking stops a key within seconds.

2. Quantities, or values too#

By default a key returns quantities only. Every money field (stock value, prices, value at risk and order amounts) is present but empty. The account owner can tick Include values for a key, when creating it or later from the key's menu.

Values follow the key, not the person looking at the report. If the key has Include values, the money columns reach everyone the report is shared with, including a report published with Power BI's Publish to web, which makes it public, and anyone who is given the file. For a report you will share widely, use a key without Include values.

3. The key lives inside the file#

The recipe keeps the key in a query called LiftApiKey, and anyone who has the .pbix file or the workbook can open that query and read it. Keep it connection-only, so the key is never loaded into the report or written into a sheet: in Power BI, clear Enable load on it; in Excel, use Close & Load To, then Only Create Connection. If the file leaves your team, revoke the key.

Which table answers which question#

A few starting points, each one line in the recipe:

• What is short at each site? stock-on-hand. Its stockState column reads AT_ZERO_MIN_SET, AT_ZERO_NO_MIN, BELOW_MINIMUM or OK, so a filter on it gives you a shortage list per location.
• How fast are we using it? activity by month, or the amc (average monthly issues and consumption) and monthsOfStock columns in stock-on-hand. Months of stock explains how to read a low number.
• What expires in the next quarter? lots-expiring with a 90-day window.
• Which orders are still open? purchase-orders-open.
• What is our stock worth over the year? stock-value-history, with a key that has Include values.

In the recipe, the second argument takes the Reporting API's own parameter names, for example:

LiftReport("lots-expiring", [withinDays = "90"])
LiftReport("stock-history", [grain = "monthly"])

Keep each refresh small#

The Reporting API has its own allowance of 600 requests an hour per account, separate from your plan's API limit. You can use all 600 at once; after that, one more comes back every 6 seconds.

For each table, the recipe sends at least one request for the table's columns plus one request per 1,000 rows. Power BI and Excel can also run a query more than once during a single refresh, and each run costs requests again. The docs work through an example of 6,000 products at their locations: stock on hand takes at least 7 requests, 13 months of monthly history at least 79, and 30 days of daily history about 181.

So:

• Use monthly history for anything longer than a few weeks.
• Filter by location when a report is about one site.
• Leave out tables the report does not use.

If the allowance runs out, the API answers "too many requests" and Power Query waits and retries by itself a few times, so a large refresh slows down and can fail if several large tables refresh at once.

Scheduled refresh in the Power BI Service#

What Microsoft documents suggests no gateway is needed: set the data source credentials to Anonymous and turn on Skip test connection. Microsoft's own limits apply, such as eight scheduled refreshes a day on shared capacity, and one a day after our daily check is all you need. If the Service will not refresh it, refresh in Power BI Desktop and publish again.

Other routes to the same figures#

• Excel for Mac is not covered.
• No API key at all: the scheduled CSV export emails the same seven datasets as CSV files every day, week or month, and Excel opens them with Data, then From Text/CSV. Those files do not refresh themselves. A scheduled CSV export for your accountant walks through it.
• Looker Studio cannot call the Reporting API, because it cannot send the key in a header. What works today is manual: download a CSV file from a scheduled export and add it with Google's own CSV file upload. See Looker Studio.

Summary#

• The Power Query recipe brings seven report datasets into Power BI Desktop or Excel for Windows, on every plan, with nothing to install.
• The figures are from a daily check, so one refresh a day is enough.
• Use a reporting key made for this file, decide deliberately about Include values, and keep the key's query connection-only.
• Keep refreshes small with monthly history, a location filter and only the tables you use.

New to API keys? Your first OpenBoxes API call starts from the beginning. Everything that connects to OpenBoxes Lift today is listed on Integrations.