Power BI Query Editor shows some Dataverse choice columns with numerical value only

[N_TechWriter]

Hi Community!

I'm really stressed out about this because it doesn't make any sense. 

Some Dataverse choice columns are duplicated with "name" added to the end, giving me the values that I need.

Other choice columns aren't duplicated and they only return the numerical value.

Why would some columns publish with my customizations and others not?

This choice column showed up in the Query Editor with the "name" column next to it with the data I need.

These choice columns don't show a corresponding "name" column with the data I need.

What I have done so far:

1. The Dataverse table is published.
2. The choices tables for each lookup column have been published.
3. I have opened the table in Power Apps Solutions and selected "Publish all customizations".
4. I navigated to the choices lookup tables, published them and selected "Publish all customizations".
5. I navigated to Solutions and selected "Publish all customizations".
6. I have cleared the cache in Data Cache Management Options in the Power BI desktop settings, and restarted the program.
7. I have used Refresh Preview and Refresh All in the Query Editor.

Any help is much appreciated. Thanks!

Update:

As a workaround, I've removed those two choice columns and replaced them with lookups. It's not ideal since I will sometimes require multiple choices in the superintendent column, and as far as I know, a lookup won't allow me to make multiple selections. 

I still need help with this. Thanks again!

[Anonymous]

Hi @N_TechWriter ,

Here are some steps you can try to resolve the issue:
Check the settings of the choice columns that are duplicated with “name” added to the end. Make sure that they are set to “local choice” instead of “global choice”.

Check the settings of the choice columns that aren’t duplicated and only return the numerical value. Make sure that they are set up correctly and have the same settings as the duplicated columns.

Try republishing the table and the choice tables, making sure to select “Publish all customizations” for each one.

Clear the cache in Data Cache Management Options in the Power BI desktop settings, and restart the program.

Solved: Duplicate columns are created in the Dataverse tab... - Power Platform Community (microsoft....

Solved: Dataverse Primary Key Column allowing duplicate va... - Power Platform Community (microsoft....

Use Microsoft Dataverse to detect duplicate records and merge - Power Platform | Microsoft Learn

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[N_TechWriter]

Hi @Anonymous,

I tried your suggestions and created other columns using "local choice". It seems that the duplicated columns with "name" added to the end is completely random. Sometimes the column is created, sometimes it is not. Sometimes it is created for a local choice, and sometimes it is created for a global choice.
Furthermore, the "name" column sometimes shows up 24 hours later for some and sometimes it never shows up.

Here's the thing, when I pass the table through a Power Automate action like 'Get a row by ID', I can see that the OData information is there in the body of the outputs.

Moving forward, my plan is to create tables with many duplicates of my choice columns with different Schema names, hope that one of them has the appropriate column created, then delete the columns that I don't need.

Thanks for your reply!