Skip to content
Skills Library
Data & analytics · Analysis

dataverse-query

/dataverse-query

Reading and analyzing Microsoft Dataverse records with the Python SDK, the dataverse CLI, or MCP tools: filtered reads, paging, lookups and $expand, SQL and FetchXML joins and aggregates, $apply, QueryBuilder, pandas DataFrames, and Jupyter notebook exploration. USE FOR: query Dataverse, read records, list records, filter, count, aggregate, group by, join tables, FetchXML, client.query.sql, $apply, $expand, formatted values, lookup display names, QueryBuilder, export to DataFrame or CSV, analyze data in pandas, Jupyter notebook, paging large tables. DO NOT USE FOR: creating, updating, deleting

Licence
MIT
Last repository change
27.10.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
4
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 27.10.2026, without references to retired models or APIs.

Licence

MIT, open: use and modify while retaining author attribution.

What it does and when to use it

Reading and analyzing Microsoft Dataverse records with the Python SDK, the dataverse CLI, or MCP tools: filtered reads, paging, lookups and $expand, SQL and FetchXML joins and aggregates, $apply, QueryBuilder, pandas DataFrames, and Jupyter notebook exploration. USE FOR: query Dataverse, read records, list records, filter, count, aggregate, group by, join tables, FetchXML, client.query.sql, $apply, $expand, formatted values, lookup display names, QueryBuilder, export to DataFrame or CSV, analyze data in pandas, Jupyter notebook, paging large tables. DO NOT USE FOR: creating, updating, deleting

Original author description

Reading and analyzing Microsoft Dataverse records with the Python SDK, the dataverse CLI, or MCP tools: filtered reads, paging, lookups and $expand, SQL and FetchXML joins and aggregates, $apply, QueryBuilder, pandas DataFrames, and Jupyter notebook exploration. USE FOR: query Dataverse, read records, list records, filter, count, aggregate, group by, join tables, FetchXML, client.query.sql, $apply, $expand, formatted values, lookup display names, QueryBuilder, export to DataFrame or CSV, analyze data in pandas, Jupyter notebook, paging large tables. DO NOT USE FOR: creating, updating, deleting

How it works

  1. When to use
  2. Choose the read surface
  3. CLI reads
  4. SDK setup
  5. Field name casing
  6. Read records
  7. SQL — client.query.sql()
  8. FetchXML — client.query.fetchxml()
  9. Raw Web API: $apply and N:N $expand
  10. QueryBuilder and DataFrames
Original SKILL.md headings · 275 lines, 1,577 words
SKILL.md preview
---
name: dataverse-query
description: >
  Reading and analyzing Microsoft Dataverse records with the Python SDK, the dataverse CLI, or MCP tools:
  filtered reads, paging, lookups and $expand, SQL and FetchXML joins and aggregates,
  $apply, QueryBuilder, pandas DataFrames, and Jupyter notebook exploration.
  USE FOR: query Dataverse, read records, list records, filter, count, aggregate, group by,
  join tables, FetchXML, client.query.sql, $apply, $expand, formatted values, lookup display names,
  QueryBuilder, export to DataFrame or CSV, analyze data in pandas, Jupyter notebook, paging large tables.
  DO NOT USE FOR: creating, updating, deleting, or importing records (use dataverse-data),
  table, column, relationship, form, or view definitions (use dataverse-metadata),
  solution export or deployment (use dataverse-solution).
---

# Dataverse Queries

Read, filter, join, aggregate, and analyze Microsoft Dataverse records.

> **Python and the `dataverse` CLI only.** Do not script Dataverse with Node.js or JavaScript — see the hard rules in **dataverse-overview**.

## When to use

- Answering questions about data ("how many", "which has the most", "show me ...")
- Exporting records to CSV or a DataFrame
- Interactive analysis in notebooks
- Spot-checking data after an import

| Need | Use instead |
|---|---|
| Create, update, delete, import records | **dataverse-data** |
| Tables, columns, relationships, full schema inspection | **dataverse-metadata** |
| Export or deploy solutions | **dataverse-solution** |

**Always query the live Dataverse environment.** Do not answer from local copies, cached files, or the source database — Dataverse is the source of truth.

---

## Choose the read surface

**Fast path:** if `dataverse auth who` shows an active profile, query straight away with the CLI — no `.env`, `auth.py`, pip, or PAC needed for reads.

MCP, the CLI, and the SDK all handle auth and retry. Pick by the shape of the read:

