Power Automate Dataverse Expression and Data-Shape Errors

#Scope

This guide fixes six common cloud-flow failures caused by unexpected null values, mismatched data types, arrays being treated as objects, and Dataverse formatting requirements. The examples use native Power Automate and Microsoft Dataverse actions and do not require the HTTP connector.

#Evidence classification

• Microsoft documented: the expression, Dataverse lookup, List rows, OData, and Split On behavior referenced in this guide.
• CMS implementation requirement: deploy flows in solutions and use connection references and environment variables. Check the CMS GCC DLP policy before introducing any additional connector.
• Validate in the target environment: table logical names, entity set names, trigger schemas, nullable columns, security privileges, and sample data differ by solution.

#1. ExpressionEvaluationFailed from a null or missing property

#Symptom

An expression such as split(), length(), int(), or formatDateTime() fails only for certain records. The affected Dataverse column is null, the property is absent from the action output, or a related row does not exist.

#Fix

Use the safe-navigation operator (?) when reading a property:

body('Get_Contact')?['emailaddress1']

Use coalesce() when an empty string is a valid fallback:

length(coalesce(body('Get_Contact')?['emailaddress1'], ''))

Before splitting an email address, validate both presence and shape:

and(
  not(empty(body('Get_Contact')?['emailaddress1'])),
  contains(body('Get_Contact')?['emailaddress1'], '@')
)

Run the following expression only in the Condition's Yes branch:

split(body('Get_Contact')?['emailaddress1'], '@')

Using split(coalesce(value, ''), '@') prevents the initial null failure, but accessing element [1] can still fail when the value does not contain @.

If the field is required for the business transaction, route the record to an exception path instead of silently supplying a default. Place risky actions in a Try scope and configure a Catch scope to run when Try fails or times out. See Exception handling and error logging in Power Automate.

#2. String and number type mismatches

#Symptom

add(), sub(), greater(), or another numeric operation fails because a trigger parameter, Compose output, or parsed value contains numeric text rather than an integer or float.

#Fix

Convert the value before performing the operation:

add(int(triggerBody()?['Quantity']), 1)

For a value that can be null, empty, or whitespace, normalize it first:

if(
  equals(
    trim(string(coalesce(triggerBody()?['Quantity'], ''))),
    ''
  ),
  0,
  int(triggerBody()?['Quantity'])
)

Use float() for decimal input:

float(triggerBody()?['Amount'])

Convert operands before comparing them:

greater(
  float(coalesce(triggerBody()?['Amount'], 0)),
  100.0
)

Important behaviors:

• int('3.14') fails. Use float('3.14') when decimals are valid.
• formatNumber() returns text. Convert the result back to float() before additional math.
• When the source contract is under the team's control, define numeric trigger or Parse JSON properties as numbers rather than repeatedly converting them downstream.
• Do not default missing values to zero unless zero has the correct business meaning.

#3. Unintended Apply to each from List rows

#Symptom

Selecting dynamic content from Dataverse List rows automatically places the next action in an Apply to each loop, although the flow expects at most one record.

#Cause

List rows returns an array in body/value, even when the filter happens to match one row. The designer adds a loop when array-based dynamic content is selected.

#Fix

If the row ID is already known, use Get a row by ID instead of List rows.

Otherwise:

1. Filter on a unique key whenever possible.
2. Set Row count to 1.
3. Check that a row exists before calling first().
4. Reference the record with an expression instead of selecting array-based dynamic content.

Check for a result:

greater(
  length(body('List_rows')?['value']),
  0
)

In the Condition's Yes branch, retrieve the required property:

first(body('List_rows')?['value'])?['emailaddress1']

Do not use first() without handling the zero-row case. Setting Row count to 1 limits the response but does not prove that a record exists or that the filter is logically unique.

If removing an existing Apply to each, first move or recreate its child actions outside the loop and replace expressions such as items('Apply_to_each') with the single-record expression.

#4. Dataverse lookup formatting

#Symptom

Add a new row or Update a row returns BadRequest, Resource not found for the segment, or another relationship error when a lookup column receives only a GUID.

#Cause

The Dataverse connector requires the lookup value to include the target table's entity set name:

<entity-set-name>(<row-id>)

#Fix

For an Account lookup:

accounts(aaaaaaaa-0000-1111-2222-bbbbbbbbbbbb)

Dynamic example:

concat(
  'accounts(',
  outputs('Get_Account')?['body/accountid'],
  ')'
)

