Power BI: Transforming Option Set Values to Option Set Labels

Colin Maitland, 02 December 2021

In this article, I will demonstrate two methods that may be used for transforming the Option Set Values in Option Set columns to their corresponding Option Set Labels when using an OData Feed to Connect Power BI to Dynamics 365. These methods may be used for any single-select Option Set column retrieved from Dynamics 365 in this way.

For this demonstration, I will use the StateCode and StatusCode columns for Opportunities. In Dynamics 365, the StateCode and StatusCode columns are specialised single-select Option Sets. The following image shows the standard StateCode and StatusCode options for Opportunities and their corresponding Option Set Labels.

Manual Option Set Table

The two methods demonstrated in this article use a manually configured table in the Power BI data model named Option Set. This table contains a list of manually configured Option Set Value and Option Set Label values. These are configured for each Option Set for which Option Set Values need to be transformed to their corresponding Option Set Labels.

The following image shows the configuration for the Opportunities Status and Status Reason option sets.

Method 1 below only requires the presence of the Name, Value and Label columns in this table. Method 2 below also requires the presence of the OptionSetKey column in this table. As previously mentioned, this is a manually configured table; however, it does have these additional applied steps following the Source step. The step at line 5 adds the OptionSetKey column as a custom column and formats the OptionSetKey using the same format as that used by Method 2.

Method 1 – Add Calculated Column using DAX

This method involves loading the Opportunities with their StateCode and StatusCode columns and adding additional Calculated Columns using DAX.

A DAX formula such as the one displayed below may be used. This formula is generic except for the name of the Calculated Column and the configuration of the _OPTION_SET_NAME and _OPTION_SET_VALUE variables.

This formula uses the DAX LOOKUPVALUE() function to look up the corresponding Option Set Label from the Option Set table for the Option Set identified by _OPTION_SET_NAME and the Option Set Value identified by _OPTION_SET_VALUE. The _OPTION_SET_NAME is set to the Option Set Name as configured in the Option Set table. The _OPTION_SET_VALUE is set to the Option Set Value column in the table to which the Calculated Column is being added.

If a matching Option Set Label is not found, this formula returns ‘ERROR! – Option Set Value’, i.e. ‘ERROR! – 123456789’, so that any users of the data model can clearly see that a corresponding Option Set Label was not found for the specified Option Set Name and Option Set Value. They then only need to update the configuration of the Option Set table to correct this.

In this example, the formula is used to create a new Calculated Column named Status. This formula looks up the corresponding Option Set Label for the OpportunityStateCode Option Set and the current row value of the Opportunities[StateCode] column.

In this example, the formula is used to create a new Calculated Column named Status Reason. This formula, looks up the corresponding Option Set Label for the OpportunityStatusCode Option Set and the current row value of the Opportunities[StatusCode] column.

When using this method, you should consider the following:

· The Option Set Value columns, such as StateCode and StatusCode, need to be retained in the Data Model so that they can be referenced by this formula. However, this is not necessarily an issue. In some cases, you will need to retain the Option Set Value column for use as a Sort By column or for use by other DAX formulas.

· When writing DAX formulas that refer to specific Option Set Values, it is usually best practice to reference the Option Set Values rather than the Option Set Labels, as shown on line 11 of the following example. Here the formula uses Opportunities[StateCode] instead of Opportunities[Status] to filter the Opportunities by their Status. This is because, in a deployment of Dynamics 365, Option Set Values do not change, while sometimes, their corresponding Option Set Labels do get changed. Less work is required to update the corresponding configuration in the Option Set table than to locate and update the configuration of many DAX Measures or Calculation Items.

· The Option Set Value columns, such as StateCode and StatusCode, would normally not need to be visible in the data model. These columns should then normally also need to be marked as hidden columns in the data model. This reduces clutter and helps users to focus on using the corresponding Option Set Label columns.

