cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VoltesDev
Helper IV
Helper IV

Data from Dataverse Choice column

Hi guys,

 

Need some clarification. I'm using Dataverse as my datasource, when my table have a Choice column like below:

VoltesDev_0-1653891902791.png

And this collumn/fields accept multiple value, so the data will be like this :

VoltesDev_1-1653891994619.png

 

When I connect it from Power BI, either Import Mode or Direct Query, I should get the NUMERIC VALUE AND DESCRIPTION (NAME), am I right ?

 

Because currently my Power BI only showing the numeric like this :

VoltesDev_2-1653892200452.png

 

Anyone know why ? and how to solve this issue ?

 

Thanks,

1 ACCEPTED SOLUTION

Hi  , 

On this post I have found an answer some time ago

https://community.powerbi.com/t5/Desktop/Can-t-see-the-display-value-of-a-choice-column-from-a-table...

 

 

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. 

MFelix_0-1653926885763.png

@VoltesDev


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

11 REPLIES 11
fep
Frequent Visitor

Is this the answer for getting numeric value from choice column? When exporting a dataverse table to excel will give me a numeric column, in dataverse is a text field.

Hi @fep ,

 

That happens because excel recognize the format and place it has number if it has no text characters.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



fep
Frequent Visitor

@MFelix Sorry I meant the Lookup column. I excel this return the primary column (textfield) and the numeric value from the alternate key. This do not happen in Power BI.

Hi @fep ,

 

I got lost by this, what do you want to show exactly? what is the calculations you are searching for?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



MFelix
Super User
Super User

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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 ->

VoltesDev_0-1653920163790.png

 

 

 

Thanks

Hi  , 

On this post I have found an answer some time ago

https://community.powerbi.com/t5/Desktop/Can-t-see-the-display-value-of-a-choice-column-from-a-table...

 

 

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. 

MFelix_0-1653926885763.png

@VoltesDev


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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 @sgoldfeder ,

 

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.