Problem displaying Dynamics CRM Option Set labels in Power BI reports when they are separated by a ;

[jonclay]

Hi everyone,

I'm attempting to display the values of a Dynamics 365 CRM Online Option Set in Power BI, but I'm having a problem. The Option Set values are, for some reason, hidden in Dynamics, so I only get e.g. 157420000 rather than the word Alumna. To remedy this I've created a manual table in Power BI and joined it to the Contact table where the Option Set values reside (as shown below):

This works fine until I come across a record that has two different Option Set values. For example, a record can be both an Alumna AND a Parent, so they would effectively show an si_type of 157420000; 157420001 in the Dynamics table (i.e. the Option Set values are separated by an ;). In this case, the records do not come out on the Power BI report at all.

Of course, one way around this would be to add all the possibilities into the manual table in Power BI, but this would take forever as there are thousands.

Is there a way (possibly using DAX?) to say "if the si_type field has 2 or more values separated by ; then show both values in the Column 1 field"? An excerpt of the manully created table in Power BI is shown below:



Many thanks,
Jon

[jonclay]

Hello there

Many thanks for your reply. I'll try that out later today, but it sounds as though it'll work.

Best wishes
Jon

[DataInsights · Accepted solution]

@jonclay,

In Power Query, you can split a column into rows. This would enable each row to join to your manual table.