| User asks... | Approach | Why |
|---|---|---|
| Simple filter ("show me open tickets") | MCP `read_query`, CLI `dataverse data query --filter`, or `client.records.list(filter=...)` | Small result, no aggregation |
| "How many X" | CLI `dataverse data count`, MCP `read_query`, or `client.query.sql("SELECT COUNT(*) ...")` | Server-side count, no row download |
| Single-table aggregation (sum, avg, top-N) | `client.query.sql()` GROUP BY or `$apply` | Server-side, returns only groups |
| Cross-table aggregation | `client.query.sql()` INNER/LEFT JOIN + GROUP BY, or `client.query.fetchxml()`; else builder → DataFrame + `pd.merge()` | Server-side first, pandas for shapes SQL can't express |
| "X with related Y" / resolve lookups | `client.records.list(expand=...)` or QueryBuilder | Lookup resolution |
| Export / bulk extract | `client.query.builder(t).select(...).execute().to_dataframe()` | Straight to DataFrame → CSV |
| Notebook analysis | Same builder → DataFrame | pandas native |
| Duplicates / complex filter | `client.records.list(filter=...)` or QueryBuilder | SDK handles paging |
| Filtered read under 5K rows | CLI `dataverse data query --sql`, or `client.query.sql()` | Single lightweight call |

**Let the server do the work.** Aggregate and join server-side whenever SQL or FetchXML can express it. For the rest, pull each table with a minimal `select` and merge in pandas — the merge is sub-second; network transfer is the bottleneck.

For `$apply` and N:N `$expand`, prefer `client.query.fetchxml()` or the `dataverse api request` escape hatch. Hand-rolled `urllib` with `get_token()` is only justified inside a tight in-process loop — see [references/web-api-advanced.md](references/web-api-advanced.md).

---

## CLI reads

```bash
# OData read (--table is the EntitySet name: accounts, not account)
dataverse data query --table accounts --select "name,accountid" --filter "name eq 'john'" --top 10 --json

# Count
dataverse data count --table accounts

# SQL mode (logical name: account, not accounts)
dataverse data query --sql "SELECT name, accountid FROM account WHERE name LIKE '%john%'" --json

# Single record
dataverse data get --table accounts --id <guid> --json

# Raw escape hatch
dataverse api request --target dataverse --path "/api/data/v9.2/accounts?%24select=name&%24top=5"
```

### CLI gotchas

- **SQL mode mis-pluralizes custom tables.** `FROM im_category` resolves to entity set `im_categorys` and returns a **404 that looks like "table missing"**. It is not. Switch to OData mode with the real entity set: `dataverse data query --table im_categories --select im_name`. Look up `EntitySetName` in `EntityDefinitions` when unsure; never conclude a table is missing from this 404.
- **Windows quoting.** Wrap the whole `--path` in double quotes so `cmd.exe` / PowerShell do not treat `&` as a command separator. Keep `&` literal — it separates OData options, and `%26` merges them. Encode only `$` as `%24` (PowerShell reads a bare `$select` as a variable). An unquoted `&` can make the command exit non-zero even when the API returned valid JSON.

---

## SDK setup

```python
import os, sys
sys.path.insert(0, os.path.join(os.getcwd(), "scripts"))
from auth import get_client

client = get_client()
```

`get_client()` loads `.env`, resolves the environment URL, and handles auth (`scripts/auth.py`, installed by **dataverse-connect**). Wrap it in `with` for scripts that run to completion.

## Field name casing

Wrong casing is the most common cause of HTTP 400.

| Property type | Convention | Example | Used in |
|---|---|---|---|
| Structural (columns) | LogicalName, always lowercase | `new_name` | `$select`, `$filter`, `$orderby` |
| Navigation (lookups) | Navigation property name, case-sensitive per `$metadata` | `new_AccountId` | `$expand` |

System navigation properties (`parentaccountid`, `ownerid`) are lowercase; custom ones match the SchemaName.

---

## Read records

`client.records.list()` collects all pages into a flat `QueryResult` (supports iteration, `len()`, indexing, `.first()`, `.to_dataframe()`). **Always pass `select=`.**

```python
result = client.records.list(
    "new_ticket",
    select=["new_name", "new_priority", "new_status"],
    filter="new_status eq 100000000",
    orderby=["new_name asc"],
    top=50,
)
for r in result:
    print(r["new_name"], r["new_priority"])
print(f"{len(result)} tickets")
```

Stream large tables one page at a time:

```python
for page in client.records.list_pages("new_ticket", select=["new_name"], page_size=200):
    for r in page:
        print(r["new_name"])
```

