Getting Local Choice column labels (Multi select)

[Syndicate_Admin]

I thought I'd post this as I found very little info on the subject.  It started when my Dataflow returned only the integer 'Value' for my Local Choice column.  So I thought I'd add a query to get the Values and Labels of my choice column to use as a 'lookup' in the Dataflow.  It seems quite a common issue in PowerBI, PowerApps, Power Automate for which there are a few techniques shared, however, very few deal with choice columns with multi select enabled. 

I tried the Web API:

https://yourcrm.crm.dynamics.com/api/data/v9.0/EntityDefinitions(LogicalName=’tablename’)/Attributes/Microsoft.Dynamics.CRM.PicklistAttributeMetadata?$select=LogicalName&$filter=LogicalName eq ‘columnname’&$expand=OptionSet

(Thanks to Carl de Souza's post here) but it doesn't work for Choice columns with Multi Select enabled. 

I used the method described in Michael Strohmayer's post here to get the data in Power Automate, and then figured out how to tweak the URL to use in Power Query for Power BI/Dataflow....

GETTING LABELS FOR LOCAL CHOICE COLUMN WITH MULTI SELECT ENABLED

This can be done using hidden Stringmaps table (actually this approach should work for any kind of Choice column).  Thanks to Michael Strohmayer for his Power Automate post.

This URL works in a browser...

https://yourcrm.crm11.dynamics.com/api/data/v9.1/stringmaps?$filter=objecttypecode eq 'tablename' and attributename eq 'columnname'

I've used it successfully in Power Query in Power BI as follows:

let

  Source =

Json.Document(Web.Contents("https://yourcrm.crm11.dynamics.com/api/data/v9.1/stringmaps?$filter=objecttypecode eq 'tablename' and attributename eq 'columnname')"),

    value = Source[value],

    #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", { "value", "attributevalue"}, { "Column1.value",  "Column1.attributevalue"})

in

    #"Expanded Column1"

[Syndicate_Admin]

That's great Guido, I'm wondering what the pros and cons of these are.  Maybe performance I guess.  Anyway, great to go from no solution to several!

On a related issue I'm now talking to Microsoft Support about why the Canvas App Dataverse Action Environment.InsertOptionValue is failing, asking me to provide a (system generated) metadataid for a new option in the optionset.  If I get that resolved I'll feel well equipped to deal with these choice columns! 

[Syndicate_Admin · Accepted solution]

the WebAPI syntax for multiselect (following the example you posted) should be this one:

https://yourcrm.crm.dynamics.com/api/data/v9.0/EntityDefinitions(LogicalName=’tablename’)/Attributes/Microsoft.Dynamics.CRM.MultiSelectPicklistAttributeMetadata?$select=LogicalName&$filter=LogicalName eq ‘columnname’&$expand=OptionSet

however I personally use another syntax (I post the M code):

let
	Source = Json.Document(Web.Contents(DataverseUrl, [RelativePath="/api/data/v9.1/EntityDefinitions(LogicalName='sample_customtable')/Attributes(LogicalName='sample_choices')/Microsoft.Dynamics.CRM.MultiSelectPicklistAttributeMetadata?$select=LogicalName&$expand=OptionSet($select=Options)"])),
	OptionSet = Source[OptionSet],
	Options = OptionSet[Options],
	#"Option Table" = Table.FromList(Options, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
	#"Expand Columns" = Table.ExpandRecordColumn(#"Option Table", "Column1", {"Value", "Label", "Description", "Color"}, {"Value", "Option.Label", "Option.Description", "Color"}),
	#"Expand Label" = Table.ExpandRecordColumn(#"Expand Columns", "Option.Label", {"UserLocalizedLabel"}, {"Label.UserLocalizedLabel"}),
	#"Expand Label UserLocalizedLabel" = Table.ExpandRecordColumn(#"Expand Label", "Label.UserLocalizedLabel", {"Label"}, {"Label"}),
	#"Expand Description" = Table.ExpandRecordColumn(#"Expand Label UserLocalizedLabel", "Option.Description", {"UserLocalizedLabel"}, {"Description.UserLocalizedLabel"}),
	#"Expand Description UserLocalizedLabel" = Table.ExpandRecordColumn(#"Expand Description", "Description.UserLocalizedLabel", {"Label"}, {"Description"})
in
	#"Expand Description UserLocalizedLabel"

and for normal choice

let
	Source = Json.Document(Web.Contents(DataverseUrl, [RelativePath="/api/data/v9.1/EntityDefinitions(LogicalName='sample_customtable')/Attributes(LogicalName='sample_choice')/Microsoft.Dynamics.CRM.PicklistAttributeMetadata?$select=LogicalName&$expand=OptionSet($select=Options)"])),
	OptionSet = Source[OptionSet],
	Options = OptionSet[Options],
	#"Option Table" = Table.FromList(Options, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
	#"Expand Columns" = Table.ExpandRecordColumn(#"Option Table", "Column1", {"Value", "Label", "Description", "Color"}, {"Value", "Option.Label", "Option.Description", "Color"}),
	#"Expand Label" = Table.ExpandRecordColumn(#"Expand Columns", "Option.Label", {"UserLocalizedLabel"}, {"Label.UserLocalizedLabel"}),
	#"Expand Label UserLocalizedLabel" = Table.ExpandRecordColumn(#"Expand Label", "Label.UserLocalizedLabel", {"Label"}, {"Label"}),
	#"Expand Description" = Table.ExpandRecordColumn(#"Expand Label UserLocalizedLabel", "Option.Description", {"UserLocalizedLabel"}, {"Description.UserLocalizedLabel"}),
	#"Expand Description UserLocalizedLabel" = Table.ExpandRecordColumn(#"Expand Description", "Description.UserLocalizedLabel", {"Label"}, {"Description"})
in
	#"Expand Description UserLocalizedLabel"

my tool Dataverse REST Builder has the Power Query (M) syntax for the Retrieve Multiple message, including the code I posted above for getting the choice/multiselect choice labels

hope it helps