Dynamics 365: Retrieve Multi Select Option Set Value in Power BI

You may have heard 'Choice' and 'Choices' instead of 'Option Set' and 'Multi Select Option Set'. Here is no difference between them, you can choose whatever terminology you want. I just want to let you know that Microsoft has terminology updates that you can read in here. To avoid any confusion, I will use the old terminology in this article.

In order to understand what feature or solution I am describing here, I propose you to begin with understanding the problem itself. The material that I will use in this article is here.

Problem

I'm using the Common Data Services connection to export entities from CRM Dynamics. It works great in that it is able to export the option set name for single select option sets. However, it does not work for multi-select option sets.

I do not get an option to select the value for this field.

Solution

I used XrmToolBox + Postman to solve this problem. Although in fact, you can limit yourself only to what I describe here and just substitute your own values and attributes.

For this example, I'll recreate this business case so that we can parse it in detail.

I have created a new option set "Patient Status" which can be:

• In Treatment
• Released
• Registered
• Redirected

And this is how it should have looked:

But instead, in Power BI, we see this:

This is because CDS exports its data this way without any temporary or intermediate tables. How it is usually done - if the relationship between tables is N:N, then a certain intermediate table is created that distributes records between the tables, in this example it could be Patient_PatientStatus_Table.

But such a table does not exist and we have to look for an alternative to solve this problem. Therefore, we resort to a solution using XrmToolBox.

XrmToolBox - is a Windows application that connects to the CDS for Apps.

If we try to find the PatientStatus table in order to import it into PowerBI, then we will fail. Since all option set records are stored in the hidden StringMaps table, and we can see this table only through the XrmToolBox. We look for this table and set the condition to it that the attributename should be the name with which you defined your option set. In my case, it is like this:

If we compare the attributevalue with the patient status column in Power BI, we understand that these are the records we need. Almost everything, since WebAPI works with OData, we will need to form the correct query that will return the desired result.

Now we turn to Postman. In order to send a request to WebAPI in Postman, you may need to go through two-factor authentication. Everything is written in detail here, but we will immediately proceed to forming the correct request.

If you set up your postman the way I did, then you should get a response 200. Now we need to write an OData query, which will not give us all the results (we don't need 1.46MB of extra data), but only what we need.

You can just put your own value instead of VARIABLE and make a request.

{{env-url}}/api/data/v8.2/stringmaps?$apply=groupby((attributevalue,value))&$filter=contains(attributename,%27VARIABLE%27)

As a result, we should get the following response. Surely you already have a question - why are we doing all this. Well, now we can use this URL and received this dataset in Power BI and create our own third table, with which we will link the target table.

After importing the data, app will open an editor query.

To transform this List, we have two options:

1. Open Advanced Editor and paste this piece of code there

let
    Source = "...",
    value = Table.FromRecords(Source[value]),
    #"Changed Type" = Table.TransformColumnTypes(value,{{"attributevalue", type text}})
in

    
    #"Changed Type"
    

2. After clicking on List, write in the function editor:

 = Table.FromRecords(Source[value])

As a result, we will get what we need:

We are now setting up a relationship.

In principle, everything is ready. All that remains is to add this script to your advanced editor:

let

    Source = Cds.Entities("URL", [ReorderColumns=null, UseFormattedValue=null]),
    entities = Source{[Group="entities"]}[Data],
    ENTITY_PLURAL_NAME = entities{[EntitySetName="ENTITY_PLURAL_NAME"]}[Data],
    #"Added Custom" = Table.AddColumn(ENTITY_PLURAL_NAME, "MultiSelect Split", each Text.Split([MULTISELECT_FIELD], ",")),
    #"Expanded MultiSelect Split" = Table.ExpandListColumn(#"Added Custom", "MultiSelect Split"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded MultiSelect Split",{{"MultiSelect Split", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"MultiSelect Split", Text.Trim, type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Trimmed Text", {"MultiSelect Split"}, StringMaps, {"attributevalue"}, "StringMaps", JoinKind.LeftOuter),
    #"Expanded StringMaps" = Table.ExpandTableColumn(#"Merged Queries", "StringMaps", {"value"}, {"StringMaps.value"})
in

    #"Expanded StringMaps"
  

After you enter this script, change all these fields to what you need. In my case, it would look like this:

Then I would see the result like this:

Now we need to group these records by row ID:

We will get the following result:

Now you just need to separate StringMaps.value from MyTable and write all records separated by comma. The following script will help you do this:

= Table.Column([MyTable], "StringMaps.value")

Super! We managed to do it with you! Now let's just get all the fields back. We select Expand Fields, but do not add three fields: Id, MultiSelect Split, StringMaps.value.

Then we just have to remove the duplicates:

And as a result, everything worked out for us:

I hope this article will help you in developing reports in Power BI. You can share it with your friends so that they can solve this problem too. Thank you for your attention!