Records support dict-style access: `r["col"]`, `r.get("col")`, `r.keys()`. Do not use `r.data.get()`.

Single record by GUID — returns `None` on 404 instead of raising:

```python
record = client.records.retrieve("new_ticket", "<guid>", select=["new_name", "new_status"])
if record is None:
    print("Ticket not found")
```

> **Deprecated:** `records.get()`. Replace page loops with `records.list()` (flat) or `records.list_pages()`, and by-GUID reads with `records.retrieve()`.

### Lookup display names (formatted values)

```python
for r in client.records.list(
    "opportunity",
    select=["name", "estimatedvalue", "_parentaccountid_value"],
    include_annotations="OData.Community.Display.V1.FormattedValue",
):
    account = r.get("_parentaccountid_value@OData.Community.Display.V1.FormattedValue")
    print(f"{r['name']} - {account}")
```

`include_annotations` is mandatory — without it the `Prefer: odata.include-annotations` header is not sent and no formatted values come back. Use `"*"` for all annotations. Formatted values exist for lookup, choice, status, and owner columns.

### `$expand` related records

```python
for r in client.records.list(
    "new_ticket",
    select=["new_name", "new_status"],
    expand=["new_CustomerId($select=new_name)", "new_AgentId($select=new_name)"],
):
    customer = r.get("new_CustomerId") or {}
    agent = r.get("new_AgentId") or {}
    print(f"{r['new_name']} | {customer.get('new_name', '')} | {agent.get('new_name', '')}")
```

- `expand` takes the case-sensitive navigation property name (`new_CustomerId`); lowercase returns 400.
- Always nest `$select` inside `$expand`, otherwise every column of the related table is returned.

---

## SQL — `client.query.sql()`

Uses the Web API `?sql=` parameter, a T-SQL subset. One HTTP call — typically 2–6 s, faster than paging for small result sets.

| Supported | Not supported |
|---|---|
| `SELECT`, `SELECT DISTINCT`, `SELECT TOP N` (0–5000) | `SELECT *` |
| `INNER JOIN`, `LEFT JOIN` | `RIGHT` / `FULL` / `CROSS JOIN` |
| `WHERE`, `GROUP BY`, `ORDER BY`, `OFFSET`/`FETCH` | `HAVING`, `UNION`, subqueries, CTEs |
| `COUNT`, `SUM`, `AVG`, `MIN`, `MAX` | `CASE`, string/date/math functions |

Results are capped at ~5,000 rows and **truncated silently** — do not use it for larger result sets.

```python
results = client.query.sql(
    "SELECT TOP 100 name, estimatedvalue "
    "FROM opportunity WHERE statecode = 0 "
    "ORDER BY estimatedvalue DESC"
)
for r in results:
    print(f"{r['name']}: {r.get('estimatedvalue', 0):,.0f}")
```

Discover which columns the SQL endpoint can query (virtual and computed lookup-display columns are excluded):

```python
for c in client.query.sql_columns("account"):
    print(f"{c['name']:30s} {c['type']:20s} PK={c['is_pk']}")  # also is_name, label
```

For full column metadata and relationships use **dataverse-metadata** (`client.tables.list_columns()`, `list_relationships()`, `list_table_relationships()`).

## FetchXML — `client.query.fetchxml()`

For joins and aggregates beyond the SQL subset or result sizes above 5K. The call returns an inert query; nothing is sent until `.execute()` (all pages) or `.execute_pages()` (lazy).

```python
query = client.query.fetchxml("""
  <fetch top="50">
    <entity name="account">
      <attribute name="name" />
      <link-entity name="contact" from="parentcustomerid" to="accountid" alias="c" link-type="inner">
        <attribute name="fullname" />
      </link-entity>
    </entity>
  </fetch>
""")

df = query.execute().to_dataframe()

for page in query.execute_pages():   # stream large results
    print(page.to_dataframe().shape)
```

## Raw Web API: `$apply` and N:N `$expand`

These are the only shapes that need the raw OData path:

- **N:N `$expand`:** `GET /<entitySet>?$expand=<nn_nav>($select=...)` — single page; follow `@odata.nextLink` past 5,000 rows.
- **`$apply`:** server-side grouping within one entity set, 50K source-record limit. Patterns: `groupby((col),aggregate(metric with sum as total))`, `aggregate($count as count)`, `aggregate(amount with average as avg)`.
- **Cross-table:** `$apply` cannot join — use `sql()` / `fetchxml()`, or builder DataFrames + `pd.merge()`.