· When Power BI loads the data model into memory, it compresses the data model using complex algorithms. These algorithms include steps that optimise the compression by pre-sorting the rows in a table by their columns based on the cardinality, or uniqueness, of the values stored in each column. However, Calculated Columns are not included in this compression optimisation step. This is one reason why it is generally best practice to consider adding additional columns to a data model as Custom Columns using Power Query rather than as Calculated Columns using DAX.

Method 2 – Add Custom Column using Power Query

This method involves loading the Opportunities with their StateCode and StatusCode columns and then adding additional Custom Columns using Power Query.

A custom M function such as that displayed below may be used. The name of this custom M function is GetOptionSetLabel(). This function takes two parameters, the OptionSetName and the OptionSetValue. It then uses these to look up the corresponding Option Set Label from the Option Set table. Note: I have redacted the metadata Documentation lines between 26 and 52 for clarity.

Here is how the GetOptionSetLabel() function works:

· Line 62 assigns the Option Set table to the variable named OptionSetTable.

· Line 63 converts the OptionSetKey column from the table into a list using the Table.Column() function. This list is assigned to the variable named OptionSetKeysList.

· Line 64 converts the OptionSetLabel column from the table into a list using the Table.Column() function. This list is assigned to the variable named OptionSetLabelsList.

· The Table.Column() function can only create a single column list. Therefore, it is used twice in this formula. Firstly to create a list of OptionSetKeys and secondly to create a list of Option Set Labels sourced from the Option Set table.

· Line 65 transforms the values of the OptionSetName and OptionSetValue function parameters into the same format as that used for the OptionSetKey column value in the Option Set table and assigns this to a variable named OptionSetKey.

· Line 66 gets the position of the matching OptionSetKey from the OptionSetKeys list using the List.PositionOf() function and assigns this to a variable named Position. This is the index value of the position of the matching OptionSetKey in the OptionSetKeysList, if any.

· Lines 67 to 70 get the corresponding Option Set Label for the same position using the value of Position as an index value. Alternately, the same “ERROR! … “ text is returned as that returned by the previously demonstrated DAX formula. This is assigned to a variable named OptionSetLabel and is returned as the Result.

Following are two examples of how the GetOptionSetLabel() function may be used. The first example adds a new Custom Column, while the second example transforms the existing column.

Example 1 – Use GetOptionSetLabel() to Add Custom Column

In this example, the GetOptionSetLabel() method is used add a new Custom Column named Status Reason to the Opportunities table.

Add an Invoke Custom Function step to the query for Opportunities and configure it as follows:

In this configuration, Function Query is configured by selecting the GetOptionSetLabel function, the Option Set Name parameter is configured by entering OpportunityStatusCode, and the Option Set Value parameter is configured by selecting the StatusCode column.

The resulting step looks like this. Note: I have formatted this step into multiple lines for clarity:

This step then needs to be manually updated as shown here by renaming the step and adding the output data type so that the resulting custom column has a data type of text rather than any.

The result of using this approach is that the data model contains both the StatusCode column and a corresponding custom Status Reason column.

Optionally: If required, a subsequent step may be added to remove the StatusCode column from the data model. However, you should consider whether that column should be retained for use as a Sort By column or by DAX formulas.

Example 2 – Use GetOptionSetLabel() to Add Custom Column

In this example, the GetOptionSetLabel() method is used to transform the existing StatusCode column from a Whole Number data type column containing the Option Set Values to a Text data type column containing the Option Set Labels. A RenameColumns() step is also used to then rename the transformed StatusCode column to Status Reason.

The result of this approach is that the data model contains a single Status Reason column.

Conclusion

In conclusion, when using an OData Feed to connect to and load data from Dynamics 365 (i.e. Dataverse), you will need to transform the Option Set Values from the Option Set columns into their corresponding Option Set Labels. In this article, I have demonstrated two methods that may be used to do this. These methods may be applied to any single-select Option Set column. A different approach needs to be used for multi-select Option Set columns.