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 Anonymous 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?
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:
- Extract the numeric values from the multi-choice column
- Then manually create a reference table with the corresponding text labels.
- Now expand the multi-choice column into separate rows in Power Query.
- And then merge the numeric values with the reference table to bring in text labels.
- 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.
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