You’re offline. This is a read only version of the page.
88
Hi all,
I have been scouring the forum and the web for an answer to this question, it feels like the enigma code at this point.
I have a multi choice column in SharePoint that I want to bring into a multi choice column in a Dataverse table. When I bring the SP table into the dataflow it separates the multi choice into numerous rows with the same ID, i.e:
Row 1 - Choice A
Row 1 - Choice B
Row 1 - Choice C
I can pivot this so that the choices are columns and therefore I have one row per row ID with a flag as to which of the X amount of choices have been selected.
After doing some reading I thought that I would be able to create a concat into my multi choice column in dataverse that gave the choices a comma between them (using the numerical ID for the choice, not the display name), i.e:
Row 1 - 1, 2, 3 instead of Row 1 - Choice A, Choice B, Choice C
But alas, this did not work and did not import the choices to my column, instead returning me an error that "choice option 1,2,3 does not exist".
So, the age old question that man has tried to crack since the dawn of time remains. Just how do you integrate into a multi choice column in a dataverse table via a dataflow.
Come on Microsoft gods, there has to be an answer.
Thanks!
Tom
Hi,
I've recently had the same issue where I was importing values correct (e.g. in the format: 1,2,3) and it was geeting the same error as you.
I found that the error was occurring when your dataflow is trying to import into both multi-select choice columns AND also single-select choice columns.
Try building your dataflow so you only import your Multi-select columns and nothing else. I think you'll find it works fine then.
I've resorted to building two dataflows:
- one for every field EXCEPT my multi-select choice fields.
- a second dataflow to only UPSERT my multi-select choice fields that will run AFTER the first dataflow.
Hope this has helped! I've also attempted to get this article updated to improve the wording around this known limitation: https://learn.microsoft.com/en-us/power-query/dataflows/get-best-of-standard-dataflows#known-limitations
Exactly. I haven't tried pulling GlobalChoice OptionSet with multi select (I tried Local Single select-success; Local Multi select-failed)
Assuming it works, if you have the Label (Text), you need to map it to the Value (100000000 or whatever) in order to load it. Then just use Merge queries to bring the Value into your source data. If it's Multi select you'll have to then Group By and it's Text.Combine to concatenate your multiple Values into a single string (good post on this here ).
Maybe some confusion here, I thought your problem was getting the right syntax to update the Dataverse column with multiple choice values. I've been able to use a Dataflow to correctly update the choice values in the Dataverse table (without Power Automate).
I've also just discovered I can get global Choice column 'OptionSet' into dataverse using a Web API connection with URL https://mydataverseenvironmentname/api/data/v9.0/GlobalOptionSetDefinitions,
To get the Local Choice column OptionSets, which is what i really need, I've been successful with
(substitute yourcrm, tablename and columnname): use https://yourcrm.crm.dynamics.com/api/data/v9.0/EntityDefinitions(LogicalName=’tablename’)/Attributes/Microsoft.Dynamics.CRM.PicklistAttributeMetadata?$select=LogicalName&$filter=LogicalName eq ‘columnname’&$expand=OptionSet . Thanks to Carl de Souza's post here. HOWEVER, it does not seem to work for Local Choice column where Multiple selection is enabled....(update 21st Dec)....
...LOCAL CHOICE COLUMN WITH MULTI SELECT ENABLED
This can be done using hidden Stringmaps table (actually this approach should work for any kind of Choice column). Thanks to Michael Strohmayer for his Power Automate post.
This URL works in a browser...
https://yourcrm.crm11.dynamics.com/api/data/v9.1/stringmaps?$filter=objecttypecode eq 'tablename' and attributename eq 'columnname'
I've used it successfully in Power Query in Power BI as follows:
let
Source =
Json.Document(Web.Contents("https://yourcrm.crm11.dynamics.com/api/data/v9.1/stringmaps?$filter=objecttypecode eq 'tablename' and attributename eq 'columnname')"),
value = Source[value],
#"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", { "value", "attributevalue"}, { "Column1.value", "Column1.attributevalue"})
in
#"Expanded Column1"
e.g. Using Dataflow only, I connect to both above tables, use merge queries in Power Query to convert the Label into a Value, and insert the correct records with correct multiple choices into Dataverse target table.
I have data I want to load:
| Item | Category |
| Record1 | Health |
| Record2 | Health, Housing |
I query Dataverse to get all possible Choices*
| Label | Value |
| Health | 866660002 |
| Housing | 866660003 |
| Finance | 866660004 |
If you can't get the LocalChoice OptionSet from dataverse, I'd just create it in Excel manually (or maybe there is a tool in xrmtoolbox?)
*So it's just a one-off pain to create the 'lookup table' rather than being able to connect to the dataverse column to retrieve all possible choices (Label and Value).
I also ran into this issue, and after some errors I did manage to update a record with 2 choice values using a Dataflow with a Text column containing 866660002,866660003 (those being 2 of the values of my choices).
The second issue I still have is how to get the list of available Choice Labels and Values in a Dataflow, so I can map the text values I'm loading from Excel to the appropriate 'Value' (number) for the Dataverse Choice. I have a text label e.g. 'Health' and I need to lookup the Choice column 'table' to get the Value e.g. 866660002. Has anyone managed to do that?
@cchannon UPDATE
I have just had a call with Microsoft support and they explained that at present this is not a feature that is possible despite the post on the below:
Field mapping considerations for standard dataflows - Power Query | Microsoft Learn
At present the only way to do the mapping is to import the choices "1,2,3" to a string of text column and then run a Power Automate that picks these values up and updates the multiple choice row.
I am raising a system improvement ticket to Microsoft so that hopefully we can do it directly in Dataverse Dataflow with Power Query straight away without the dependance on Power Automate.
Thanks,
Tom
Thank you for the advice, I can confirm that using the "" did not work as expected.
I have setup the power automate and works fine and will submit a support ticket. I'll post the results here when MSFT get back to me 🙂
Thanks!
Tom
#1
mmbr1606
22
Super User 2025 Season 1
#2
stampcoin
17
#3
ankit_singhal
11
Super User 2025 Season 1
Share
Report
All responses (
Answers (
