CRM option set problem

[Guustc]

I am using PowerBI desktop with a CRM2016 online dataset via the OData feed an i am having problems with the CRM option sets. Options sets are not supported in the Odata feed and they come with a numeric value which needs to be transformed or replaced manually by the value in the option set.

There are some topic about this on this community, in which most of the time the XRM toolbox Powerbi options set assistent tool has been named as a solution. However, the gapconsulting blog post about this is offline and i can not find any information about the tool.

By now I have managed to run the tool and create a option set table to import in my powerbi Application but I have problems connecting the table to my dataset. I therefore explain my problem more briefly with a picture:

I have these two tables and i need to replace the value in the potential and customercategory columns by the values in the column Optionlabel of the table Optionset. The problem is, that i can not use for example a direct relationship or an inner join because the values in each optionset are almost the same and not unique. An optionset can contain many CRM fields and many value's. 

How can I now get the values in the columns potential and customercateogry replaced by the Optionlabel values from the table Optionset?

[Guustc · Accepted solution]

I already found a solution that was actually quite simple...

Add a new formula column to the table with the option set value's and use the LOOKUPVALUE DAX formula in the table editior (not the query editor) mode and search for the gap_optionsetschema values. Next, add an extra search parameter to the formula so you can search on the field option set name AND the option set value and the right value will be filled in in this column.

You need to do this for each option set column, but it is quite simple and the quickest way to solve this problem.

[PL1976]

Thanks for answering. Do you have an example?

[Eric_Zhang]

@Guustc

For your dataset, I'd re-model it by "unpivot" first and then do a "merge". Check more details in the attached pbix.

By the way, posting sample data as plain text is prefred as it is time saving. 🙂 

[Guustc]

@Eric_Zhang thanks for your option, but this dod not work on my dataset. Really appreciating your help!!

Because I think your understanding might be much easier with sample data, i have added an excel and .pbix sample file to show what i would like to do.

Now in the table, Opportunities, i like to replace the value rows with the gap_label rows with the same value from the Optionset table. 

This is an example, the number of columns and the number op different fields an optionset can increase. 

Hope you can help me with this :

Can't attach file so here it is: https://ufile.io/4ed7c

[Guustc]

Anyone an idea based on the example file that was posted?