Use the target table's entity set name, not an assumed plural display name. For example, a Contact reference normally uses:

contacts(<contact-guid>)

For a polymorphic lookup such as Customer or Owner, populate the lookup input for the correct target type. Also confirm that the flow connection has the required Dataverse privileges, including Append and Append To where applicable.

#5. OData Filter rows syntax failures

#Symptom

Dataverse List rows rejects the Filter rows value, or the action succeeds but returns no records.

#Rules

• Do not include $filter= in the Filter rows box.
• Use Dataverse column logical names, not display names.
• Put text literals in single quotes.
• Use ISO 8601 timestamps for date/time comparisons.
• Keep operand types compatible with the Dataverse column type.
• URL-encode reserved characters when required by the connector.

Text example:

emailaddress1 eq 'person@example.gov'

Dynamic text example:

concat(
  'emailaddress1 eq ''',
  variables('EmailAddress'),
  ''''
)

GUID example:

accountid eq aaaaaaaa-0000-1111-2222-bbbbbbbbbbbb

Date/time values in Dataverse Filter rows expressions are normally unquoted. To retrieve records created during one UTC calendar date, use a half-open range:

createdon ge 2026-09-03T00:00:00Z and createdon lt 2026-09-04T00:00:00Z

Dynamic UTC date range:

concat(
  'createdon ge ',
  formatDateTime(variables('StartDate'), 'yyyy-MM-ddT00:00:00Z'),
  ' and createdon lt ',
  formatDateTime(addDays(variables('StartDate'), 1), 'yyyy-MM-ddT00:00:00Z')
)

If the business date is based on a local time zone, convert the local day boundaries to UTC before constructing the filter. Do not assume that midnight UTC matches the business day.

When accepting free-form text, remember that an apostrophe inside an OData string literal must be escaped by doubling it. Prefer validated identifiers or controlled values when possible.

#6. Missing array properties caused by Split On

#Symptom

An array expected from the trigger is missing from dynamic content, or the trigger body represents only one element rather than the complete payload.

#Cause

Split On debatches a supported trigger array. Power Automate starts a separate flow run for each array item, changing the effective trigger shape for each run.

#Fix

Open the trigger's context menu (...), select Settings, and inspect Split On.

• If one flow run per array item is intended, leave Split On enabled and reference the current item's properties directly.
• If the flow must process the complete array in one run, disable Split On, save the flow, and reopen the designer if the dynamic-content schema does not refresh.

After disabling Split On, reference the array explicitly using the actual trigger property name, for example:

triggerBody()?['value']

Then process the array intentionally with Apply to each, Select, or Filter array. Disabling Split On changes run count, concurrency behavior, error isolation, and retry scope, so retest those behaviors with multiple elements and a deliberately failing element.

#Recommended diagnostic sequence

1. Open the failed run and identify the first failed action, not only the failed Scope.
2. Inspect the action's raw inputs and outputs.
3. Confirm whether the runtime value is null, text, number, object, or array.
4. Confirm Dataverse logical names and the target entity set name.
5. Test the OData filter with a fixed known value before making it dynamic.
6. Inspect trigger settings for Split On.
7. Add null and zero-result branches before manipulating values.
8. Retest with valid, null, empty, malformed, zero-row, one-row, and multi-row cases.

#CMS GCC and ALM requirements

• These patterns use native Dataverse and built-in data operations; they do not require the HTTP connector.
• Check CMS GCC DLP and connector workarounds before introducing another connector.
• Package flows in solutions.
• Use connection references for connector bindings.
• Use environment variables for environment-specific values; do not hard-code environment URLs or deployment-specific identifiers.
• Avoid recording secrets, access tokens, or unnecessary personal data in error logs.

#Test checklist

• Null and missing optional fields are handled.
• Malformed strings do not reach split(), date conversion, or numeric conversion.
• Numeric text is explicitly converted before math or comparison.
• Zero-row, one-row, and multi-row List rows results are tested.
• Lookup values use the verified entity set name and row ID.
• OData filters use logical column names and correct literal formats.
• Local business dates are converted to correct UTC boundaries.
• Split On matches the intended one-run-per-item or one-run-per-array design.
• Try/Catch logging captures the first actionable failure.
• The flow is solution-aware and uses connection references and environment variables.

#References

• Getting errors with null fields
• Expression cookbook for cloud flows
• Use lists of rows in flows
• Use relationships to modify rows
• Filter rows using OData
• Schema reference for trigger and action types