Dynamics multi select option set export from CDS

[brownrobm]

I'm using the Common Data Services (CDS) connection to export entities from CRM Dynamics. It works great in that it is able to export the option set name for single select option sets. However, it does not work for multi-select option sets

I do not get an option to select the value for this field. For the life of me, I cannot figure out where to pull this option set value from. I've tried picklistmapping and GlobalOptoinsSetDefinitions with no luck.

[mhammo]

Hello,

Did you resolve this issue? I am running into the same thing. I can't find where these values are stored in CDS.

Mike

PS - suggesting someone go create a ticket is not a helpful answer.

[brownrobm]

To resolve the issue, I had to setup some new tables in Power BI.

I have an Advisor field in a table named Strategy that has multiple values available to it.

In order to get it decoded in a reasonable way, I ended up creating two tables:

"Advisor Lookup" - hidden table that contains a value/description for advisor

"Strategy Advisor" - table that expands all advisors for a given strategy id. 

Here's the code for Advisor Lookup (service root url is a variable that has my CRM URL in it. This code is copied from another post that states how to get multi-item lookup values from CRM. My CRM guy had to put the option set in a global list in order for the code to work. Unfortunately, I don't remember where I got the code from. It might have come from the XRM toolbox...):

let
Source = Json.Document(Web.Contents(ServiceRootURL &"/EntityDefinitions(LogicalName='gsi_reporting')/Attributes(LogicalName='gsi_advisorentity')/Microsoft.Dynamics.CRM.PicklistAttributeMetadata?$select=LogicalName&$expand=OptionSet($select=Options)")),
OptionSet = Source[OptionSet],
Options = OptionSet[Options],
#"Converted to Table" = Table.FromList(Options, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Value", "Color", "Label", "Description"}, {"Value", "Color", "Label", "Description"}),
#"Expanded Label" = Table.ExpandRecordColumn(#"Expanded Column1", "Label", {"UserLocalizedLabel"}, {"UserLocalizedLabel"}),
#"Expanded UserLocalizedLabel" = Table.ExpandRecordColumn(#"Expanded Label", "UserLocalizedLabel", {"Label"}, {"Label"}),
#"Expanded Description" = Table.ExpandRecordColumn(#"Expanded UserLocalizedLabel", "Description", {"UserLocalizedLabel"}, {"UserLocalizedLabel"}),
#"Expanded UserLocalizedLabel1" = Table.ExpandRecordColumn(#"Expanded Description", "UserLocalizedLabel", {"Label"}, {"Label.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded UserLocalizedLabel1",{{"Label.1", "Description"}})
in
#"Renamed Columns"

 

This creates a table with the CRM lookup code, label and description for each advisor. Then I take my Strategy table and create a new row for every advisor that has multiple entries per strategy in "Strategy Advisor":

let
Source = Cds.Entities("https://xxxxx.crm.dynamics.com", [ReorderColumns=null, UseFormattedValue=true]),
entities = Source{[G...

#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Renamed Columns", {{"GS Advisor List", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "GS Advisor List"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"GS Advisor List", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"GS Advisor List"}, #"Advisor Lookup", {"Value"}, "Advisor Lookup", JoinKind.LeftOuter),
#"Expanded Advisor Lookup" = Table.ExpandTableColumn(#"Merged Queries", "Advisor Lookup", {"Label"},{"AdvisorLookup.Label"}),

#"Renamed Columns3" = Table.RenameColumns(#"Expanded Advisor Lookup",{{"AdvisorLookup.Label", "Advisor"}}),
#"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns3",{"Strategy ID", "Advisor"})
in
#"Removed Other Columns"

 

The key steps here are:

"Split Column by Delimiter" - splits the Advisor column into multiple rows 

"Merge Queries" - Merges the Advisor Lookup table with the recently split strategy table. 

(almost every step here was done in the GUI and not hand coded)

Hope this at least puts you on the right track. Sorry I could not be more helpful, it's been a few months since I did this. 

[v-chuncz-msft]

@brownrobm 

To get faster and better technical support for this issue, you may directly create a support ticket.