Power Automate - Common Error Fixes

#Scope

These are recurring Power Automate errors encountered during the CCIIO SME Booking App and ISG Contract Language Library (CLL) builds. The guide focuses on issues that either produced misleading error messages or required implementation patterns that were not immediately obvious from Microsoft documentation.

#Evidence classification

Evidence labels used in this guide:

• Microsoft documented: confirmed in Microsoft documentation
• CMS tenant observed: reproduced in the CMS GCC environment
• Production validated: currently used successfully in a production flow
• Unconfirmed root cause: behavior reproduced, but the underlying platform mechanism has not been independently confirmed

#Problem 1: PicklistValueOutOfRange

Evidence: CMS tenant observed, production validated

Symptom: A flow writing to a Dataverse Choice (OptionSet) column fails with PicklistValueOutOfRange, even though the value being written looks correct in the flow's Dynamic Content preview.

Cause: Dataverse Choice columns store an underlying numeric value, not the display label. If the source system (in this case, Jira issue values syncing into Dataverse) sends a value that doesn't map to one of the Choice column's defined numeric options, the write fails. This most often happens when the source system's values and the Dataverse Choice option set were defined independently and drift out of alignment.

Fix: Where the source system already provides stable numeric identifiers, aligning Dataverse Choice values with those identifiers can eliminate translation logic and simplify synchronization. In this case, the Dataverse Choice column's numeric options were set to match the Jira issue IDs directly, so the write succeeds without any transformation logic in the flow. This won't be the right approach in every scenario, particularly if the source identifiers aren't stable or don't map cleanly to a bounded set of options.

Status: Used in production for the Jira CCIF issue sync flow feeding cr549_interestform.

#Problem 2: Dataverse Choice columns return numeric codes instead of labels

Evidence: Microsoft documented, CMS tenant observed, production validated

Symptom: A flow reads a Dataverse Choice column and gets back a number (for example 1) instead of the expected label (for example "Approved"), breaking any downstream logic that compares against the label text.

Cause: This is expected Dataverse behavior. Choice columns are backed by an integer value; the label is a display-only mapping, and the raw value returned by most Dataverse actions is the integer, not the label.

Fix: Depending on where the value is needed:

• Use the formatted value annotation when it's available in the response. Dataverse Web API responses can include the @OData.Community.Display.V1.FormattedValue annotation when formatted values are returned, which provides the display label rather than the raw integer.
• Use the Dataverse connector's formatted output if the specific action exposes one.
• Otherwise, map the integer to the expected label yourself, for example with the Switch control action or a lookup table maintained alongside the Choice column's option set.

Note that this is Power Automate cloud flow behavior specifically. Formatting functions available in Power Fx (Canvas Apps) are a separate language from Power Automate cloud flow expressions; a function that works in one context doesn't necessarily exist or behave the same way in the other.

Status: Applied across status-based filtering logic in the CLL workflow flows using the formatted value annotation.

#Problem 3: Filter array advanced mode conditions silently return no results

Evidence: CMS tenant observed, production validated

Symptom: A Filter array action set to advanced mode returns zero results, even though the underlying array clearly contains matching items when inspected in the flow run's raw inputs.

Cause: Advanced mode in Filter array expects the condition to be entered as an expression rather than plain text. Depending on how the expression is entered, this may require the @ expression prefix (for example @equals(item()?['status'], 'Active')). Entering the condition as plain text, or omitting the expression prefix where it's required, doesn't throw a syntax error; it just fails to evaluate the condition correctly, so the filter quietly returns nothing.

Fix: When using Advanced mode, ensure the condition is entered as an expression rather than plain text. Depending on how the expression is entered, this may require the @ expression prefix. If a Filter array in advanced mode returns unexpectedly empty results and the array itself is confirmed to have data, this is the first thing to check.

Status: Used across CLL status-sequence filtering logic and CCIIO SME slot filtering.

#Problem 4: Trying to use switch() as an inline expression

Evidence: Microsoft documented, CMS tenant observed

Symptom: Trying to use switch() inside a dynamic content expression field (for example, inside a Compose action's input, written as a formula) fails or the function isn't recognized.

Cause: Switch is implemented as a Control action in Power Automate rather than as an inline expression function, added to the flow as a distinct step with its own canvas presence. This is a common point of confusion for anyone coming from a language where switch is an expression construct.

Fix: Use the Switch control action from the Control category instead of trying to write it as an inline expression. For inline conditional logic inside an expression field, use nested if() calls instead.

Status: Confirmed while building conditional logic in the CLL notification flows; corrected to use the Switch control action.

#Takeaways for future builds

• Cryptic Dataverse write errors like PicklistValueOutOfRange are usually a value-mapping mismatch between the source system and the Dataverse option set, not a flow logic bug.
• When a Dataverse Choice field doesn't behave as expected downstream, check whether the flow is reading the raw numeric value instead of the formatted label first.
• Advanced mode expressions in Filter array and similar actions need to be entered as expressions, not plain text, and a wrong result there tends to fail silently rather than with a clear error.
• Some familiar-sounding function names, like switch, aren't expression functions in Power Automate even though they exist in the platform. Confirm whether something is a Control action or an expression function before assuming syntax is at fault.
• Power Fx (Canvas Apps) and Power Automate cloud flow expressions are different languages with different available functions. Don't assume a function from one works in the other.

#Reusable snippets

#Dataverse Choice formatted value

Reference the formatted label for a Choice column returned from a Dataverse List Rows or Get Row action:

@{outputs('Get_row')?['body/statuscode@OData.Community.Display.V1.FormattedValue']}

Replace Get_row with your action name and statuscode with your Choice column's logical name.

This pattern applies when the Dataverse action returns formatted value annotations. If the annotation isn't present, check whether the action supports formatted values or retrieve the label using another supported approach.

#Filter array advanced mode condition

Filter an array where a field equals a specific value:

@equals(item()?['status'], 'Active')

#Nested if() for inline conditional logic

Use nested if() calls in place of switch() inside an expression field:

@if(equals(triggerBody()?['status'], 'Approved'), 'Green', if(equals(triggerBody()?['status'], 'Pending'), 'Yellow', 'Red'))

#Open items

• Confirm which specific Dataverse actions expose a formatted-output option directly, versus requiring the FormattedValue annotation
• Document a reusable expression snippet library for common advanced-mode Filter array conditions beyond simple equality checks

#References

• Microsoft Learn: Create and edit choices (picklists) overview for Microsoft Dataverse
• Microsoft Learn: Use expressions in conditions in Power Automate
• Microsoft Learn: Use data operations in Power Automate (includes Filter array)
• Microsoft Learn: Explore the cloud flows designer (covers container/control actions including Switch)