Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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,
Solved! Go to Solution.
Hi ,
On this post I have found an answer some time ago
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsIs 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.
Hi @fep ,
That happens because excel recognize the format and place it has number if it has no text characters.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@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.
Hi @fep ,
I got lost by this, what do you want to show exactly? what is the calculations you are searching for?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Hi ,
On this post I have found an answer some time ago
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsWith 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?
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI 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.
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCheck out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
108 | |
108 | |
108 | |
90 | |
61 |
User | Count |
---|---|
171 | |
138 | |
132 | |
102 | |
86 |