itelligent-i's avatar
itelligent-i
Icon for Advocate IV rankAdvocate IV
11 years ago
Status:
Needs Votes

CRM Online Dynamic Options sets PickListMappingSet

The CRM Online OData feed doesn't dynamically provide access to user defined Option Sets. When adding an Option Set to CRM it should be provided in the PickListMappingSet table
power bi
laurent_chinde1's avatar
laurent_chinde1
New Member
6 years ago
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

Recent ideas