Map Dynamics CRM Option Set Values into Labels in Power BI

When you try to import the data from Dynamics CRM in Power BI, the option set values usually appears in the form of “Record“. And when you try to expand that column it shows the values of those records instead of the labels. Most of the time for reporting and visualization we need to have labels of option set instead of values. In this post we’ll see how you can set labels of option set instead of values.

For example if we are dealing with PurchaseTimeFrame Option in Oppurtunity data we can have multiple option values and replacing them manually can be too much time consuming. For this purpose we need to write a query in the editor and have to retrieve the option set labels first from Dynamics CRM using PickListMapping entity. We will be using the direct OData URL for this purpose. Since we only need SourceValue, TargetValue and ColumnMappingId columns we can mention them before hand. The final OData URL will look like below

https://your-org-url/XRMServices/2011/OrganizationData.svc/PickListMappingSet?$select=ColumnMappingId,SourceValue,TargetValue

We will get the following result

Epand the ColumnMappingId Name field and you can now see the name of the different OptionSets. Since we are only interested in the PurchaseTimeframe OptionSet we will filter out all other values.

Note that there are many duplicate values. We can remove them using the Remove Duplicates transformation.The final version of the PurchaseTimeFrameOptionSet query will return the following result

We now have two Queries for which the results need to be merged. In the Queries window we can select the Opportunities query and in the ribbon press the Merge Queries button. A Merge window will be displayed that will ask us for the second Query to merge the first one with and on what fields this has to be done.

From the first Query we can take the PurchaseTimeframe column and from the second Query the Target Value. When the OK button is pressed a new column labelled NewColumn of type Record is added to the Opportunities Query. If we expand this column and only select the SourceValue column we now have the Labels of the OptionSet. To clean things up we can remove the original PurchaseTimeFrame column and rename NewColumn.SourceValue to PurchaseTimeFrame.

Similarly you can map any option set values to its label using this way.