Select Page

Recently I was working on developing a range of Power BI reports based on data from Dynamics 365. There were are few times when I needed to display OptionSet labels in the reports, and these labels were not available directly from within the entity. I had OptionSet codes but no labels. There are a few blog posts about this including one that requires installing a plugin, however it is relatively easy to retrieve these from the API with a few steps.

This answer on stackoverflow was helpful however it required passing the MetadataID of related entity rather than the LogicaName. To make my life easier I developed a Power BI function that would take the name of the entity and the name of the optionset and then perform two requests – the first to retrieve the related entity metadataID from the name, and then the second to retrieve the optionset labels using this ID.

(EntityName as text, OptionSetName as text, DynamicCRMAPI as text)=>
 Source = OData.Feed(DynamicCRMAPI & "/EntityDefinitions(LogicalName='" & EntityName & "')"),
 MetadataId = Source[MetadataId],
 OptionSetSource = OData.Feed(DynamicCRMAPI & "/EntityDefinitions(" & MetadataId & ")/Attributes/Microsoft.Dynamics.CRM.PicklistAttributeMetadata?$select=LogicalName&$expand=OptionSet,GlobalOptionSet"),

#"Get OptionSet" = OptionSetSource {[LogicalName=OptionSetName]}[OptionSet],
 Options = #"Get OptionSet",
 SelectOptions = Options[Options],

#"Converted to Table" = Table.FromList(SelectOptions, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Value", "Label"}, {"Column1.Value", "Column1.Label"}),
 #"Expanded Column1.Label" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.Label",
 {"LocalizedLabels"}, {"Column1.Label.LocalizedLabels"}),
 #"Expanded Column1.Label.LocalizedLabels" = Table.ExpandListColumn(#"Expanded Column1.Label", "Column1.Label.LocalizedLabels"),
 #"Expanded Column1.Label.LocalizedLabels1" = Table.ExpandRecordColumn(#"Expanded Column1.Label.LocalizedLabels", "Column1.Label.LocalizedLabels", {"Label"}, {"Column1.Label.LocalizedLabels.Label"}),
 #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1.Label.LocalizedLabels1",{{"Column1.Value", "Code"}, {"Column1.Label.LocalizedLabels.Label", "Label"}})
 #"Renamed Columns"

Unfortunately although this worked well in the desktop environment, the Power BI service environment wouldn’t load due the the combination of source requests and the dynamic variable being passed to the second request.

In the end I separated the two functions to load all entities names and IDs and then manually created a query with the entity metadata ID copied in.