How do you query out the possible label values of the choice columns (a.k.a. picklists or option sets) in a Dataverse/Power Apps table (entity) using Power Query?
As part of building a dimension table, here’s what I came up with to answer this question. In addition to working with picklist columns, with a slight tweak, the below can be used to fetch the textual label values for true/false column choices (e.g. for a given column, maybe true is configured to show as “Yes”, and false as “No”).
Sharing this so it can hopefully help the next person with the same need…and so that I can remember how to get this information if I need it again down the road. 🙂
let
WebApiBaseUrl = "https://my-project.api.crm.dynamics.com/api/data/v9.2/",
LcidOfInterest = 1033,
FetchAttributes = (url as text, valuesCallback as function) as table =>
let
Result = Web.Contents(WebApiBaseUrl, [RelativePath = url]),
ToJson = Json.Document(Result)[value],
ConvertedToTable = Table.FromRecords(
ToJson,
type table[LogicalName = text, AttributeType = text, OptionSet = record, RequiredLevel = record]
),
TransformRenameRequiredLevel = Table.RenameColumns(
Table.TransformColumns(
ConvertedToTable,
{{"RequiredLevel", (_) as logical => [Value] = "ApplicationRequired"}}
),
{{"RequiredLevel", "Required"}}
),
AddedValues = Table.AddColumn(
TransformRenameRequiredLevel,
"Values",
each LocalizedLabelValues(valuesCallback([OptionSet])),
type { text }
),
RemovedColumns = Table.RemoveColumns(AddedValues, {"OptionSet"}),
RenamedColumns = Table.RenameColumns(RemovedColumns, {{"LogicalName", "ColumnName"}})
in
RenamedColumns,
LocalizedLabelValues = (options as list) as list =>
let
LocalizedLabels = List.Combine(List.Transform(options, each [Label][LocalizedLabels])),
LocalizedLabelsOfInterest = List.Select(LocalizedLabels, each [LanguageCode] = LcidOfInterest),
LabelValues = List.Transform(LocalizedLabelsOfInterest, each [Label])
in
LabelValues,
Result = FetchAttributes(
"EntityDefinitions(LogicalName='my_entity')/Attributes/Microsoft.Dynamics.CRM.PicklistAttributeMetadata?$select=LogicalName,AttributeType,RequiredLevel&$expand=OptionSet",
each [Options]
)
in
Result
Output is a four column table:
- ColumnName—Name of the choice column
- AttributeType—”Picklist” (or if adjusted to return Boolean columns, “Boolean”)
- Required—
trueif the column is application required, otherwisefalse) - Values—List of text labels of the possible values for the column
Notes
- The above fetches local option sets, which is all I needed for my purposes. To fetch global option sets, I believe you can change the
$expandin the first argument passed to FetchAttributes from “OptionSet” to “GlobalOptionSet”, then slightly adapt the M code to reflect the slightly different structure of the JSON that will then be returned (or you could$expand=OptionSet,GlobalOptionSetat the same time and adapt the M code to handle reading both…). - If, instead of picklist (optionset) values, you want the textual labels for Boolean columns, change the Result step to the following:
FetchAttributes(
"EntityDefinitions(LogicalName='my_entity')/Attributes/Microsoft.Dynamics.CRM.BooleanAttributeMetadata?$select=LogicalName,AttributeType,RequiredLevel&$expand=OptionSet($select=TrueOption,FalseOption)",
each {[TrueOption]} & {[FalseOption]})
- You can also easily combine picklist and Boolean column details into a single table by combining the output of the two EntityDefinition invocations. (This is what I ended up doing to provide what my dimension table needed.)
- A possible adaption of the above would be to return not just textual labels but also the colors associated with those labels (e.g. so that those colors can be used in reporting).