> ## Documentation Index
> Fetch the complete documentation index at: https://docs.xenia.team/llms.txt
> Use this file to discover all available pages before exploring further.

# Connecting Xenia Data to Power BI

> Pull Xenia submissions, task/work-order data, and reporting analytics directly into Microsoft Power BI using the Xenia API

This guide explains how to pull your Xenia operational data — submission records, task/work-order data, and reporting analytics — directly into Microsoft Power BI using the Xenia API. It covers getting your API credentials, the data endpoints available to you, and step-by-step Power BI setup with copy-paste query examples.

<Note>
  **In short:** Xenia exposes a JSON API. Power BI's built-in **Web** connector calls that API using your API key and secret (sent as request headers) and loads the results into your report. No middleware or custom connector is required.
</Note>

## 1. Before you begin

You will need:

| Requirement                              | Notes                                                                                                                                                                                |
| ---------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| **Public API enabled on your workspace** | This is an add-on capability. If you don't see **Settings → Public Integrations** in Xenia, contact your Xenia representative to have it enabled for your workspace.                 |
| **Workspace Owner access**               | Creating API keys requires the *Manage API Access Keys* permission, which is granted to workspace **Owners** by default.                                                             |
| **Power BI Desktop**                     | Free from Microsoft. Power BI Desktop is where you build the connection and report. Publishing to the Power BI Service (for scheduled refresh) is optional and covered in Section 7. |

A few things to know up front:

* **One key = one workspace.** Each API key is permanently tied to the workspace it was created in. It can only read data from that workspace.
* **The key acts as a specific user.** When you create a key you choose a *default user*; every API call behaves as if that user made it. The data the key can see, and the reports it can run, are governed by that user's role and location access. We recommend creating a dedicated integration user with a role that has reporting access and that is assigned to **all** locations, so your Power BI reports see complete data (see Section 3).
* **Credentials are shown once.** The client secret is displayed a single time when the key is created. Save it somewhere secure — it cannot be retrieved later. If it is lost, create a new key.

## 2. Key facts

|                             |                                                                                                                                                                                                            |
| --------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **Production API base URL** | `https://api.xenia.team/api/v1`                                                                                                                                                                            |
| **Authentication**          | Two request headers on every call: `x-client-key` and `x-client-secret` (both required)                                                                                                                    |
| **Format**                  | JSON request bodies and JSON responses                                                                                                                                                                     |
| **Workspace scope**         | Determined automatically by the key — you do not choose it per request                                                                                                                                     |
| **Request timeout**         | Requests are cut off at \~60 seconds (enforced at the load balancer; surfaces as an HTTP 504). Keep each query scoped (e.g. a date range) so it finishes comfortably under that — aim for \< \~55 seconds. |

## 3. Step 1 — Create your API credentials in Xenia

1. In the Xenia web app (`https://app.xenia.team`), open **Settings → Public Integrations**.
2. (Recommended) First create a dedicated user to run the integration — e.g. `powerbi-integration@yourcompany.com`. Give it an Admin- or Owner-level role (so it holds the reporting, task, and dashboard capabilities the endpoints in Section 5 require) and assign it to all locations you want represented in Power BI. A narrower custom role also works, as long as it grants those capabilities.
3. In **Public Integrations**, click to **create a new API key**:
   * Enter your **client key** — an identifier of your choosing (letters, numbers, and hyphens; at least 5 characters), e.g. `powerbi-reporting`. This value is your `x-client-key`.
   * Choose the **default user** the key will act as (the integration user from step 2).
