Dataverse - Converting strings to option set

Option sets vs strings

Recently I came in a situation where I had imported data from an online dataset and the data was imported as strings. For some columns I had imported the data type would be better of being an option set.

Option sets in Dataverse are a predefined list of selectable values for a column, allowing users to standardize input by choosing from consistent options. This reduces errors and helps users pick values. I love them!

Thus, a conversion was needed. I decided to use Power Automate for this since it would give me the best flexibility of solving this. Below my description of how I managed this.

Step 1: Recreate the column and copy/migrate the data

I already set up the right column names and imported the data. Therefore, I needed to copy the data into a new column so I could recreate the column into a Choice column. The column in question was called Level (fsvtech_level) and housed the difficulty level of exercises in the gym for an application I am building for personal use.

I recently spoke to Daryl Labar about his great contributions to the XRM toolbox and he pointed me to his attribute manager tool. I decided to use (abuse?) this tool for my problem. You can find this tool in the XRM Toolbox and it’s called Attribute Manager.

Step 2: Use the Attribute Manager tool to copy the data into a new column.

Using the Attribute Manager tool you can copy the data into a new column. Be sure to connect to the right environment before using the tool.

1. Choose your entity and attribute after retrieving the entities

2. Click on Convert Attribute Type.

3. Choose Single Line of Text as the target column data type.

   

4. Since we want to keep both columns for now we tick off all steps after ‘Migrate to New Attribute’.

   

Step 3: Check if your new column exists with data.

Check if your column is created and whether there is data in it. The column will show up with “_t_” added to it (t for temporary). If the data is present you can now delete the original column. That column might not have data in it but we don’t need that data anymore.

Step 4: Create the option set and column

Next, create the option set you want to use and recreate the original column as a Choice column this time. I have three values for this option set. Beginner, intermediate and expert.

Step 5: Create the flow to convert the string to option set

Create a flow and make the trigger manual. Use the list rows action in Dataverse to list the columns including the column you want to convert. For testing purposes, we set the row count on 5.

Step 6: Create the steps needed for the flow

Now the magic comes. In an Apply to each box have the following steps:

1. Use the values from the previous list rows action.

2. I love working with Compose actions to make immutable variables. More info on this can be found on Damien Birds website who I can highly recommend. Create a compose action and insert your string column, in my case this column is called ‘Level’ (fsvtech_level_t_)

3. I put a condition in to check whether there is a value in the Level column, this speeds up the flow a bit but is not neccesary.

4. For this value you can just add null through typing the word null in the Expression section of the field.

5. Most importantly, the conversion of string to option set value. We need to use the values we gave the option set choices in step 4 here. I used the following code for this:

   if(equals(outputs('LevelString'),'beginner'), 314710000, if(equals(outputs('LevelString'), 'intermediate'), 314710001, if(equals(outputs('LevelString'), 'expert'), 314710002, '')))

   You can add more different options if you have them. Be sure that your fields only contain one option though! Multiple options divided by a comma won’t work here. Put the code in a new compose step, I called this step LevelOptionSetValue

6. Lastly, update the Choice column you created with the LevelOptionSetValue.

Step 7: Update the new Choice column with the option set value.

To use the LevelOptionSetValue compose step, you will have to click on ‘Enter custom value’ after clicking the arrow pointed downwards to ‘Show options’.

Step 8: Insert your value in the new Choice Column

After clicking ‘Enter custom value’ you can choice your compose step and you are ready to run the flow.

Step 9: Run your flow

Run your flow. If the test completed and the first 5 records are successful you can remove the row limit and run the flow so it will iterate over all records.

Step 10: Delete the old column

Lastly, delete your old column and start using your new Choice column with your option set 😁