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 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.
I have two tables, the first being my primary table, that contains my Choices column that looks like this:
599320000,599320001,599320002,599320003,599320004,599320005
Then I have my second table that has the IDs with the corresponding values from the web pull.
Do I need to split up my first column before I do the merge you mentioned?
I am very new to Power Bi so apoligies if this is basic stuff.