Display OptionSet Label instead of Value – Power BI Report

As a limitation Power BI with Dynamics 365 does not automatically map optionset values with labels.  By default optionset column in Power BI report shows values. To replace values with corresponding labels, below are steps to follow

1. 1. Create OptionSet query table – For each optionset in your report, you would need to create optionset query which will be merged with your entity query table. This merge is similar to JOIN operation in SQL which maps optionset values and display labels.
   2. • Go to Edit Queries, which will open Query window
      • On left hand side under Queries, right click and click on New Query -> Blank Query
        
      • Query format:

      • = #table(
        {“Value”,”Label”},
        {
        {Value1,”Label1″},
        {Value2,”Label2″}
        }
        )

      • For “Is Closed” optionset with Yes/No options, below would be query

      • = #table(
        {“Value”,”Label”},
        {
        {913210000,”Yes”},
        {913210001,”No”}
        }
        )

      • 
      • Right click on Query1 and rename it to “IsClosedOptionSet”
      • Similarly “PreferredDayOptionSet” query will be created
   3. Merge Queries – Second step would be to merge entity query with optionset queries we created in first step.
   4. • Select entity query in my case it is accounts and select Merge Queries option
      • In Merge query window – select optionset column in entity table, select optionset query in dropdown and select Label column in optionset table. Power BI will automatically match rows based on selected columnsNote – use Left Outer join if option set column has null values else Inner Join
      • You will see one more column added to your report. Click on arrow icon in new column header and select only Label
      • Similarly perform merge queries for IsClosedOptionSet

Now you should be able to display Label instead of Value in your report.