Forum Discussion
Dataverse for Teams Choice Columns - Customizations published but column not showing
Hi Anonymous 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.
- 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
- 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.
- 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
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,
Hi DataNinja777 Thanks for responding.
- 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.
- 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 ?