How to Programmatically Add Option Set Values in Dynamics 365 with Power Automate

In every Dynamics 365 or Power Apps implementation, it’s common to require fields where users select values from predefined lists—such as Job Title, Customer Category, Priority, or Region. These lists are typically implemented using Choice fields (Option Sets) or custom Lookup fields referencing separate entities.

A common challenge when configuring Option Sets in Dynamics 365 arises when dealing with large volumes of values—especially when lists are provided externally, such as in Excel. Manually entering over 50 or more values can be highly time-consuming and inefficient, making bulk data input a significant pain point in such scenarios.

A fundamental architectural decision in data modelling is whether to implement a field as an Option Set or as a custom entity referenced through a Lookup field.

This choice depends on data requirements, system performance, and storage efficiency. Option Sets are ideal for static or infrequently changing lists where metadata (such as status or creation date) and relationships to other tables are unnecessary.

Based on research, we could utilize the Excel file provided by the client—with minimal modifications—to completely automate this process. By structuring the Excel data properly and using Power Automate, we can read each row and dynamically insert the values into the Option Set field.

Once the setup is done, the flow can be triggered with a single click, and it will:

• Retrieve every row from the Excel file.
• Add each value to the specified Option Set field.
• Automatically publish the changes to make them available in the model-driven or canvas apps.

The steps below explain the method to automate the creation and management of Option Set values in Dataverse using “Job Title” as an example field by using Power Automate and Excel.

1. Prepare an Excel Sheet Containing the Desired Option Set Values

Before automating, prepare or modify a structured Excel workbook listing all the Option Set values you want to add. The Excel file should contain a formatted table with the following columns as shown in screenshot below:

• AttributeLogicalName: The logical name of the Option Set field where the values will be created.
• OptionLabel: The display name that users will see in the Option Set dropdown.
• OptionValue: The unique integer identifier for each choice.

2. Save the file to either OneDrive or SharePoint.

To allow Power Automate to access the Excel file, upload it to a cloud storage location such as OneDrive or SharePoint.

3. Design a Power Automate Flow to Automate Option Set Value Creation

After ensuring the data is ready and accessible, set up a Power Automate flow that includes these steps:

a. Create a new flow and select a manual trigger to start the flow on demand.

b. Add the “List rows present in a table” action (Excel Online) and configure it with the file location and table name to read each row from the Excel file.

c. Add a condition to check if the file contains any rows (e.g., if the value is not null).

d. If data exists, use an “Apply to each” loop to iterate through each row. Inside the loop:

• Add a Perform an unbound action step.
• Set the action name to InsertOptionValue.
• Map the AttributeLogicalName from the Excel data to the corresponding field.
• Set the EntityLogicalName to the logical name of the target entity.
• For the Label field, use the following JSON format to localize the OptionLabel:

{  “LocalizedLabels”: [    {      “Label”: “@{items(‘Apply_to_each’)?[‘OptionLabel’]}”,      “LanguageCode”: 1033    }  ]}

• Map the Value field to the OptionValue column in the Excel sheet.

Note: The Option Set field must already exist on the respective entity in Dataverse before running this automation.

1. To publish the changes made to the respective entity, add a “Perform an unbound action” step after the “Apply to each” loop. Select the action name as PublishXml, and in the ParameterXml field, enter the following XML. Be sure to replace YOUR_ENTITY_LOGICAL_NAME with the logical name of your target entity:

<importexportxml>

<entities>

<entity>YOUR_ENTITY_LOGICALNAME</entity>

</entities>

</importexportxml>

Save and run the flow manually to trigger the automation. Once the flow executes successfully, you will be able to see the newly added Option Set values reflected in the respective field within your Dataverse entity, as demonstrated in the screenshot below.

Conclusion

Using Power Automate to manage Option Sets enables efficient scaling of application development while maintaining a lean, high-performance Dataverse environment. When handling large lists of values, automating Option Set creation eliminates manual entry inefficiencies and significantly reduces the risk of errors.

The post How to Programmatically Add Option Set Values in Dynamics 365 with Power Automate first appeared on Microsoft Dynamics 365 CRM Tips and Tricks.

Original Post https://www.inogic.com/blog/2025/05/how-to-programmatically-add-option-set-values-in-dynamics-365-with-power-automate/