Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 38 ยท Connectors and Data

Dataverse in Power Automate: Tables, Rows and OData Queries

Use the Dataverse connector: list rows with filter, select and expand, add and update rows, relationships and lookups, row triggers, alternate keys and paging.

Microsoft Dataverse is the database behind the Power Platform. It stores data in tables (formerly entities) with typed columns, relationships, security roles and auditing. When a process outgrows a SharePoint list, Dataverse is the usual next step. The connector is premium for most uses, and it needs an environment that has Dataverse.

Vocabulary

Term Meaning
Table A set of rows with columns, such as Account or Contact
Row One record
Column A field. Has a logical name (name, new_amount) and a display name
Choice A column with a fixed list of options, stored as a number
Lookup A column that points to a row in another table
Alternate key A unique column combination that can find a row
Publisher prefix The new_ or fin_ start of custom names

In expressions and queries you use logical names (lowercase), not display names.

Key actions

  • List rows: returns rows with filter, sort, select and expand.
  • Get a row by ID
  • Add a new row
  • Update a row (by ID or alternate key)
  • Delete a row
  • Perform a bound action / unbound action for custom messages
  • Relate rows / Unrelate rows for many-to-many links

Triggers: When a row is added, modified or deleted, with a Change type, a Table name, a Scope (User, Business unit, Organization) and optional Select columns and Filter rows.

The Select columns and Filter rows fields on the trigger are the best way to avoid unwanted runs, as described in Trigger conditions and filtering.

List rows with OData

The advanced options of List rows use OData:

Field Example
Select columns name,revenue,statecode
Filter rows statecode eq 0 and revenue gt 100000
Sort By revenue desc
Expand Query primarycontactid($select=fullname,emailaddress1)
Row count 500
Fetch Xml Query A FetchXML string for joins and aggregates

Notes on OData for Dataverse:

  • Strings need single quotes: name eq 'Contoso'.
  • Functions available include contains(name,'soft'), startswith(name,'A'), endswith.
  • Use the choice number, not the label, in filters: statuscode eq 1.
  • A lookup is filtered by its value column: _primarycontactid_value eq 00000000-....
  • Dates are ISO: createdon ge 2025-01-01T00:00:00Z.
  • Always select only needed columns. Without it, the connector retrieves every column, which is slow.
Python Stand-in: filter, sort and select like List rows

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Add and update rows

Add a new row shows the columns of the selected table. For a lookup, you cannot just pass a GUID; use the OData binding format:

TEXT
Primary Contact (Contacts)   contacts(@{outputs('Get_contact')?['body/contactid']})

For a choice column pick the label from the drop-down; in expressions use the numeric value. Update a row takes the Row ID and only changes the fields you fill in; blank fields are left alone, unlike a SharePoint update.

Use alternate keys (such as an order number) with Update a row (the field Row ID accepts ordernumber='A-1042' form in the Upsert variants) to do an upsert: create if missing, update if present, without a separate lookup.

Paging and large tables

List rows returns up to 5,000 rows per page and follows paging automatically when you turn on Pagination in settings and set a threshold. A very large table should be filtered at the source, and loops over thousands of rows should be replaced by Select, Filter array or batch operations.

Python Stand-in: reading a table in pages

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Security

Dataverse applies security roles to the connection's identity. A flow running as a user can only touch tables that user can read or write. Business units and row ownership also restrict access. Use a dedicated service account with a role that grants only the tables the flow needs (see Connections and authentication).

SharePoint or Dataverse?

Need SharePoint list Dataverse
Quick, simple data Good Overkill
Relationships and many writers Weak Strong
Row-level security Limited Strong
License Included in Microsoft 365 Premium
Large volumes Thresholds apply Designed for it

Common mistakes

  • Using display names instead of logical names in filters.
  • Putting the choice label in a filter instead of its number.
  • Omitting Select columns, which makes list rows retrieve everything.
  • Triggering on modified with no column filter, so every update starts a run.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What do you use in a Dataverse filter query for a column?

    OData queries use the lowercase logical column names such as revenue or new_amount.
  2. 2. Why should you set Select columns on List rows?

    Without a column list, all columns are retrieved, which is slower and larger.
  3. 3. What happens to blank fields in Update a row?

    Blank fields in Dataverse Update a row are ignored, so only filled fields change.
  4. 4. How is a lookup column value set in Add a new row?

    Lookups are bound with the entity set name and the row ID, as in contacts(00000000-0000-0000-0000-000000000000).

Summary

  • Dataverse stores typed tables with relationships and security; use logical names in queries.
  • List rows supports select, filter, sort, expand and FetchXML; always select the columns you need.
  • Lookups are bound with table(id), and choices are numbers.
  • Choose SharePoint for light data and Dataverse for relational, secure, high-volume work.

Next: Calling APIs with the HTTP action