--- title: "Connect Demo Data to Power BI" slug: "/help/api/connect-to-power-bi" tags: ["demo api","api","demos","demos api"] topic: "api" last_updated: "2026-06-17T14:41:06.038Z" --- > Pull your Walnut demo-session analytics into Microsoft Power BI, build live dashboards (daily trends, completion/bounce rates, top demos), and keep them refreshing automatically. **Method A (native Web connector) is the recommended path** for almost everyone. A Python alternative is included at the end. > > ⚠️ The generic samples on the Code Samples help page are written for a **terminal**, not Power BI. The Python ones end in `print()` and the Node/Apps Script/Zapier ones don't run in Power BI at all - so none of them load data in Power BI as-is. Use the Power-BI-specific code on this page instead. Each query below returns a finished **table** with no manual clicking. > ## Before you start | What | Value | | --- | --- | | Tool | Power BI Desktop (Windows). Free download from Microsoft. | | API key | Your Walnut Customer API key (`wlt_...`), provided by your Walnut account team. Keep it secret. | | Base URL | `https://customer-api.teamwalnut.com` | | Auth | Send your key in the **`x-api-key`** request header. This is a custom header, **not** `Authorization: Bearer`. | | Data scope | Read-only demo-session analytics. Up to 1,000 rows per request (paginate for more). | ### The three endpoints | Endpoint | Auth | Best for in Power BI | | --- | --- | --- | | `/health` | none | A connectivity test before you add the key. Returns `{ "status": "ok" }`. | | `/demo-sessions` | `x-api-key` | Row-level session data, or daily/grouped trends via `group_by`. | | `/demo-sessions/summary` | `x-api-key` | A single pre-aggregated KPI snapshot (totals, averages, top 10 demos/countries). | > Tip: choose the endpoint by what you're building. **Trends over time** -> `/demo-sessions?group_by=date`. **A KPI header / scorecard** -> `/demo-sessions/summary`. **A detailed table you'll slice yourself** -> `/demo-sessions` raw rows. > --- ## Method A - Native Web connector (recommended) No extra software, and it refreshes in the Power BI Service with **no data gateway** required. ### How to add a query 1. Open Power BI Desktop -> **Home -> Get data -> Web** -> choose **Web** -> **Connect**. 2. In the *From Web* dialog, select **Advanced**, put `https://customer-api.teamwalnut.com/health` in the URL, and click **OK**. If prompted, choose **Anonymous**. If you see `status: ok`, your connection works - now you're ready for the real queries. 3. For each recipe below: **Home -> Get data -> Blank Query -> Advanced Editor**, delete everything, and paste the M code. Replace `YOUR_API_KEY` with your key, click **Done**, then **Close & Apply**. > Why paste M instead of using the dialog boxes: the M code sends the `x-api-key` header on **every** request and uses the `RelativePath` + `Query` structure that keeps **scheduled refresh** working in the Power BI Service. Building one long URL in the dialog can trigger a "dynamic data source" error on refresh. > --- ## Recipe 1 - Daily trend (sessions per day) The most common ask. This returns a clean two-column table (`Date`, `Sessions`) ready to chart - no manual unpivoting. ``` let Raw = Json.Document( Web.Contents( "https://customer-api.teamwalnut.com", [ RelativePath = "demo-sessions", Query = [ group_by = "date", start_date = "2026-01-01", end_date = "2026-06-17", user_type = "external" ], Headers = [ #"x-api-key" = "YOUR_API_KEY" ] ] ) ), Daily = Record.ToTable(Raw[data]), Renamed = Table.RenameColumns(Daily, {{"Name", "Date"}, {"Value", "Sessions"}}), Typed = Table.TransformColumnTypes(Renamed, {{"Date", type date}, {"Sessions", Int64.Type}}) in Typed ``` Then insert a **Line chart**: `Date` on the X-axis, `Sessions` as the value. How it works: the API returns counts keyed by date (`{ "data": { "2026-03-01": 669, ... } }`). `Record.ToTable` turns that record straight into rows, so you skip the drill-in/unpivot dance. Variations: - `group_by = "date,user_type"` splits each day by external/internal. The keys come back pipe-delimited (`2026-03-01|external`); after `Record.ToTable`, use **Split Column by Delimiter** (`|`) on the `Name` column. - `group_by` also accepts `viewer_type`, `demo_id`, `is_embed`. - Days with zero sessions are **omitted**. For a gap-free axis, join against a Date table later. --- ## Recipe 2 - KPI scorecard (totals, averages, top demos) For an executive snapshot in one call, use `/demo-sessions/summary`. This example returns the **Top 10 demos** as a table: ``` let Raw = Json.Document( Web.Contents( "https://customer-api.teamwalnut.com", [ RelativePath = "demo-sessions/summary", Query = [ start_date = "2026-01-01" ], Headers = [ #"x-api-key" = "YOUR_API_KEY" ] ] ) ), TopDemos = Table.FromRecords(Raw[top_demos]), Typed = Table.TransformColumnTypes(TopDemos, {{"demo_name", type text}, {"sessions", Int64.Type}}) in Typed ``` The same response also contains, for Card visuals and other tables: - `Raw[totals]` -> `sessions`, `completed`, `bounced` - `Raw[averages]` -> `duration_seconds`, `interactions`, `screen_views` - `Raw[by_user_type]`, `Raw[by_viewer_type]`, `Raw[by_embed]` -> wrap with `Record.ToTable(...)` for pie/bar charts - `Raw[top_countries]` -> `Table.FromRecords(Raw[top_countries])` For a single-row KPI table, replace the last steps with: `Totals = Table.FromRecords({ Raw[totals] })`. Calculate rates as DAX measures, e.g. `Completion Rate = DIV([completed], [sessions])`. > If no date filter is given, the summary auto-scopes to the **last 12 months**. > --- ## Recipe 3 - Raw session table (with pagination) For a detailed table you'll slice yourself. `Table.FromRecords` builds the table directly: ``` let Raw = Json.Document( Web.Contents( "https://customer-api.teamwalnut.com", [ RelativePath = "demo-sessions", Query = [ limit = "1000", fields = "id,demo_name,demo_owner_name,started_at,user_type,viewer_type,is_completed,is_bounced,interaction_count,screen_views_count,geo_country_name" ], Headers = [ #"x-api-key" = "YOUR_API_KEY" ] ] ) ), Sessions = Table.FromRecords(Raw[data]) in Sessions ``` **Going past 1,000 rows.** `limit` maxes out at 1,000, so larger pulls need `offset` pagination. This fetches pages until an empty page signals the end, then combines them into one table: ``` let GetRows = (offset as number) as list => Json.Document( Web.Contents( "https://customer-api.teamwalnut.com", [ RelativePath = "demo-sessions", Query = [ limit = "1000", offset = Text.From(offset) ], Headers = [ #"x-api-key" = "YOUR_API_KEY" ] ] ) )[data], Pages = List.Generate( () => [ Offset = 0, Rows = GetRows(0) ], each List.Count([Rows]) > 0, each [ Offset = [Offset] + 1000, Rows = GetRows([Offset] + 1000) ], each [Rows] ), AllRows = List.Combine(Pages), Sessions = Table.FromRecords(AllRows) in Sessions ``` Note: offset pagination slows at very high offsets; prefer date-range filters to keep pulls small. --- ## Refresh: build once, then automatic After **Close & Apply**, the whole query (URL, header, transforms) is saved in the `.pbix`. After that it's just refresh: - **In Power BI Desktop:** click **Refresh** (Home tab) to re-pull the latest data. - **In the Power BI Service:** publish, then **Dataset -> Settings -> Scheduled refresh** and set a cadence. Under **Data source credentials**, set the source to **Anonymous** (the key is in the header). **No gateway required** with Method A. - **If your key is rotated:** update it in the `Headers` line of each query (or Data source settings) - no rebuild. Key changes can take up to ~5 minutes to apply. --- ## Method B - Python (alternative) Use only if your team specifically prefers pandas. Two caveats vs. Method A: Power BI's Python connector imports **pandas DataFrames left in memory** (it ignores `print()` output), and **scheduled refresh with Python requires a personal data gateway** on an always-on machine. > This is exactly why the generic Python samples on the Code Samples page fail in Power BI: they end in `print()` / a loop and never assign a DataFrame, so Power BI has nothing to load. The scripts below end in a DataFrame, so they work. Also: paste with **every top-level line flush against the left margin** (no leading spaces) - a stray indent causes `IndentationError: unexpected indent`. Only the lines inside `(...)` should be indented. > **Sessions** (in **Get data -> Python script**): ```python import requests, pandas as pd API_KEY = "YOUR_API_KEY" BASE = "https://customer-api.teamwalnut.com" resp = requests.get( f"{BASE}/demo-sessions", headers={"x-api-key": API_KEY}, params={"limit": 1000, "user_type": "external"}, ) resp.raise_for_status() sessions = pd.DataFrame(resp.json()["data"]) ``` **Summary** (multiple DataFrames - Power BI lets you pick which to load): ```python import requests, pandas as pd API_KEY = "YOUR_API_KEY" BASE = "https://customer-api.teamwalnut.com" resp = requests.get( f"{BASE}/demo-sessions/summary", headers={"x-api-key": API_KEY}, params={"start_date": "2026-02-01", "end_date": "2026-02-28"}, ) resp.raise_for_status() summary = resp.json() totals = pd.DataFrame([summary["totals"]]) top_demos = pd.DataFrame(summary["top_demos"]) top_countries = pd.DataFrame(summary["top_countries"]) ``` --- ## Troubleshooting **"Access to the resource is forbidden" / HTTP 403, or 401 Unauthorized** The request reached Walnut but auth was rejected. Work through these in order: 1. **Test `/health` first** (no key). If it returns `status: ok`, connectivity is fine and the issue is the key or how it's being sent. If `/health` itself fails, something in front of the API (corporate proxy, VPN, IP allowlist) is blocking - involve your IT/network team. 2. **Clear cached credentials** - the most common cause when `/health` works. Power BI can cache an "Anonymous, no header" credential from your health test and then send the authed call without the key. Fix: **File -> Options and settings -> Data source settings -> select `customer-api.teamwalnut.com` -> Clear Permissions**, then retry and choose **Anonymous**. The M queries above avoid this because the header is on every request. 3. **Check the key:** active, not truncated, no trailing space? Key changes take up to ~5 minutes to apply. If it still fails, confirm with your Walnut account team that the key is valid. **Python: `IndentationError: unexpected indent`** - a top-level line was pasted with leading spaces. Make `import`, `API_KEY`, `BASE`, `resp`, and the DataFrame lines all flush left. **Python: query runs but no data loads** - the script didn't assign a pandas DataFrame. Power BI only imports DataFrames, not `print()` output. **HTTP 400 Bad Request** - invalid date (`YYYY-MM-DD`), unknown `fields` name, or malformed parameter. **HTTP 429 Too Many Requests** - rate limit exceeded (50 req/sec sustained, 100 burst). Reduce refresh frequency or page size. **"Dynamic data source" error on scheduled refresh** - the query built one long URL string. Use the `RelativePath` + `Query` form shown above. **Only 1,000 rows came back** - that's the per-request maximum. Use the pagination query in Recipe 3, or narrow with `start_date` / `end_date`. --- ## Useful fields for Power BI `/demo-sessions` exposes 29 fields. Common ones for reporting: | Field | Meaning | | --- | --- | | `started_at` | Session start (ISO 8601). Your time axis. | | `demo_name`, `demo_owner_name` | Which demo, and who owns it. | | `user_type` | `external` (prospects) vs `internal` (your team). | | `viewer_type` | `prospect`, `owner`, `colleague_member`, `colleague_non_member`. | | `is_completed` | Reached the end of the demo. Completion rate = completed / total. | | `is_bounced` | Left after the first screen. Bounce rate = bounced / total. | | `interaction_count`, `screen_views_count` | Engagement depth. | | `is_embed` | Viewed in a website embed vs direct link. | | `geo_country_name`, `geo_city` | Geography (great for map visuals). | | `lead_form_submitted` / `lead_form_opened` | Lead-form conversion. | See the **Customer Data API - Developer Documentation** page for the full 29-field reference and definitions. --- ## Security - **Never share or paste a live API key** into chats, emails, screenshots, or shared docs. Use `YOUR_API_KEY` placeholders, and give each consumer their own key. - The key is embedded in the `.pbix` / published dataset, so treat the file as containing a secret. - If a key is ever exposed, contact your Walnut account team to **rotate** it (no self-service rotation today).