Power BI and Excel

Power BI and Excel can read your report data today, through a Power Query recipe: two short queries you paste into Power BI Desktop or Excel, which read the Reporting API with an API key. It works on every plan (Shared, Dedicated and Enterprise), with no add-on to buy.

The Power BI connector is coming soon. We are building it and it is not available yet, with no date. There is nothing to download or install today: this page is the whole recipe, and the recipe is how Power BI and Excel connect until the connector is ready.

If you do not want to use an API key at all, the scheduled CSV export sends the same seven datasets as CSV files, which Excel opens with Data, then From Text/CSV. Those files do not refresh by themselves.

What you get#

A table in Power BI or Excel for each Reporting API dataset you choose: summary, stock-on-hand, stock-history, stock-value-history, lots-expiring, purchase-orders-open and activity. The columns are the dataset's fields, in order, with their types (numbers, dates, true or false), exactly as listed in the field reference.

The figures come from our daily check, not from your live OpenBoxes data (see Where the data comes from). A refresh brings in the latest daily check; refreshing more than once a day does not bring newer figures.

Before you start: an API key for reporting#

The account owner creates the key. Only the account owner can create API keys, on the API keys page of the portal. Invited teammates cannot.
Give it the reporting scope, and use this key for Power BI or Excel only, so you can revoke it without breaking anything else.
Decide about values. A key returns quantities only, with every money field empty, unless the account owner ticks Include values for it (when creating the key, or later from the key's menu on the API keys page). Only tick it if everyone who will see the report, and everyone who could get the file, may see your stock value, prices and order amounts. See Who can see the data.

The key is shown once when you create it. Keep it for the next step.

The recipe#

The recipe is two queries:

LiftApiKey holds your API key.
LiftReport is a function that reads one dataset, every page of it, and returns a typed table.

The function sends the key only in the X-API-Key header. It never puts the key in the web address: the Reporting API refuses any request with a key in the address (400 api_key_in_url), because addresses end up in logs.

It asks for pages of 1,000 rows in JSON and follows page.nextCursor in each response body until it is empty. (Power Query cannot read the Reporting API's own response headers outside a custom connector, so the recipe cannot page in CSV, which carries the next page in a header.)

1. The LiftApiKey query#

Create a blank query, name it LiftApiKey, and replace its whole content with your key in double quotes:

"obc_yourKeyId.yourSecret"

2. The LiftReport function#

Create a second blank query, name it LiftReport, and replace its whole content with this:

// LiftReport: reads one OpenBoxes Lift Reporting API dataset, every page.
// Needs a query named LiftApiKey that holds your API key as text.
(dataset as text, optional options as nullable record) as table =>
let
    Path = "api/v1/reporting/" & dataset,
    Headers = [#"X-API-Key" = Text.Trim(LiftApiKey), Accept = "application/json"],

    // Option values are sent as text; a date is sent as yyyy-MM-dd.
    Raw = if options = null then [] else options,
    AsText = (v) =>
        if v is text then v
        else if v is date then Date.ToText(v, [Format = "yyyy-MM-dd", Culture = "en-US"])
        else if v is logical then (if v then "true" else "false")
        else Text.From(v, "en-US"),
    Extra = Record.FromList(List.Transform(Record.FieldValues(Raw), AsText), Record.FieldNames(Raw)),

    // One request. The key goes in the X-API-Key header only, never in the address.
    // The API explains these refusals in its JSON body, so they are read here
    // instead of being turned into a generic error.
    Call = (relativePath as text, query as record) as record =>
        let
            Response = Web.Contents("https://api.openboxes.cloud", [
                RelativePath = relativePath,
                Query = query,
                Headers = Headers,
                ManualStatusHandling = {400, 402, 404, 405, 409, 410}
            ]),
            Body = Json.Document(Response),
            Checked =
                if Body is record and Record.HasFields(Body, "error") then
                    error Error.Record(
                        "OpenBoxes Lift: " & Text.From(Body[error]),
                        Text.From(Record.FieldOrDefault(Body, "message", "The Reporting API refused the request.")))
                else Body
        in
            Checked,

    // The columns and their types, from the dataset's schema (one request).
    SchemaQuery = if Record.HasFields(Extra, "grain") then [grain = Extra[grain]] else [],
    Fields = Call(Path & "/schema", SchemaQuery)[fields],
    Names = List.Transform(Fields, each [name]),
    TypeOf = (t as text) as type =>
        if t = "integer" then Int64.Type
        else if t = "decimal" then type number
        else if t = "boolean" then type logical
        else if t = "date" then type date
        else if t = "datetime" then type datetimezone
        else type text,
    Types = List.Transform(Fields, each {[name], TypeOf([type])}),

    // Every page, one after another, 1,000 rows a page. The next page's cursor
    // is read from the response body (page.nextCursor), null on the last page.
    PageQuery = Record.Combine({Extra, [format = "json", limit = "1000"]}),
    GetPage = (cursor as nullable text) as record =>
        Call(Path, if cursor = null then PageQuery else Record.AddField(PageQuery, "cursor", cursor)),
    Pages = List.Buffer(List.Generate(
        () => GetPage(null),
        (p) => p <> null,
        (p) => let next = p[page][nextCursor] in if next = null then null else GetPage(next)
    )),

    Rows = List.Combine(List.Transform(Pages, each [rows])),
    AsTable = if List.IsEmpty(Rows) then #table(Names, {}) else Table.FromRecords(Rows, Names, MissingField.UseNull),
    Typed = Table.TransformColumnTypes(AsTable, Types, "en-US")
in
    Typed

3. One query per table#

For each table you want, create another blank query, name it after the table (for example StockOnHand), and replace its content with one line that calls the function:

LiftReport("stock-on-hand")

More examples:

LiftReport("summary")
LiftReport("lots-expiring", [withinDays = "90"])
LiftReport("stock-history", [grain = "monthly"])
LiftReport("stock-on-hand", [locationId = "your-location-id", state = "needs_attention"])
LiftReport("activity", [grain = "monthly"])
LiftReport("purchase-orders-open", [status = "all"])

The second argument is optional. Its field names are the Reporting API's own parameter names (see Parameters), and their values are text in double quotes. format, limit and cursor are set by the function, so leave them out. A name the dataset does not know is refused with the API's own message (unknown_parameter). category on activity takes one value in this recipe; to see several categories, leave it out and filter the table.

In Power BI Desktop#

Select Get Data, then Blank Query. The Power Query Editor opens with a new query.
Open the Advanced Editor (on the Home or View tab), replace everything in it with the LiftApiKey content above, and select Done. Rename the query LiftApiKey. Then right-click LiftApiKey in the Queries list and clear Enable load, so the key is not loaded into the report.
Do the same for LiftReport, and then for each table query.
The first time a query runs, Power Query asks how to connect to https://api.openboxes.cloud. Choose Anonymous: the key travels in the header the recipe sets, not as a Power BI credential. If you are asked for a privacy level, choose Organizational.
Select Close & Apply to load the tables.

To refresh, use Refresh on the Home tab.

Microsoft's own guides for these screens: Query overview in Power BI Desktop (the Advanced Editor and Close & Apply), Managing query refresh (Enable load) and the Web connector (choosing Anonymous).

In Excel for Windows#

Select Data, then Get Data, then From Other Sources, then Blank Query.
In the Power Query Editor, select Home, then Advanced Editor, paste the LiftApiKey content, and select Done. Rename the query LiftApiKey.
Do the same for LiftReport and each table query.
When asked how to connect to https://api.openboxes.cloud, choose Anonymous.
Select Close & Load To, then Only Create Connection, so no query writes the key into a sheet. Then load each table query (not LiftApiKey) to a sheet from Queries & Connections, with Load To.

To refresh, use Refresh All on the Data tab.

Excel for Mac is not covered: Microsoft's page for Power Query on the Mac (Import and shape data in Excel for Mac) lists no web source and does not describe the Advanced Editor.

Microsoft's guides: Create a parameter query (Close & Load To, Only Create Connection), Create Power Query formulas in Excel and Create and invoke a custom function.

Scheduled refresh in the Power BI Service#

What Microsoft documents about a scheduled refresh in the Power BI Service, and what it means here:

No gateway should be needed. api.openboxes.cloud is a public web address, and Microsoft documents that a query built on Web.Contents with the RelativePath and Query options, as this recipe is, is one of the cases that can refresh in the Service (Refresh and dynamic data sources).
Turn on "Skip test connection". In the semantic model's data source credentials, choose Anonymous and turn on Skip test connection. Without it the Service tests https://api.openboxes.cloud on its own, which answers 401 without a key, and the refresh can fail before it starts (background).
Microsoft's limits apply: eight scheduled refreshes a day on shared capacity (48 on Premium, Premium Per User or Fabric capacity), and a refresh on shared capacity must finish within two hours (Microsoft).
Once a day is enough. Our figures change once a day, after the daily check (after 04:00 in your account's time zone), so schedule one refresh a day after that.

If the Service will not refresh the recipe, refresh in Power BI Desktop and publish again.

How many requests a refresh uses#

The Reporting API has its own allowance of 600 requests an hour per account: you can use 600 at once, then one more comes back every 6 seconds. It is separate from your plan's API limit.

For each table, the recipe sends one request for the schema, plus one request per 1,000 rows (at least one, even for an empty table). These are minimums: Power BI and Excel can run a query more than once during a single refresh (for example while it works out the columns), and every run costs requests again.

Table	Rows (example: 6,000 products at their locations)	Requests per refresh, at least
`summary`	1	2
`stock-on-hand`	6,000	7
`stock-history`, `grain=monthly` (13 months)	78,000	79
`stock-history`, daily, the default 30 days	about 180,000	about 181
`stock-history`, daily, 90 days	about 540,000	about 541

So keep refreshes small:

Use grain = "monthly" for long history, or a short daily range (from and to).
Filter with locationId when a report is about one location.
Leave out tables the report does not use.

When the allowance runs out, the Reporting API answers 429 with a Retry-After of about 6 seconds. Power Query waits and tries again by itself, up to three times (Microsoft), so a large refresh slows to about one page every 6 seconds, and it can fail if three tries in a row are refused, which is more likely when several large tables refresh at once. Several small tables refreshing at once is fine: a reporting_busy answer is retried the same way and does not count against the allowance.

Who can see the data#

The key is saved inside your file. Anyone who has the .pbix or the Excel workbook can open the LiftApiKey query and read the key, and use it to call the Reporting API. Keep LiftApiKey connection-only (above), so the key is never loaded into the report's data or written into a sheet. Use a key made for this report only, and revoke it on the API keys page if the file goes outside your team. Revoking stops the key within seconds.

Values follow the key. If the key has Include values, stock value, prices and order amounts are in the tables, and they appear to everyone the report is shared with: everyone with access to the report, its semantic model or its workspace in the Power BI Service, everyone it is published to (Publish to web makes it public), and anyone given the file. The recipe has no switch of its own to hide values. For a report you share, use a key without Include values.

If a refresh fails#

The recipe shows the Reporting API's own message. The common ones:

You see	What to do
A credentials or access error	The key is wrong, revoked or expired, `LiftApiKey` is empty, or the account is being closed. Check it, or create a new key.
`OpenBoxes Lift: unknown_parameter` or `invalid_parameter`	An option in the second argument is not one the dataset accepts, or its value is not valid.
`OpenBoxes Lift: range_too_large`	A daily range longer than 92 days, or a monthly range longer than 13 months.
`OpenBoxes Lift: values_changed` or `cursor_expired`	Values were turned on or off for the key, or a day left the history, during the refresh. Refresh again.
`OpenBoxes Lift: account_suspended`	The account is suspended. Reporting data comes back once it is active.
`OpenBoxes Lift: not_found`	The dataset name is misspelled. Use one of the seven names above.
An error after several `429` answers	The allowance is used up. Make the refresh smaller (above) and try again later.

The full list is in Errors.

Not available yet#

The Power BI connector is coming soon: being built, not available yet, with no date.
The recipe has no "Show values" switch: its values follow the key's Include values setting only.
The recipe reads the Reporting API only. It does not read or write your live OpenBoxes data.

Reporting overview: the Reports page, the Reporting API and the scheduled CSV export on one page.
Reporting API: every dataset, parameter and field
Scheduled CSV export: the same datasets as CSV files, with no API key
Looker Studio: using the CSV files in Looker Studio today
Integrations overview: what is available today and what is coming