cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lnpapin
Helper I
Helper I

Can't see the display value of a choice column from a table in the Dataverse in power bi

I have imported tables from de Dataverse connector in PowerBI desktop, in that table i have a choice column called cr6bc_q3.  I only can show the value and not the name of the choice selected by the user in the powerapps.  Need help please

My table

lnpapin_0-1612804030449.png

My choice:

lnpapin_1-1612804054194.png

My result in power bi:

lnpapin_2-1612804072556.png

 

Thanks for the help!

 

1 ACCEPTED SOLUTION
rtbergin
Regular Visitor

Thank you @hannibalmads! This solution worked for me! No more workarounds 🙌

 

@GrantR I am also working within a Dataverse for Teams environment. And the Publish all customizations worked, however it was a bit tricky to find. Below are some screenshots that should show you how to get there.

 

rtbergin_0-1631802883801.png

rtbergin_1-1631802912116.png

View solution in original post

14 REPLIES 14
GrantR
Frequent Visitor

Ermagerd! It works... 

rtbergin
Regular Visitor

Thank you @hannibalmads! This solution worked for me! No more workarounds 🙌

 

@GrantR I am also working within a Dataverse for Teams environment. And the Publish all customizations worked, however it was a bit tricky to find. Below are some screenshots that should show you how to get there.

 

rtbergin_0-1631802883801.png

rtbergin_1-1631802912116.png

View solution in original post

GrantR
Frequent Visitor

Since the original post, it seems Microsoft have done some work. The name fields now show when using the Dataverse from a full environment. However, I'm using a Dataverse for Teams environment and it's still broken. I've an open support call they've been looking at for the last two weeks. Which reminds me I should chase them up....

hannibalmads
New Member

Found this description : After making changes to labels for a choice column, the table needs to have customizations published. (Link)

hannibalmads_0-1631741666062.png

 

So I went to the Solutions in my environment and clicked on Publish all customizations

hannibalmads_1-1631741700588.png

And now my <choice>name columns are populated 🙂

aleksandes
New Member

I Have the same problem

rtbergin
Regular Visitor

Same issue here.

 

Choice options reutrn two coulmns. One which contains a numeric value and another which has the word "name" trialing it. 

 

Seems like the the "name" column for the choice variables should be retuning the actual value but instead it's returning null. This has to be a bug right?

RyanGilreath
New Member

We are having the same issue as OP - a new choice field will not show the display name of the choices, but instead returns "null" in PowerBI/PowerQuery.

 

Other choice fields in the same table appear to be working correctly, a la showing both the numerical number and display name correctly.

 

When using the Excel add-in, we are able to see the display name.

 

I've looked everywhere for a setting/control in both Dataverse as well as PowerBI - but can't find anything that would control this.

 

It's not a huge list of choices, so a reference table can probably be stood up, but that's hardly ideal.

 

Any thoughts or other areas I can turn to for troubleshooting?

So you have some older choice fields which are working correctly, but newly created ones don't?

I logged a job with Microsoft support and their respsonse was: 

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. 

This workaround worked fine. A way Microsoft could solved this easy is that choices table could be available in the "datavaerse" view when performing a normal query with Dataverse. But now it has to be done manually, and then created a relations to the table you want to relate this label field

GrantR
Frequent Visitor

Going to bump this one. I'm also experiencing the same.  
The name coumn for choices from a dataverse source are importing into Power Bi as 'null'

v-kelly-msft
Community Support
Community Support

Hi @lnpapin ,

 

What do you wanna show in the table?Not sure whether it's an issue about power apps,if so,I would check with others and get back to you.

 

 

Best Regards,
Kelly

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

Hi,  I want to show the text value of the choice column from a table in the dataverse.  Normaly we have the numeric value in one column and the text in the other. But now on new table that i import into powerbi desktop, the text value doesnt show.  It used to work fine before, I tried different table with no luck

Hi  @lnpapin ,

 

Check the blog below which may help:

https://knowhere365.space/power-apps-get-dataverse-option-set-id-and-label/

 

 

Best Regards,
Kelly

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

Hello, I experience the same issue as the user above.

 

The table should show the value of the choice. That is, each choice defined in Dataverse has an ID (numeric) and a value (text string). In the example above, the ID shows in the column cr6b_q3 and the value should appear in the column cr6b_q3name, but the name column remains empty. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!