# Power BI

> Load TimeTracking days, claims, export runs and master data into Power BI with Power Query, a read-only API key and cursor pagination.

URL: https://timetracking.tech/developers/integrations/bi/power-bi
Last updated: 2026-10-24

## 1. Summary

**Status:** Recipe. A bulk export feed for BI is **Planned**.

**Direction:** TimeTracking to Power BI (read-only).

Typical reports: hours per client per month, approval progress (draft/submitted/approved/exported/locked), conflicts, source mix (app, kiosk, mail, import), exported rows per payroll run.

## 2. Mechanism

Power Query calls the REST API with `Web.Contents` and a **read-only** key:

| Endpoint | Table |
|---|---|
| `GET /days?from&to[&status][&updated_since]&limit=500&cursor=` | one row per placement per date (the resolved value) |
| `GET /claims?from&to[&source]&limit=500&cursor=` | raw claims per source (optional) |
| `GET /people?limit=500`, `/clients?limit=500`, `/placements?limit=500` | dimensions (paginated shape `{items, next_cursor}`) |
| `GET /exports/runs?limit=500` | payroll export runs |
| `GET /leave?from&to`, `GET /expenses?from&to` | leave requests and expenses (read keys allowed) |

All return `{data: {items, next_cursor}}`; request the next page with `cursor=<next_cursor>` until it is `null`. Payroll hour types (normal, overtime, ORT) are not exposed to read keys: export preview needs a write key; run details (`GET /exports/runs/{id}`) carry `pay_lines` per run.

## 3. Prerequisites

- Power BI Desktop; for scheduled refresh, Power BI Service with *Anonymous* web credentials and the key in a parameter.
- TimeTracking API key with scope `read` only.

## 4. Entity mapping

| TimeTracking field | Power BI table.column | Notes |
|---|---|---|
| `days[].id` | `FactDay[dayId]` | Key. |
| `work_date`, `placement_id`, `person_id` | `FactDay[date]`, `[placementId]`, `[personId]` | |
| `minutes` | `FactDay[minutes]` | `null` while `has_conflict = true`. |
| `status`, `has_conflict`, `resolution` | `FactDay[status]` … | |
| `approved_by_office_at`, `approved_by_client_at`, `export_run_id`, `updated_at` | `FactDay` | `export_run_id` relates to `FactRun[id]`. |
| `claims[].source`, `minutes`, `confidence`, `created_at` | `FactClaim` | Optional. |
| `placements` items (`id`, `ref`, `role_title`, `person_id`, `client_id`, `active`, `external_ref`) | `DimPlacement` | Join client via `client_id`. |
| `people` items | `DimPerson` | |
| `clients` items | `DimClient` | |
| `exports/runs` items (`id`, `starts_on`, `ends_on`, `row_count`, `created_at`) | `FactRun` | |

Measure: `Hours = DIVIDE(SUM(FactDay[minutes]), 60)`.

## 5. Step-by-step setup

1. Parameters `TTKey` (text, read key) and `FromDate` (date, for example 2026-01-01).
2. Shared function `TTPaged`:

```powerquery
(path as text, query as record) as list =>
let
    Base = "https://app.timetracking.tech/api/v1",
    Page = (cursor as nullable text) =>
        let
            Q = if cursor = null then query else Record.AddField(query, "cursor", cursor),
            Resp = Json.Document(Web.Contents(Base, [RelativePath = path, Query = Q,
                       Headers = [Authorization = "Bearer " & TTKey]]))
        in Resp[data],
    Pages = List.Generate(
        () => Page(null),
        (p) => p <> null,
        (p) => if p[next_cursor] = null then null else Page(p[next_cursor])),
    Items = List.Combine(List.Transform(Pages, each _[items]))
in
    Items
```

3. Query `FactDay`:

