Power BI

Overview

The end-to-end flow: create an API key in the portal, use a Power Query blank query with a short M script that sends X-API-Key, then add visuals on a new report page. Do not use the Web / Web API picker — current Power BI Desktop does not expose a header field there.

Portal → API key (devices scoped)
         ↓
Power BI Desktop → Get data → Blank query → M (Web.Contents + X-API-Key)
         ↓
Flat table (SKU, Physical, Location, …)
         ↓
Report visuals (table, bar chart, KPI cards)

Prerequisites

• Power BI Desktop (Windows). Sign in is optional for local development; you need a Power BI licence only when publishing to the service.
• A PartLogic API key with access to the devices whose stock you want in the report.
• Outbound HTTPS access to the Stock API host documented in the API Reference.
  PartLogic Stock API — /api/stockCopy

Get an API key

If you already have a key, skip to Connect in Power BI. Otherwise follow the Portal API key guide (or open Integrations):

1. Sign in to the portal → Integrations → New API Key.
2. Name the key, select the devices to include, and set optional expiry.
3. Copy the secret immediately — it is shown once. Store it in a password manager; you will paste it into Power Query (or a parameter) in the next section.

Connect (blank query)

Start from a blank report (File → New). You need the Power Query Editor for about two minutes.

Quick start (paste key in script)

1. Home → Get data → Blank query (in the same menu as Web — do not pick Web).

   

2. Power BI opens an M code editor — either in the Get Data (Power Query) dialog (with Blank query selected on the left) or in the Power Query Editor after you continue. Replace the default let … Source = "" … block with the script below. If you land in the Power Query Editor first, use Home → Advanced Editor instead.

   

3. Change PASTE_YOUR_API_KEY_HERE in the script to your real key (keep the quotes).
4. Click Create or Done, then Next if the wizard continues. If Power BI asks about privacy / access to the web, approve access to the PartLogic API host.
5. You should see a preview table with columns such as SKU and Physical. Rename the query (for example PartLogic Stock or Stock List) in the left Queries pane.

   

6. Home → Close & Apply.

let
    ApiKey = "PASTE_YOUR_API_KEY_HERE",
    Response = Json.Document(
        Web.Contents(
            "https://partlogic-api-stock-dwbbcthkhfb0ceca.ukwest-01.azurewebsites.net/api/stock",
            [Headers = [#"X-API-Key" = ApiKey]]
        )
    ),
    Rows =
        if Value.Is(Response, type list) then Response
        else if Record.HasFields(Response, "rows") then Response[rows]
        else error "Unexpected JSON from /api/stock",
    #"Converted to Table" = Table.FromList(Rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded stock" = Table.ExpandRecordColumn(
        #"Converted to Table",
        "Column1",
        {"id", "DeviceName", "SKU", "GTIN", "DeviceList", "Description", "MinQty", "MaxQty", "Physical", "Location", "LocationAlias"},
        {"id", "DeviceName", "SKU", "GTIN", "DeviceList", "Description", "MinQty", "MaxQty", "Physical", "Location", "LocationAlias"}
    )
in
    #"Expanded stock"

Production: store the key in a parameter

After the quick start works, move the key out of the script: Home → Manage Parameters → New → name PartLogicApiKey (Text) → paste your key. Then use this script (replace the quick-start script in Advanced Editor):

let
    ApiKey = PartLogicApiKey,
    Response = Json.Document(
        Web.Contents(
            "https://partlogic-api-stock-dwbbcthkhfb0ceca.ukwest-01.azurewebsites.net/api/stock",
            [Headers = [#"X-API-Key" = ApiKey]]
        )
    ),
    Rows =
        if Value.Is(Response, type list) then Response
        else if Record.HasFields(Response, "rows") then Response[rows]
        else error "Unexpected JSON from /api/stock",
    #"Converted to Table" = Table.FromList(Rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded stock" = Table.ExpandRecordColumn(
        #"Converted to Table",
        "Column1",
        {"id", "DeviceName", "SKU", "GTIN", "DeviceList", "Description", "MinQty", "MaxQty", "Physical", "Location", "LocationAlias"},
        {"id", "DeviceName", "SKU", "GTIN", "DeviceList", "Description", "MinQty", "MaxQty", "Physical", "Location", "LocationAlias"}
    )
in
    #"Expanded stock"

Endpoint used by both scripts: PartLogic Stock API — /api/stock. PartLogic does not accept the key as a query parameter (?X-API-Key=...).

Excel only (legacy Web dialog)

Excel's Data → From Web → Advanced still has HTTP request header parameters. Power BI Desktop's Web menu item does not — use blank query here. See the API Reference for Excel steps.

Shape the data

If you used the M script above, shaping is already done — skip to Build a report. The scripts handle both a raw JSON array and an envelope { "rows": [ ... ] }. Manual steps below apply only if you connected another way.

If the root is a List

1. With the list selected: Convert → Into Table.
2. Click the expand icon on Column1 → select all fields → OK. Uncheck "Use original column name as prefix" if offered.
3. Set appropriate types (Transform → Detect Data Type or set Physical, MinQty, MaxQty to Whole Number).

If the root is a Record with rows

1. Click into the rows field (or use Record tools → expand).
2. Convert → Into Table, then expand Column1 as above.

Rename the query to PartLogic Stock, then Home → Close & Apply.

Useful calculated column (optional)

Flag lines below minimum stock for conditional formatting or filters:

[Physical] < [MinQty]

Build a report

After Close & Apply, the PartLogic Stock table appears in the Fields pane. On a blank canvas page:

• Table — Drag SKU, Description, Location, Physical, MinQty. Sort by Physical ascending to surface low stock.
• Clustered bar chart — Axis: SKU or Description; Values: sum of Physical. Add a Location slicer if you have multiple sites.
• Card — Total units: SUM(Physical). Optional second card: count of rows where Physical < MinQty using a measure or the BelowMin column.
• Conditional formatting — On the table's Physical column, format by rules (e.g. red when below MinQty).

Column definitions (GTIN, DeviceName, LocationAlias, etc.) are listed in the glossary and OpenAPI spec.

Refresh

• Desktop: Home → Refresh re-runs the query against the live API.
• Privacy levels: If refresh fails with a formula firewall error, open File → Options and settings → Options → Privacy and set both PartLogic and your organisation to Organizational, or combine data in Power Query before merging with other sources.
• Power BI Service: After publish, scheduled refresh needs a gateway if the dataset uses Web.Contents to a custom URL. Configure the data source on the gateway with the same X-API-Key header (or parameter prompt). DirectQuery is not required for typical stock snapshots — Import mode with a schedule is usually enough.

Troubleshooting

Full status codes and header rules: API Reference.