Data from Dataverse Choice column

[VoltesDev]

Hi guys,

Need some clarification. I'm using Dataverse as my datasource, when my table have a Choice column like below:

And this collumn/fields accept multiple value, so the data will be like this :

When I connect it from Power BI, either Import Mode or Direct Query, I should get the NUMERIC VALUE AND DESCRIPTION (NAME), am I right ?

Because currently my Power BI only showing the numeric like this :

Anyone know why ? and how to solve this issue ?

Thanks,

[fep]

Is this the answer for getting numeric value from choice column? When exporting a dataverse table to excel will give me a numeric column, in dataverse is a text field.

[MFelix]

Hi @fep ,

That happens because excel recognize the format and place it has number if it has no text characters.

[fep]

@MFelix Sorry I meant the Lookup column. I excel this return the primary column (textfield) and the numeric value from the alternate key. This do not happen in Power BI.

[MFelix]

Hi @fep ,

I got lost by this, what do you want to show exactly? what is the calculations you are searching for?

[MFelix]

Hi @VoltesDev ,

Choice fields in Dataverse are stored has numbers and not fields, please check this documentation how to get the values you need.

https://docs.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-choices-powerbi

If you need further assistance please tell me.

[VoltesDev]

Hi @MFelix 

But that document is for getting data from Datalake (which probably the source is Dataverse). 

My report datasource is directly to Dataverse, by using the Dataverse buton ->

Thanks

[MFelix · Accepted solution]

Hi  , 

On this post I have found an answer some time ago

https://community.powerbi.com/t5/Desktop/Can-t-see-the-display-value-of-a-choice-column-from-a-table...

So you have some older choice fields which are working correctly, but newly created ones don't?

The answer is this one:

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. 

@VoltesDev

[Anonymous]

With a lot of searching I was finally able to bring in the data using the web connector, what is the best approach to handling the join, with a field with multiple selected choices?

[MFelix]

Do you have two tables or a single one? How is the data looking?

If you have two tables one with the multiple options and another with the answers, the best options is to do a merge based on the ID column.

[Anonymous]

I have two tables, the first being my primary table, that contains my Choices column that looks like this:

599320000,599320001,599320002,599320003,599320004,599320005

Then I have my second table that has the IDs with the corresponding values from the web pull.

Do I need to split up my first column before I do the merge you mentioned?

I am very new to Power Bi so apoligies if this is basic stuff.

[MFelix]

Hi @Anonymous ,

If the values you present are in a single cell then yes otherwise you won't be abble to match the ID with the value in each line.