Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
In Dynamics 365, pick list values are stored in a separate table. When you access a table through a reporting system like Power BI, you will not get the values of the pick list, only the id (slightly simplified). For some time now, I have been struggling with a pick list from Dynamics 365 in Power Query. Using the Odata.Feed data source in Power Query I was able to find the desired pick list after expanding some columns and filtering some rows of a table named 'picklist'. However, it turned out that the values in the picklist neither corresponded with the actual list in Dynamics, nor with the table I wanted to insert the pick list values into. The reason for this miss-match is unknown, but I suspect that the pick list has been changed after its initial creation.
Solved! Go to Solution.
After a tip fro a colleague, which is a Dynamics 365 developer, I decided to try accessing the pick list directly as a web data source rather than a Odata feed. The trick is to go straight to the pick list using the web API for dynamics. The following string was provided in the URL-field:
https://[myCompany].crm4.dynamics.com/api/data/v9.0/EntityDefinitions(LogicalName='[myTable]')/Attributes(LogicalName='[myPickList]')/Microsoft.Dynamics.CRM.PicklistAttributeMetadata?$select=LogicalName&$expand=GlobalOptionSet($select=Options)
Some more navigational steps where needed to get to the list it self. Below is the entire M-code. Replace [myCompany], [myTable] and [myPickList] with your own. You will probably be asked for login method and credentials after applying this.
let Source=Json.Document(Web.Contents("https://[myCompany].crm4.dynamics.com/api/data/v9.0/EntityDefinitions(LogicalName='[myTable]')/Attributes(LogicalName='[myPickList]')/Microsoft.Dynamics.CRM.PicklistAttributeMetadata?$select=LogicalName&$expand=GlobalOptionSet($select=Options)")), GlobalOptionSet = Source[GlobalOptionSet], Options = GlobalOptionSet[Options], #"ToTable" = Table.FromList(Options, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expand Column1" = Table.ExpandRecordColumn(#"ToTable", "Column1", {"Value", "Color", "IsManaged", "ExternalValue", "ParentValues", "MetadataId", "HasChanged", "Label", "Description"}, {"Column1.Value", "Column1.Color", "Column1.IsManaged", "Column1.ExternalValue", "Column1.ParentValues", "Column1.MetadataId", "Column1.HasChanged", "Column1.Label", "Column1.Description"}), #"Expand Column1.Label" = Table.ExpandRecordColumn(#"Expand Column1", "Column1.Label", {"LocalizedLabels"}, {"LocalizedLabels"}), #"Expand LocalizedLabels" = Table.ExpandListColumn(#"Expand Column1.Label", "LocalizedLabels"), #"Expand LocalizedLabels1" = Table.ExpandRecordColumn(#"Expand LocalizedLabels", "LocalizedLabels", {"Label"}, {"Label"}), #"Remove other columns" = Table.SelectColumns(#"Expand LocalizedLabels1",{"Label", "Column1.Value"}) in #"Remove other columns"
After a tip fro a colleague, which is a Dynamics 365 developer, I decided to try accessing the pick list directly as a web data source rather than a Odata feed. The trick is to go straight to the pick list using the web API for dynamics. The following string was provided in the URL-field:
https://[myCompany].crm4.dynamics.com/api/data/v9.0/EntityDefinitions(LogicalName='[myTable]')/Attributes(LogicalName='[myPickList]')/Microsoft.Dynamics.CRM.PicklistAttributeMetadata?$select=LogicalName&$expand=GlobalOptionSet($select=Options)
Some more navigational steps where needed to get to the list it self. Below is the entire M-code. Replace [myCompany], [myTable] and [myPickList] with your own. You will probably be asked for login method and credentials after applying this.
let Source=Json.Document(Web.Contents("https://[myCompany].crm4.dynamics.com/api/data/v9.0/EntityDefinitions(LogicalName='[myTable]')/Attributes(LogicalName='[myPickList]')/Microsoft.Dynamics.CRM.PicklistAttributeMetadata?$select=LogicalName&$expand=GlobalOptionSet($select=Options)")), GlobalOptionSet = Source[GlobalOptionSet], Options = GlobalOptionSet[Options], #"ToTable" = Table.FromList(Options, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expand Column1" = Table.ExpandRecordColumn(#"ToTable", "Column1", {"Value", "Color", "IsManaged", "ExternalValue", "ParentValues", "MetadataId", "HasChanged", "Label", "Description"}, {"Column1.Value", "Column1.Color", "Column1.IsManaged", "Column1.ExternalValue", "Column1.ParentValues", "Column1.MetadataId", "Column1.HasChanged", "Column1.Label", "Column1.Description"}), #"Expand Column1.Label" = Table.ExpandRecordColumn(#"Expand Column1", "Column1.Label", {"LocalizedLabels"}, {"LocalizedLabels"}), #"Expand LocalizedLabels" = Table.ExpandListColumn(#"Expand Column1.Label", "LocalizedLabels"), #"Expand LocalizedLabels1" = Table.ExpandRecordColumn(#"Expand LocalizedLabels", "LocalizedLabels", {"Label"}, {"Label"}), #"Remove other columns" = Table.SelectColumns(#"Expand LocalizedLabels1",{"Label", "Column1.Value"}) in #"Remove other columns"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
76 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |