Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

20 REPLIES 20
Anonymous
Not applicable

Has anyone found a working solution? I have tried all other suggestions and so far nothing has worked.

Yes, the solution is posted in this thread.  Use the 'publish all customizations' option. 

mwayte
Frequent Visitor

This also does not solve the issue for me - Any idea on the criteria for this Publish all Customisations solution to work? 

I am working within a dataverse for Teams environment

Anonymous
Not applicable

I have tried that multiple times and my Choices columns still show up in PBI with just the ID number and no value. 

fep
Helper I
Helper I

any solution for this problem? I only get the text value in power bi. Not the numerice value.

Hi @fep  the solution involving "publish all customizations" has consistently worked for me.

GrantR
Frequent Visitor

Ermagerd! It works... 

rtbergin
Advocate I
Advocate I

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

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

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 🙂

Anonymous
Not applicable

I Have the same problem

rtbergin
Advocate I
Advocate I

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

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. 

Anonymous
Not applicable

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!

Anonymous
Not applicable

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors