Dynamic option sets in PowerBI

I recently had a requirement from a customer who needed to filter reports based on global option sets. The kicker for this requirement was that the option set would change rapidly for some time (don’t ask), so they needed a dynamic way to retrieve the labels and use them in a report.
Now, there are several solutions out there to retrieve the option set data, and I got some tips from the talented Ulrik “Why is he not an MVP yet” Carlsson. While his solutions work, I don’t want a static transformation in PBI, I don’t want a custom entity to store information already in the system, and I don’t want to use fetchxml as a source. Are those unreasonable demands? Maybe. Is that going to stop me? Never.
So to start with, I checked the global option set definitions for my organization, found from the following URL
https://<org>.crm4.dynamics.com/api/data/v8.2/GlobalOptionSetDefinitions
Sure enough I found all the option sets in the system, so I tried to add that url as a source in PowerBI to see if I can get the option set values. After waiting some time for it to load I can see that there are no lists or subsets to load that gives me anything useful.

So I try to load up the direct url for the option set in question, using the metadata id of the option set. The url looks like this:
https://<org>.crm4.dynamics.com/api/data/v8.2/GlobalOptionSetDefinitions(bd3519cf-3d67-43f2-8a94-d6849872f615)
While this does give me a result, it’s not anything I can use.

So I load the URL into chrome to look at the data. Here I can clearly see all of the option sets, complete with locales and values. That means that the data is available, but I am not getting it in the format I want.

Alternative 1, parse the content as a JSON document

Instead of using an OData.Feed to load the data, I can just take the specific optionset metadata and load it into a Json.Document instead. To do this, create a new blank query, then paste the following value
= Json.Document(Web.Contents("https://<org>.crm4.dynamics.com/api/data/v8.2/GlobalOptionSetDefinitions(bd3519cf-3d67-43f2-8a94-d6849872f615)
This will provide you with “Options” as an attribute which contains a list of records. You can then click on the List to navigate to it.

You now get a list of records, where you can right click the column and select “To Table”. This will convert the list into a table, and from here you can expand the columns. I chose to expand Value and Label, as that will give me the option set int value and the label string. The label is a record itself, so you have to expand it to get more options. The choices here is LocalizedLabels and UserLocalizedLabel. The difference being that the LocalizedLabels contains all the labels for all the localizations defined in your system, while the UserLocalizedLabel contains only the label for the user’s language.

UserLocalizedLabel

Expanding the UserLocalizedLabel gives us a new record, which we then can expand to Label, LanguageCode, and other additional metadata attributes. I’ve chosen to expand the first two, and we now have a useable table with both int values as well as label strings in the user’s language

You can now rename the columns and use them to join with the primary records in your PBI report.

LocalizedLabels

Expanding the LocalizedLabels will give us lists of labels for each value. You can’t expand a list to new columns straight away, so you will have to expand the table with new rows. If you have additional languages, which is probably why you’re doing this, then you will duplicate the integer values for all option set values that have several language definitions.
In my example I’ve just duplicated the table because I don’t have multiple languages enabled, so don’t worry about duplicate values, it’s on purpose.

So the next step is to right click the Column1.Value column, and select group by. Give the new column a sensible name (I named mine labels), then choose operation “Max” and select the label column as the column. This is just to get a basis for the grouping.
Now, in the next stage, the formula should look somewhat like this:
= Table.Group(#"Appended Query", {"Column1.Value"}, {{"Labels", each List.Max([Column1.Label.LocalizedLabels.Label]), type text}})
You need to change it so it looks like this instead:
= Table.Group(#"Appended Query", {"Column1.Value"}, {{"Labels", each Text.Combine([Column1.Label.LocalizedLabels.Label], ","), type text}})
The result will look something like this:

Now, right click the Labels column and select “Split Columns” –> “By delimiter”. Choose to split each occurrence. The resulting table now looks like this:

You can now give the columns sensible names and use them in your PBI report.

Alternative 2, add query options to get the list of labels

The other solution to retrieve the odata feed is to add additional columns. As we saw earlier the OData.Feed(… just gave us metadata about the entity, but none of the options. This is because the options, which includes data, is excluded by default. To include them in the query simply change the source definition from this:
= OData.Feed(https://<org>.crm4.dynamics.com/api/data/v8.2/GlobalOptionSetDefinitions(4565bea1-7c02-e811-8113-5065f38bb391))
To this:
= OData.Feed(https://<org>.crm4.dynamics.com/api/data/v8.2/GlobalOptionSetDefinitions(4565bea1-7c02-e811-8113-5065f38bb391), null, [MoreColumns = true])
The “null” is for additional headers, while the second is a PBI record which states that MoreColumns should be true (default false). All the available options are specified in the following article https://msdn.microsoft.com/en-us/library/mt260868.aspx
The result of this is the following screen.

We now have an attribute named “More Columns”. Clicking on that will navigate into the record, which is a list of records. Right click the column and choose “To Table”. You will now have a table where you can expand the columns just like specified in alternative 1.

Wrap up

Getting option set labels isn’t obvious when you’re creating reports in Power BI thanks to how attributes and attribute metadata is defined in Dynamics 365. Luckily there are ways to retrieve additional data in a dynamic way which allows us to utilize the system in awesome ways.
Until next time!