```powerquery
let
    Items = TTPaged("days", [
        from = Date.ToText(FromDate, "yyyy-MM-dd"),
        to = Date.ToText(Date.From(DateTime.LocalNow()), "yyyy-MM-dd"),
        limit = "500"]),
    T = Table.FromRecords(Items, {"id","placement_id","person_id","work_date","minutes","status","has_conflict",
        "resolution","approved_by_office_at","approved_by_client_at","export_run_id","updated_at"}, MissingField.UseNull),
    Typed = Table.TransformColumnTypes(T, {{"work_date", type date}, {"minutes", Int64.Type}, {"has_conflict", type logical}})
in
    Typed
```

4. Dimensions: `TTPaged("people", [limit="500"])`, `TTPaged("clients", [limit="500"])`, `TTPaged("placements", [limit="500"])`, `TTPaged("exports/runs", [limit="500"])`, each through `Table.FromRecords`.
5. Relationships: `FactDay[placement_id]` → `DimPlacement[id]`, `DimPlacement[client_id]` → `DimClient[id]`, `FactDay[person_id]` → `DimPerson[id]`, `FactDay[export_run_id]` → `FactRun[id]`.

`RelativePath` + `Query` keep the base URL static, which scheduled refresh in the Service requires.

## 6. Sync schedule and triggering events

Scheduled refresh (for example daily 06:00). With `limit=500` a year of days for 200 placements is about 100 pages, well within 600 requests/minute. For large models, use Power BI incremental refresh with `RangeStart`/`RangeEnd` mapped to `from`/`to`, or keep a history table and refresh only `updated_since` the last refresh.

## 7. Corrections, conflicts and locked periods

- Days change until exported/locked (late claims, approvals, client corrections); `updated_at` and `version` change with them. Refresh a rolling window, or merge on `id` using `updated_since`.
- Conflicted days have `minutes = null` and `has_conflict = true`.
- `GET /days` includes days of inactive placements (unlike `GET /org/week`).

## 8. Testing checklist

- [ ] Monthly totals equal a manual check (`GET /days?from&to` summed).
- [ ] Paging stops (`next_cursor` null) and row counts match.
- [ ] Scheduled refresh succeeds in the Service.
- [ ] The key has only the `read` scope.

## 9. Troubleshooting

| Problem | Fix |
|---|---|
| "This dataset includes a dynamic data source" | Use `RelativePath`/`Query`, not concatenated URLs. |
| `422` "Invalid cursor" | Cursor reused across endpoints; restart paging. |
| `422` on `/days` | `from` and `to` are required (`YYYY-MM-DD`). |
| `401` | Key revoked or its creator lost admin rights. |
| `403` "API key is read-only" on `POST /exports` | Export preview needs a write key. |
| `429` | Stagger refreshes; wait 60 s. |

## 10. Limits and known gaps

- No hour-type split for read keys except per committed run (`GET /exports/runs/{id}` → `pay_lines`). `GET /leave` and `/expenses` return plain arrays filtered by `from`/`to`, not cursor pages.
- Without `limit`/`cursor`/`updated_since`, `GET /people|clients|placements` return the old full-array shape; always pass `limit` for a stable shape.
- A bulk BI feed (Parquet/warehouse export) is Planned.

## 11. Build it with Claude

```text
Write Power Query M for a Power BI model on the TimeTracking API (base https://app.timetracking.tech/api/v1,
Authorization: Bearer <read-only key> as parameter TTKey). Build a generic paging function using
Web.Contents(Base, [RelativePath, Query, Headers]) and List.Generate over {data:{items, next_cursor}} with
cursor=<next_cursor>. Tables: FactDay from GET days?from&to&limit=500 (id, placement_id, person_id, work_date,
minutes, status, has_conflict, resolution, approved_by_office_at, approved_by_client_at, export_run_id,
updated_at), optional FactClaim from GET claims, dimensions from GET people|clients|placements?limit=500, FactRun
from GET exports/runs. Add relationships, DAX measures (hours, approval rate, conflict rate) and incremental refresh.
```