CRM Online Dynamic Options sets PickListMappingSet

Very important for the acceptance of PowerBI within MS Dynamics CRM projects

Agreed!!

As well as Look up, OData feed does not support loading the Option sets or look ups

would be a welcome addition

Hi everyone, You can get the Option Sets value list by using the following Dynamics 365 API endpoint: https://.crm.dynamics.com/api/data/v8.2/GlobalOptionSetDefinitions(). The trick here is instead of using a normal OData call you put the API call inside a Json.Document(Web.Contents()), then convert the result in a table and keep the Options. Here a sample M Query: let Source = Json.Document(Web.Contents("https://.crm.dynamics.com/api/data/v8.2/GlobalOptionSetDefinitions()")), #"Converted to Table" = Record.ToTable(Source), Value = #"Converted to Table"{2}[Value], #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing () , null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"Value", "Label", "Description", "Color", "IsManaged", "MetadataId", "HasChanged"}, {"Value", "Label", "Description", "Color", "IsManaged", "MetadataId", "HasChanged"}), #"Expanded Label" = Table.ExpandRecordColumn(#"Expanded Column1", "Label", {"LocalizedLabels", "UserLocalizedLabel"}, {"Label.LocalizedLabels", "Label.UserLocalizedLabel"}), #"Expanded Label.UserLocalizedLabel" = Table.ExpandRecordColumn(#"Expanded Label", "Label.UserLocalizedLabel", {"Label", "LanguageCode", "IsManaged", "MetadataId", "HasChanged"}, {"Label.UserLocalizedLabel.Label", "Label.UserLocalizedLabel.LanguageCode", "Label.UserLocalizedLabel.IsManaged", "Label.UserLocalizedLabel.MetadataId", "Label.UserLocalizedLabel.HasChanged"}), #"Removed Other Columns" = Table.SelectColumns(#"Expanded Label.UserLocalizedLabel",{"Value", "Label.UserLocalizedLabel.Label"}) in #"Removed Other Columns" This can be optimized, but it's just for your reference. Best regards

I'm a bit disappointed that issue has not been resolved yet. This issue was posted in Aug 2015, its Aug 2018 now and this is still a problem. As a D365 cloud user, I'm unable to create reports that use user defined option sets and the workarounds are inelegant solutions.

OK - after some more digging and manipulating I found a partial solution. This is still too much effort for Business Line users, but a start. Kudos to CloudFront for their post here - https://www.cloudfronts.com/crm-option-set-in-power-bi/ The post doesn't work exactly as stated as it's a bit dated, but it can be manipulated to work. I had to forgo the Metadata browser portion of their solution as I couldn't find a way to get the OptionSets to appear. That said an easier way was to simply do an OData query in PowerBI for the entire Global Option Set list using my OData API - Example: https://contoso.api.crm.dynamics.com/api/data/v8.2/GlobalOptionSetDefinitions Once I had that I could get the MetadataID for each option set and then copy the table to excel and append the MetaDataID to the above query similar to CloudFronts answer. Example: https://contoso.api.crm.dynamics.com/api/data/v8.2/GlobalOptionSetDefinitions(ae2a3b9d-395a-4dbd-9abc-c32aeb10888b) This allows you to then query specific optionsets. After you Query each OptionSet you will get a list table. Towards the bottom you will see an Item labeled "Options". Click on "Table" next to it and that will expand the query and you can expand the fields and choose columns. After the above I basically created a new share point list of all option set query strings for users. Still a pain, but they can at least find and access the values now. Hope that helps, but REALLY hope the Dynamics team can work with PowerBI to get this done.

We recently moved to Dynamics. This is a huge issue for us (lots of option sets in data base) and had I known we would likely not have made the switch. My bad. These workarounds are impossible for most of our users. Very disappointed in Dynamics. If you promote Dynamics or CDS with Power BI capability for business line users, but this doesn't work, you are going to have some upset customers. Can someone please address this so a standard business user can pick the option set values easily, not just someone who can write JSON....