Power Apps: Get Dataverse Option Set ID as well as Label

Why?

Dataverse (previously known as the Common Data Service (CDS)) is the promised land for Power Platform Business Apps and I definitely agree!
However Option Sets coming from Dataverse used in Canvas Power Apps, return the textual label only… where the Choice Column of SharePoint returns an object containing the label as well as an Identifier.

What?

In this post we discover a workaround for getting all information from a Dataverse Option Set. Not only the textual label but alwo the numeric identifier.
Update NOV2022: please also see Matthew his comment below for a (new) more simple way.

How?

First let me give credits to the posts that pointed me into this direction:

• Microsoft Community Member Mike8 for joining Collections: https://powerusers.microsoft.com/t5/Building-Power-Apps/Combine-two-columns-from-two-different-sources/td-p/84292
• Debajit Dutta for retrieving Option Set ID’s: https://debajmecrm.com/how-to-get-the-value-not-text-of-a-selected-option-for-a-cds-option-set-in-canvas-app/

So we have an Option Set in the Dataverse. In my example the Field (Column) of the Entity (Table) is called SolProFS Sample Status. In this column, I have created a Local Option Set because it will only be used in this Table:

When we expand one of the options, we see that an option (item) has a Name as well as a Value. The Name is the textual option we defined. The Value follows the numbering configured in the Power Platform Solution Publisher:

A unique numeric Value is very useful, when it comes to multi-language solutions and changes in names throughout the lifecycle of your business application. To get all data of an Option Set, we will create a collection in the OnStart of the Power App. We can then use this collection in every Control where we need the Value as well as the Name. Based on the blogs shared above I have split the workaround into 3 steps:

1) We create a Global Variable containing the Values of all the options in the Option Set. We place this function in the OnStart property of the Power App:

Set(
        vargSampleStatusOptions,
        JSON(Choices('SolProFS Sample Status (SolProFS Samples)'))
    );

Using the JSON function, the result will be a long string with all the Values of all the Option Set options:

[{"Value":880000000},{"Value":880000001},{"Value":880000002},{"Value":880000003},{"Value":880000004},{"Value":880000005},{"Value":880000006}]

2) Right after this action, we create a collection that splits this long string into separate records. We will then have more flexibility in the rest of the Canvas Power App:

ClearCollect(
    colSampleStatusOptions,
    AddColumns(
        Split(
            vargSampleStatusOptions,
            ","
        ),
        "acStatusID",
        Value(
            Substitute(
                Substitute(
                    Substitute(
                        Result,
                        "[",
                        ""
                    ),
                    "{""Value"":",
                    ""
                ),
                "}",
                ""
            ))))

This collection will result in a nice table with an added column asStatusID showing the actual Value (unique identifier):

3) Now we will use this collection as a baseline to combine the unique Values with the actual Names that users will see:

Clear(colOptionSetCounter);
Clear(colSampleStatusOptionsAll);
ForAll(
    colSampleStatusOptions,
    Collect(
        colOptionSetCounter,
        Last(colOptionSetCounter).Value + 1
    );
    Collect(
        colSampleStatusOptionsAll,
        {
            OptionValue: Last(
                FirstN(
                    colSampleStatusOptions,
                    Last(colOptionSetCounter).Value
                )
            ).acStatusID,
            OptionLabel: Last(
                FirstN(
                    Choices('SolProFS Sample Status (SolProFS Samples)'),
                    Last(colOptionSetCounter).Value
                )
            ).Value
        }
    )
);

I found the expression

Choices('SolProFS Sample Status (SolProFS Samples)') 

in the Dropdown of a Form Control in the Canvas App where the SolProFS Sample Status column was used. This expression however only contains the Names of the Option Set and not the Values:

My expression needs the colOptionSetCounter to count the number of records in the baseline collection colSampleStatusOptions, because a collection itself does not have an index… Maybe the Power Apps Team should have a sit down with the Power Automate Team and dicuss the added value of indexing to the Power Apps collections concept based on the array Power Automate array concept 🤨

Using the colOptionSetCounter we can loop through the baseline collection colSampleStatusOptions and create a wonderful new collection that combines the OptionValue with the OptionLabel: colSampleStatusOptionsAll.
The OptionValue column is already in the baseline collection –> in the newly added column acStatusID.
The LabelValue column is retrieved from the Choices() function referencing the SolProFS Sample Status column. The ordering configured in the CDS when configuring the Option Set is consistent and should have the same order as the retrieved OptionValues:

This collection can from now on be used to sort the Option Set in any way we want, use the Label when we want the text and use the Value when we want the unique ID 😎

Some of the steps above can be done combined / more efficient, but this setup should make clear how the steps work in a specific order and what the main logic behind the workaround is.