4. When the key is created, Xenia generates the **client secret** and displays it once. Copy the secret now and store it securely (a password manager or your BI team's secrets vault) — it is not shown again.

You now have the two values you'll use in Power BI:

* **Client key** → sent as the `x-client-key` header
* **Client secret** → sent as the `x-client-secret` header

You will also need your **Workspace ID** for the task/work-order endpoints (Section 5). It is the UUID in your Xenia web address bar (e.g. `app.xenia.team/workspace/<workspace-id>/...`); if you're unsure, ask your Xenia contact.

## 4. Step 2 — Verify your credentials (optional but recommended)

Before opening Power BI, confirm the credentials work with a quick command-line test. This returns a small sample of submission records:

```bash theme={null}
curl -X POST "https://api.xenia.team/api/v1/ops/template-submissions" \
  -H "x-client-key: YOUR_CLIENT_KEY" \
  -H "x-client-secret: YOUR_CLIENT_SECRET" \
  -H "Content-Type: application/json" \
  -d '{"fromDate":"2026-01-01","toDate":"2026-01-31","limit":5}'
```

* A JSON response with a `data` array → credentials are working.
* `401 Invalid client credentials` → the key or secret is wrong.
* `403 Forbidden: API key not authorized for this route` → the URL path is wrong (check for typos against Section 5).

## 5. Available data endpoints

All endpoints below return **JSON** and are reachable with your API key. Base URL for every path is `https://api.xenia.team/api/v1`. For the complete request-body and response reference for every endpoint, see the [Data Extraction & Reporting guide](/guides/integrations/data-extraction).

<Note>
  **Permissions matter.** Some endpoints require the key's user to hold a specific permission or workspace capability (last column). If the user lacks it, the call returns a permission error even though the credentials are valid — this is why we recommend a well-privileged integration user (Section 3). Exact capability names in the role editor may differ slightly from the labels used here; if in doubt, give the integration user an Admin/Owner role, which includes them all.
</Note>

### Submission data

| What you get                                                                                             | Method & path                           | Response shape                                                 | Requires              |
| -------------------------------------------------------------------------------------------------------- | --------------------------------------- | -------------------------------------------------------------- | --------------------- |
| **Submission records** (individual template/checklist submissions, optionally with their answered items) | `POST /ops/template-submissions`        | `{ data: [ …rows… ], meta: { totalCount, count } }` — see note | Valid key + workspace |
| **Submission counts by status**                                                                          | `POST /ops/submissions-count-by-status` | `{ data: { "<status>": { count }, …, total } }`                | Valid key + workspace |

`/ops/template-submissions` **request body** (all optional): `fromDate`, `toDate` (date range on last update), `checklists` (list of template IDs), `statuses` (list), `locations` (list), `users` (list), `searchText`, `offset` (default 0), `limit` (default returns all — set a value for paging), `includeItems` (default `true`; set `false` to exclude the per-question answer detail for a lighter payload).

<Note>
  `meta.totalCount` is the total number of matching submissions — use it for paging (Section 8). `meta.count` is **not** a page count; it's a per-status breakdown object (the same figures as `submissions-count-by-status`).
</Note>

### Task & work-order data

For these three, the **Workspace ID is part of the URL path** (it must match your key's workspace). Replace `{workspaceId}` with your Workspace ID.

| What you get                          | Method & path                                              | Response shape                | Requires                                                                        |
| ------------------------------------- | ---------------------------------------------------------- | ----------------------------- | ------------------------------------------------------------------------------- |
| **Task / work-order list** (filtered) | `POST /ops/workspaces/{workspaceId}/tasks/list`            | `{ data: { count, rows } }`   | View Tasks or View Work Orders                                                  |
| **Task catalog** (list + counts)      | `POST /ops/workspaces/{workspaceId}/tasks/catalog`         | `{ data: { counts, tasks } }` | View Tasks or View Work Orders                                                  |
| **Single task details**               | `GET /ops/workspaces/{workspaceId}/tasks/{taskId}/details` | `{ data: { …task… } }`        | Valid key — the task must be visible to the key's user (by location/visibility) |

<Note>
  The `tasks/list` response also includes sibling top-level keys alongside `data` — `statusObject`, `notification`, and a `meta` with pagination (`current_page`, `last_page`, `per_page`, `total`); `tasks/catalog` and `tasks/{id}/details` return just `data` + `meta`. You can ignore the extras — `data` holds the records you want.
</Note>

### Reporting analytics (aggregated)

These return pre-aggregated numbers (good for KPI tiles and trend charts). All are POST, and all require the key's user to have the View Reporting capability (internally `CAN_VIEW_REPORTING`). Workspace is taken from the key — do **not** put it in the path.

| What you get                            | Method & path                                                 | Response shape                                        |
| --------------------------------------- | ------------------------------------------------------------- | ----------------------------------------------------- |
| Task count by status                    | `POST /ops/reports/tasks/count-by-status`                     | `{ data: { "<status>": { count, trend? } } }`         |
| Task count by assignee                  | `POST /ops/reports/tasks/count-by-assignees`                  | `{ data: [ { id, assignee, count, assigneeType } ] }` |
| Task count by category                  | `POST /ops/reports/tasks/count-by-categories`                 | `{ data: [ { id, category, color, count } ] }`        |
| Weekly completion                       | `POST /ops/reports/tasks/weekly-completion`                   | `{ data: { items, weeks, endDate, page } }`           |
| Schedule completion by location         | `POST /ops/reports/tasks/schedule-completion-by-location`     | `{ data: { charts, summary } }`                       |
| On-time vs. late submission by location | `POST /ops/reports/tasks/on-time-late-submission-by-location` | `{ data: { charts, summary } }`                       |
| Daily compliance                        | `POST /ops/reports/tasks/daily-compliance`                    | `{ data: { charts } }`                                |

Body shape — and, importantly, filter field names — differ by endpoint. An unrecognized field name is silently ignored (no error, just unfiltered results), so match these exactly:

* `count-by-status`, `count-by-assignees`, `count-by-categories` take the filter fields at the **top level** of the body (e.g. `{ "locations": [...], "fromDate": "2026-01-01", "toDate": "2026-01-31" }`). `count-by-assignees` also accepts `assigneeType = "user" | "team" | "location"`. An empty body `{}` returns everything.
* `weekly-completion`, `schedule-completion-by-location`, `on-time-late-submission-by-location` wrap filters under a `filters` key: `{ "filters": { "dateRange": [...], "location": [...], "projects": [...] } }`.
* `daily-compliance` takes `{ "filters": { "date": "YYYY-MM-DD", "project": [...], "location": [...], "onlyMissed": false } }`.

### Grid Reports

Requires the **Advanced Dashboards** capability plus View Reporting. (In some role editors "Advanced Dashboards" may appear as "Advance Dashboards" — it is the same capability.)

| What you get                 | Method & path                                     | Response                                 |
| ---------------------------- | ------------------------------------------------- | ---------------------------------------- |
| List your saved grid reports | `GET /mgt/grid-reports`                           | JSON `{ data: { gridReports: [...] } }`  |
| Export a grid report view    | `GET /mgt/grid-report-views/{viewId}/spreadsheet` | `.xlsx` file (binary download, not JSON) |

The grid-report **spreadsheet** endpoint returns an Excel file, not JSON. Power BI can import it with **Get Data → Web →** then *Excel* handling, but for live/refreshable reporting the JSON endpoints above are the better choice.

### Not available through this API

To keep this guide accurate, note what is **not** offered so you don't build against it:

* There is **no OData feed** and **no dedicated "Xenia" Power BI connector** — use the generic **Web** connector as described here.
* There is **no single "export every raw row" endpoint** beyond `template-submissions`; pull the datasets you need from the endpoints above.
* The **computed cells of a grid report** are only available as the Excel export above (there is no JSON endpoint for a rendered grid via the API key).

## 6. Step 3 — Connect from Power BI

The approach is the same for every endpoint: Power BI's **Web** connector sends your two credential headers and parses the JSON response. Because the API uses custom headers (not one of Power BI's built-in auth types), and because Power BI sends **POST** requests anonymously, you will set the connection's credential type to **Anonymous** — the actual authentication travels in the headers.

### 6.1 Store your credentials as parameters (recommended)

So the secret isn't scattered across queries:

1. In Power BI Desktop: **Home → Transform data** to open the Power Query Editor.
2. **Manage Parameters → New Parameter**. Create two **Text** parameters:
   * `ClientKey` — paste your client key
   * `ClientSecret` — paste your client secret
3. Optionally create a `WorkspaceId` parameter with your Workspace ID.

### 6.2 Example A — Submission records (POST, the most common case)

1. **Home → New Source → Blank Query**.
2. Open the **Advanced Editor** and paste (adjust the dates):

```powerquery theme={null}
let
    BaseUrl = "https://api.xenia.team",
    Body = Json.FromValue([
        fromDate = "2026-01-01",
        toDate   = "2026-01-31",
        limit    = 5000,
        offset   = 0
    ]),
    Response = Web.Contents(
        BaseUrl,
        [
            RelativePath = "/api/v1/ops/template-submissions",
            Headers = [
                #"Content-Type"  = "application/json",
                #"x-client-key"  = ClientKey,
                #"x-client-secret" = ClientSecret
            ],
            Content = Body
        ]
    ),
    Parsed = Json.Document(Response),
    Rows   = Parsed[data],
    Table  = Table.FromList(Rows, Splitter.SplitByNothing(), {"Submission"})
in
    Table
```

3. Click **Done**. When prompted for credentials, choose **Anonymous** and **Connect**.
4. You'll see a one-column table of records. Click the **expand** (⇔) icon on the column header to flatten the submission fields into columns.
5. **Close & Apply**.

<Note>
  **Why `RelativePath` instead of the full URL?** Keeping a static `BaseUrl` and putting the path in `RelativePath` is what lets **scheduled refresh in the Power BI Service** work reliably. Passing a fully dynamic URL to `Web.Contents` is a well-known cause of refresh failures in the Service.
</Note>

### 6.3 Example B — A GET endpoint (list grid reports)

```powerquery theme={null}
let
    BaseUrl = "https://api.xenia.team",
    Response = Web.Contents(
        BaseUrl,
        [
            RelativePath = "/api/v1/mgt/grid-reports",
            Headers = [
                #"x-client-key"  = ClientKey,
                #"x-client-secret" = ClientSecret
            ]
        ]
    ),
    Parsed = Json.Document(Response),
    Reports = Parsed[data][gridReports],
    Table   = Table.FromList(Reports, Splitter.SplitByNothing(), {"Report"})
in
    Table
```

Same as before: choose **Anonymous** when prompted, then expand the column.

### 6.4 Example C — Task list (POST, Workspace ID in the path)

```powerquery theme={null}
let
    BaseUrl = "https://api.xenia.team",
    Body = Json.FromValue([ /* your filter criteria, or leave empty: */ ]),
    Response = Web.Contents(
        BaseUrl,
        [
            RelativePath = "/api/v1/ops/workspaces/" & WorkspaceId & "/tasks/list",
            Headers = [
                #"Content-Type"  = "application/json",
                #"x-client-key"  = ClientKey,
                #"x-client-secret" = ClientSecret
            ],
            Content = Body
        ]
    ),
    Parsed = Json.Document(Response),
    Rows   = Parsed[data][rows],
    Table  = Table.FromList(Rows, Splitter.SplitByNothing(), {"Task"})
in
    Table
```

`WorkspaceId` here is a static parameter, so concatenating it into `RelativePath` is fine for scheduled refresh. If the Power BI Service ever objects to a "dynamic data source," hard-code the full path instead.

### 6.5 Handling the "keyed object" responses

A few endpoints (e.g. `count-by-status`, `submissions-count-by-status`) return data as an **object keyed by status** rather than an array. Convert it with `Record.ToTable`:

```powerquery theme={null}
let
    BaseUrl = "https://api.xenia.team",
    Body = Json.FromValue([]),   // empty body = no filter (all statuses); count-by-* filters go at the top level
    Response = Web.Contents(
        BaseUrl,
        [
            RelativePath = "/api/v1/ops/reports/tasks/count-by-status",
            Headers = [
                #"Content-Type"  = "application/json",
                #"x-client-key"  = ClientKey,
                #"x-client-secret" = ClientSecret
            ],
            Content = Body
        ]
    ),
    Parsed = Json.Document(Response),
    AsTable = Record.ToTable(Parsed[data])   // columns: Name (status), Value (record with count)
in
    AsTable
```

## 7. Scheduled refresh in the Power BI Service

To refresh automatically in the cloud after **Publish**:

1. In the Power BI Service, open the dataset's **Settings → Data source credentials**.
2. **Edit credentials →** set the authentication method to **Anonymous** (the key/secret headers are already inside the query).
3. Configure **Scheduled refresh** as normal.

Notes:

* Building queries with a static `BaseUrl` + `RelativePath` (as shown) is important for Service refresh to succeed.
* **Security:** because authentication is via headers, your client secret is stored inside the dataset definition. Treat the `.pbix` file and the published dataset as sensitive, and limit who can edit/export them. If a secret is ever exposed, create a new key in **Public Integrations** and delete the old one.

## 8. Working with large datasets

* **Scope every pull.** Always send a `fromDate`/`toDate` (or other filters) so a query returns in well under the \~60-second request limit (aim for \< \~55s).
* **Page with `offset`/`limit`.** For `template-submissions`, request a page at a time (e.g. `limit = 5000`) and increment `offset`. The response's `meta.totalCount` tells you the total so you know when to stop. For very large historical loads, consider Power BI **incremental refresh** partitioned by date.
* **Don't rely on a longer client timeout.** The server cuts requests at \~60 seconds regardless of Power BI's own setting, so raising the Power Query timeout won't rescue a slow query — narrow the query instead. (For reference, Power BI's own default request timeout is 100 seconds.)
* **Lighter payloads.** For `template-submissions`, set `includeItems = false` if you only need submission-level fields (status, dates, location, submitter) and not every answered question.

