Power Query: How to get a Dynamics 365 pick list

[KnutErlandBrun]

Problem:

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.

[KnutErlandBrun · Accepted solution]

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"