OptionSet Labels in Power BI Reports

If you’ve tried reporting on your Dataverse / Dynamics 365 data using Power BI, you’ll probably have noticed you don’t get the labels associated with any optionset (now “Choice”) fields in your data. There are ways around this such as using the PowerBI OptionSet Assistant solution. In this post I’ll show how to get this data without having to install anything into your environment. It’s also completed automated with no manual updating of labels as you add or change them.

TLDR: Use the TDS Endpoint to create a lookup table while still using Import mode for your fact tables.

Power BI Query Modes

Power BI offers two query modes – Import and DirectQuery. Microsoft recommends that you use the Import mode wherever possible for the best performance of your reports. Import mode also gives us some functionality that is disabled in DirectQuery mode, such as automatic date hierarchies.

When using the Dataverse connection though, Import mode only gives numeric values for your optionset columns, not the human-readable labels.

With the DirectQuery mode you can get the number and associated name. In multi-lingual deployments you even get the name in the language of the user making the query:

Mixed Mode

What we really want to do is use these two modes together to get the best of both worlds.

Firstly you’ll need to enable the TDS Endpoint for your environment in order to use the DirectQuery mode part of the solution.

Adding the Import mode table

If you’ve already got an Import mode table that you want to add labels to, skip over this section.

Starting from a new report, click “Get data” and select the “Dataverse” connector:

Enter the domain for your instance, ensure “Import” mode is selected and click OK:

Select the table that includes your optionset column and click Transform Data:

Do any further changes you need to your data such as removing columns or filtering the data, then click Close & Apply to add it to your report.

Creating the lookup table with DirectQuery

Click “Get data” again, select the Dataverse connector and enter the same URL. This time though, select the DirectQuery mode:

Select the same table again and click Transform Data.

This time we’re going to use Power Query to reduce this table down to a simple lookup of optionset values to their associated labels.

1. To start off with, remove all the other columns. Select the optionset value and label columns, click the dropdown arrow for “Remove Columns” in the ribbon, then click “Remove Other Columns”
2. Next, remove any rows that don’t have a value for these columns. Click the dropdown arrow at the top of the value column, untick the “(null)” value and click OK
3. Finally, we only want one row for each value. Select the two columns and click “Group By” in the ribbon. This will automatically create a new “Count” column as well to show how many records had that value, but we’re not interested in that so you can remove this in the formula editor. For example, if the formula was:
   = Table.Group(#"Filtered Rows", {"industrycode", "industrycodename"}, {{"Count", each Table.RowCount(_), Int64.Type}})
   you can remove the last part so it becomes:
   = Table.Group(#"Filtered Rows", {"industrycode", "industrycodename"})

It can be difficult to find the correct columns to use in the first step, especially in a large table like account. You might find it easier to use the “Advanced Editor” option on the “View” tab of the ribbon. Here you can simply paste in the following query, changing the URL, table and column names as appropriate:

let
    Source = CommonDataService.Database("orgcfb204be.crm3.dynamics.com"),
    dbo_account = Source{[Schema="dbo",Item="account"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(dbo_account,{"industrycode", "industrycodename"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([industrycode] <> null)),
    Distinct = Table.Distinct(#"Filtered Rows")
in
    Distinct

In this version I’ve used the Distinct operation instead of Group for the final step, but it comes to the same result.

Finally, make sure you give this new table a useful name. By default it will have been called account (2) which isn’t very helpful. Edit the name in the top right (something like IndustryCodeLookup), then click Close & Apply again.

Joining the tables

The model view will now look like:

The next step we need to do is join the two tables by dragging the industrycode field from the lookup table to the main account fact table. You’ll see the “Create relationship” dialog:

Click OK on this and you should now see the two tables being joined:

Building the report

Now we’ve got the two tables linked together we can use them in the report. For example, drop on a clustered column chart visual, put the createdon field from the main account table into the Axis, the industrycodename field from the lookup table into the Legend, and the accountid field into the Values.

Because the account table is using Import mode you still get an automatic date hierarchy created for the createdon field, but we now get an automatic lookup for the correct labels for the optionset fields too.

Because the labels are generated automatically from your data you don’t need to worry about keeping a separate lookup table up to date – any changes you make in the make.powerapps.com portal will be immediately reflected in your report.