Code samples: [references/web-api-advanced.md](references/web-api-advanced.md).

## QueryBuilder and DataFrames

`client.query.builder(table)` is a fluent API with composable AND/OR filters; `.execute().to_dataframe()` is the default for any analysis, comparison, or export:

```python
from PowerPlatform.Dataverse.models.filters import eq

df = client.query.builder("opportunity") \
    .select("name", "estimatedvalue", "statuscode") \
    .where(eq("statuscode", 1)) \
    .execute() \
    .to_dataframe()
```

Full reference, filter composition, and the DataFrame task table: [references/querybuilder.md](references/querybuilder.md). Notebook setup: [references/jupyter-setup.md](references/jupyter-setup.md).

---

## Common errors

| Status | Cause | Fix |
|---|---|---|
| 400 | Wrong casing: `$select`/`$filter` need lowercase LogicalName; `$expand` needs the case-sensitive navigation property | Check names in `EntityDefinitions(LogicalName='...')/Attributes` |
| 400 | Unsupported SQL. MCP `read_query` rejects DISTINCT, HAVING, subqueries, OFFSET, UNION, CAST/CONVERT, CASE, date functions (allows JOIN + GROUP BY). `client.query.sql()` rejects `SELECT *`, subqueries, CTEs, HAVING, UNION, RIGHT/FULL/CROSS JOIN, functions (allows INNER/LEFT JOIN, GROUP BY, DISTINCT) | Use `fetchxml()` / `$apply`, or pandas for cross-table |
| 404 | Table logical name wrong — or CLI SQL mode mis-pluralized a custom table | Verify with `client.tables.get("<name>")`; use OData mode with the real entity set |
| 429 | Throttled | SDK retries; reduce page size or pause between pages |

For `HttpError` handling in scripts see **dataverse-data**.

## Windows scripting

- ASCII only in `.py` files — curly quotes and em dashes cause `SyntaxError` on Windows.
- Do not use `python -c` for multi-line code; write a `.py` file.
- Generate GUIDs in Python (`str(uuid.uuid4())`), not via shell substitution.

## References

| Reference | When to load |
|---|---|
| [references/querybuilder.md](references/querybuilder.md) | Fluent queries, AND/OR filters, paged builder execution, choosing DataFrame vs page streaming, DataFrame write-back |
| [references/web-api-advanced.md](references/web-api-advanced.md) | `$apply` aggregation, N:N `$expand`, raw POST create responses, cross-table pandas merges |
| [references/jupyter-setup.md](references/jupyter-setup.md) | Querying from a Jupyter notebook |
Files in this package (4)
  • SKILL.md 13 KB
  • references/jupyter-setup.md 2 KB
  • references/querybuilder.md 4 KB
  • references/web-api-advanced.md 5 KB

Versions

The database has no other skills named dataverse-query.

Database snapshot: 13.11.2026 · package version 997fc9ab0906 (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 100dv-query
    Bulk reads, multi-page iteration, and analytics over Dataverse data. Use when the user wants to read, list, filter, aggregate, group, join, or analyze records — including pandas DataFrame workflows and notebook exploration. Also covers ERP (Finance and Operations / F&O)…
  • 100d365-webapi
    Correct Dataverse Web API (OData v4) requests in any language - TypeScript, JavaScript, C#, PowerShell, Python or raw HTTP. Covers paging with odata.maxpagesize and @odata.nextLink, FetchXML paging cookies, $select/$filter/$expand, formatted values and annotations, setting…
  • 90data-operations
    Use for Dataverse row/record DATA and queries - create, read, update, upsert, or delete rows, seed or sync reference/config data, and design and run queries. Trigger on "create a record/row", "read/retrieve this record", "get the value of a field on a row", "list/query the rows…
  • 100dataverse-data
    Record-level writes to Microsoft Dataverse tables with the Python SDK, the dataverse CLI, or MCP tools: create, update, delete, upsert with alternate keys, bulk CreateMultiple/UpsertMultiple, CSV import, multi-table foreign-key loads, DataFrame write-back, and schema-driven…
  • 96dataverse-python-sdk
    dataverse-python-sdk guidelines Triggers on: **
  • 85jupyter-notebook
    Work with Jupyter notebooks (.ipynb) — create, read, edit and run cells on a live Python kernel, with figures and tables returned to the chat. Notebooks can live in the workspace or on a running Jupyter Server / JupyterLab. Use when the user asks for a notebook, wants to analyse…

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.