## 9. Troubleshooting

| Symptom                                                | Likely cause                                                                                        | Fix                                                                                          |
| ------------------------------------------------------ | --------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------- |
| `401 Invalid client credentials`                       | Wrong key/secret, or the key was deactivated                                                        | Re-check the values; create a new key if needed                                              |
| `401 Client does not belong to this workspace`         | The Workspace ID in the URL path doesn't match the key's workspace                                  | Use the Workspace ID that the key belongs to (or omit it where the endpoint doesn't need it) |
| `403 Forbidden: API key not authorized for this route` | The path/method isn't one of the API-key-enabled endpoints, or there's a typo                       | Match the path exactly against Section 5                                                     |
| Permission / access error despite valid key            | The key's default user lacks the required permission (e.g. *View Reporting*, *Advanced Dashboards*) | Grant the integration user the needed role/capability                                        |
| `504` (or `503`) / request times out                   | The query took longer than the \~60s server limit                                                   | Narrow the date range, add filters, or page the results                                      |
| Scheduled refresh fails in the Service                 | Fully dynamic URL, or credentials not set                                                           | Use the `BaseUrl` + `RelativePath` pattern; set data-source credentials to **Anonymous**     |

## 10. Support

For help enabling the Public API on your workspace, questions about which data you can access, or issues connecting, contact your Xenia representative.

***

*This guide describes Xenia's production API as of July 2026. Endpoint availability depends on your workspace's enabled capabilities.*