Dynamics CRM Option Sets and Power BI

3 Comments

When using Power BI to connect to Dynamics CRM. there is a problem in that option set values are retrieved as their number values, not their actual text label value. The CRM OData feed returns this value, so it is up to the developer to get the actual text value. Doing so isn’t so easy, as we can’t simply look it up. There are different ways to get around this issue, including doing a “replace” in Power BI desktop, which isn’t very maintainable.

Take the contact record for example. There is a field on the record called “Preferred Method of Contact”. The options for this field are shown below:

You can see the field values from Power BI below showing “1” below, instead of “Any” which is the label equivalent:

A clean way to deal with these option sets is the Power BI Option-Set Assistant, provided courtesy of Gap Consulting, which runs in the XMLToolBox for Dynamics CRM. Download and run the XMLToolBox and you will see the Power BI Option-Set Assistant (you may need to enable it):

Click Load Entities to display all the CRM entities:

Select the option set you want and press “Create records for selected option sets”. Note you can select everything as an easy option:

The first time you should see this message:

Click Yes, and the entity will be created in CRM:

Looking in Advanced Find, you can see the Option Set and the Value:

Now, back in Power BI, add gap_PowerBIOptionsetRefSet:

On the AccountSet, select Merge Queries:

Merge the Value columns:

This will create a new column. Select the column and check gap_Label:

You will now see the label:

The nice thing about this approach, is if there are additional values added to the option sets, you can rerun this tool to populate the table.

 

ABOUT CARL DE SOUZA

Carl de Souza is a developer and architect focusing on Microsoft Dynamics 365, BI, Web, Cloud and Data Science.

carldesouza.comLinkedIn Twitter | YouTube

 

3 Responses to Dynamics CRM Option Sets and Power BI

  1. Can you only do one option set at a time? I selected all to be created and when I added to Power BI, they are all there but it has to reference different columns in the original data so I don’t understand how to manage that. Any thoughts?

Leave a Reply

Your email address will not be published. Required fields are marked *