Dataverse for Teams Choice Columns - Customizations published but column not showing

[mobul]

I have a dataverse table and added a choice column over a week ago. When I import the table into PowerBI /PowerQuery I can see the numeric choices but not the text value column. I have read this article in PowerBI forum and this one in PowerQuery form but the name column isn't showing up.

The choice column settings are

• Searchable (yes)
• Sync with global choice (yes) (also listed as recommended)
• Option column but does contain data 
• It has a schema name and logical name

I have

1. published all customizations (multiple times)
2. published the individual table then published all customizations
3. published the choice object itself 

Publish all customizations returns success message

I have refreshed the query in PowerBI and also created a new query to the same table but I can't get the name column to show up.

What else can I try or is there a limitation applied because its Dataverse for Teams ? 

[v-mdharahman]

Hi @mobul,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you can only see the numeric values not the corresponding text values in the imported table. As @DataNinja777 already responded to your issue, kindly go through his solution and accept it if you are able to solve your issue.

I would also take a moment to thank @DataNinja777, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[v-mdharahman]

Hi @mobul,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

[mobul]

Hi @v-mdharahman I responded to the post (link here) The problem appears to occur when I enable multiple responses ("Select multiple choices is allowed") . Is there a way to achieve the same outcome another way?

[v-mdharahman]

Hi @mobul,

Thank you for your detailed follow up and for testing different configurations. Based on your findings, it appears that Power BI does not automatically resolve multi-choice selections in Dataverse for Teams, which explains why the text labels are missing. Here are few options you can check to display multi-choice labels in PowerBI

* You can expand the Multi-Choice column in Power Query. First open Power Query and find the multi-choice column then click on the expand icon next to the column. If its available, select “.Value” or “.Label” to retrieve text labels.

If this does not work, you can check the following option.

* You can manually map numeric IDs to text labels. Since multi-choice values are stored as     

   numeric IDs, Power BI may require a mapping table to display text labels. Go through the 

   following steps:

1. Extract the numeric values from the multi-choice column 
2. Then manually create a reference table with the corresponding text labels.
3. Now expand the multi-choice column into separate rows in Power Query.
4. And then merge the numeric values with the reference table to bring in text labels.
5. Use "Text.Combine()" to reaggregate the text labels into a single field.

If manually mapping the values is not feasible, Power Automate can be used to extract choice labels from Dataverse and store them in a separate table. This table can then be connected to Power BI.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[mobul · Accepted solution]

Hi @v-mdharahman Your proposed solution is helpful but some points as they relate to my experience using powerbi desktop May 2024 version, against a powerBI server (not service) and dataverse for teams as they might be useful to others. 

1. Within powerquery in PowerBI there is no option to expand the multi choice column. It is a column within an existing table not a table within the it and there is no obvious extra table that i could identify as containing the choice labels 
2. The choice & multi-choice column values in Dataverse take the form of 3 sets of 3 digits separated by a comma e.g. ddd,ddd,ddd .The right most digit increments with the addition of each new choice. 
   1. However within PowerQuery each selected value is returned as 9 digit numbers separated by a commas between choices e.g. dddddddd,dddddddd. Its something to be aware of if creating a custom lookup table and need to split the column by delimiter. 

I created a new query that imported just the GUID (primary key) of the row and the choice column. I removed the 6 digits that (I presume wont change) and carried out a series of replace values to change the values to their correct labels. That left me with a comma separated string for each row. I split that column by delimiter comma to get a series of columns with the text choice for each selected row. I then unpivoted the primary key so i had a key value for each key and the text choice.

Don't forget to publish all customisations if you add a new choice to the dataverse column 

[DataNinja777]

Hi @mobul ,

This issue occurs because Dataverse for Teams handles choice columns differently in Power BI/Power Query. When importing a table, Power BI often shows only the numeric values from choice columns rather than the corresponding text labels, which are stored separately. One possible solution is to check if the text values exist in a related table. In Power Query, expanding the table might reveal a column with a .Value or .Label suffix, which should contain the text labels. If such a column isn’t present, it’s worth checking whether a separate table exists for the choice values, often named something like "TableName_ChoiceColumn". If found, this table can be merged in Power Query using a relationship between the numeric ID and its corresponding text value.

If the expected text values still do not appear, another step is to ensure proper synchronization settings in Dataverse. Since you have already set "Sync with Global Choice" to "Yes," it’s possible that the sync is delayed or not functioning correctly. Try disabling this option, publishing all customizations, and then re-enabling it before republishing. Additionally, confirming that the choice column is visible in Dataverse settings within Power Apps may help, as sometimes columns are hidden from Power BI by default.

Another potential fix is to force a schema refresh. This can be done by opening the Dataverse table in Power Apps, going to the schema settings, and selecting "Refresh." After publishing the changes, clearing the cache in Power BI and reloading the table may help ensure the latest schema updates are applied. If none of these steps work, there might be a limitation in Dataverse for Teams, as it does not offer full feature parity with the full Dataverse environment. Unlike full Dataverse, Dataverse for Teams has limited support for choice labels in Power BI, experiences slower synchronization with global choices, and often requires manual joins to retrieve text labels. If a direct solution isn’t available, using the Dataverse Web API or Power Automate to extract and load choice labels into Power BI could be a workaround. Let me know if you've already tried these steps, and I can suggest further troubleshooting.

Best regards,

[mobul]

Hi @DataNinja777  Thanks for responding.

1. There is no separate table listed in the environment that corresponds to option tables.  Neither is there a options table if I connect via Power Query. 
2. Sync with global choice: Its not possible to deselect or toggle the Sync with global choices either via the Teams app or make.powerapps.com environment. 

I already have other choice columns types in the same table and their numeric and value columns are showing up in my table. The value column is postfixed by "name" but these are not synced with a global choice.

I don't understand the point about refreshing the schema setting. Its not an option i see in make.powerapps.com when I have the table selected. I do have advanced tools options to add required objects, show dependencies, see solution layers, managed properties, table segmenation properties, publish table, add to solution. There is also an option to remove active customisations but thats disabled. 

I added a new choice columns but this time

• I didn't sync it with global choice and only allowed 1 choice and the numeric and name columns have appeared in PowerQuery after publishing all customisations
• I didn't sync it with global choice and allowed multiple choices and only the numeric column appeared in PowerQuery after publishing all customisations

It would seem to me the problem is the multiple choices. Is there a way to support multiple selected choices ?