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.
I AM SPENDING MORE TIME THESE DAYS CREATING YOUTUBE VIDEOS TO HELP PEOPLE LEARN THE MICROSOFT POWER PLATFORM.
IF YOU WOULD LIKE TO SEE HOW I BUILD APPS, OR FIND SOMETHING USEFUL READING MY BLOG, I WOULD REALLY APPRECIATE YOU SUBSCRIBING TO MY YOUTUBE CHANNEL.
THANK YOU, AND LET'S KEEP LEARNING TOGETHER.
CARL
Carl this article was extremely helpful. Thank you!
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?
Adding the stringmap entity to the oData feed would be the real solution. I wonder why this hasn’t been done?