Forum Discussion
Data from Dataverse Choice column
Hi ,
On this post I have found an answer some time ago
So you have some older choice fields which are working correctly, but newly created ones don't?
The answer is this one:
This is a by-design scenario as Dataverse store Choice type value in numeric. As a workaround, mapping relationship between on choice type value can be exported via another API call, please try using Power BI Web connector and access source link via below:
https://<environmentName>/api/data/v9.2/EntityDefinitions(LogicalName='<TableName>')/Attributes(LogicalName='<Choice Name>')/Microsoft.Dynamics.CRM.PicklistAttributeMetadata?$select=LogicalName&$expand=GlobalOptionSet($select=Options)where environment name = org12345678..api.crm6.dynamics.com
Essentially adding another table with the value/name pairing and joining to the original table. I have about 12 choices so far in my database, so this is painful.
Hi VoltesDev ,
Choice fields in Dataverse are stored has numbers and not fields, please check this documentation how to get the values you need.
https://docs.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-choices-powerbi
If you need further assistance please tell me.
Hi MFelix
But that document is for getting data from Datalake (which probably the source is Dataverse).
My report datasource is directly to Dataverse, by using the Dataverse buton ->
Thanks
Hi ,
On this post I have found an answer some time ago
So you have some older choice fields which are working correctly, but newly created ones don't?
The answer is this one:
This is a by-design scenario as Dataverse store Choice type value in numeric. As a workaround, mapping relationship between on choice type value can be exported via another API call, please try using Power BI Web connector and access source link via below:
https://<environmentName>/api/data/v9.2/EntityDefinitions(LogicalName='<TableName>')/Attributes(LogicalName='<Choice Name>')/Microsoft.Dynamics.CRM.PicklistAttributeMetadata?$select=LogicalName&$expand=GlobalOptionSet($select=Options)where environment name = org12345678..api.crm6.dynamics.com
Essentially adding another table with the value/name pairing and joining to the original table. I have about 12 choices so far in my database, so this is painful.
With a lot of searching I was finally able to bring in the data using the web connector, what is the best approach to handling the join, with a field with multiple selected choices?
Do you have two tables or a single one? How is the data looking?
If you have two tables one with the multiple options and another with the answers, the best options is to do a merge based on the ID column.