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.
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.
Hi Anonymous ,
If the values you present are in a single cell then yes otherwise you won't be abble to match the ID with the value in each line.