Integrating to a Multiple Choice Column - Dataflow

(0) Share

Report

Posted on by THowkins96

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

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (13)

Answers (1)

cchannon 4,702 Moderator on at

Like (0)

Report

You need to pass a comma delimited string with the integer values of the choice options, not the labels. So you're probably looking for 1234567,1234568,1234569 or something like that.

https://learn.microsoft.com/en-us/power-query/dataflows/get-best-of-standard-dataflows#known-limitations

Was this reply helpful? Yes No
THowkins96 88 on at

Like (0)

Report

Hi @cchannon

Thank you for your response.

This is what I have already been doing, but I do not unfortunately get the integration 😞 See below:

The column is set up for multi choice enabled per below:

Is there anything I am missing here? I am using the values of the choices rather than the labels and sequencing them using commas with no spaces.

Thanks
Tom

Was this reply helpful? Yes No
cchannon 4,702 Moderator on at

Like (1)

Report
OK, so here's the footnote I found in the docs about it:

"Mapping to Choice fields configured with the multiple select option enabled is only supported under certain conditions. The dataflow only loads data to Choice fields with the multiple select option enabled, and a comma separated list of values (integers) of the labels are used. For example, if the labels are "Choice1, Choice2, Choice3" with corresponding integer values of "1, 2, 3", then the column values should be "1,3" to select the first and last choices."

I suppose you could try wrapping your value in double quotes to see if they literally meant to wrap those examples in quotations, though that seems like a real long-shot. More likely you're somehow falling under that "only supported under certain conditions" clause that they chose not to define... I would say give the string literal a try because it's easy, then open a ticket with MSFT. It might take ages to get an answer, but at least you have a chance at finding out what is and is not supported here.

In the meantime, what I'd do is dump the comma delimited string into a normal text col, then write a power automate flow that can pick up that text, split on the commas, and correctly set the multiselect.

Was this reply helpful? Yes No
THowkins96 88 on at

Like (1)

Report

@cchannon

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

Was this reply helpful? Yes No
Verified answer

THowkins96 88 on at

Like (1)

Report

@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

Was this reply helpful? Yes No
cchannon 4,702 Moderator on at

Like (0)

Report

Well that is disappointing. But at least we now know!

Was this reply helpful? Yes No
Mdoll 82 on at

Like (0)

Report

thanks.

Was this reply helpful? Yes No
jkic 150 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Mdoll 82 on at

Like (0)

Report

No...that is the problem.

Was this reply helpful? Yes No
jkic 150 on at

Like (0)

Report

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).

Was this reply helpful? Yes No