Although I’m a big fan of using dataflows to carry out bulk imports into Dataverse, they’re not without their limitations – and not all of them are documented
What are dataflows?
Dataflows are a part of the Microsoft Power Platform and there are two types:
- Standard Dataflows – used to load data into Microsoft Dataverse tables from a wide variety of sources, including text files, spreadsheets and SQL.
- Analytical Dataflows – used to load data into Azure Data Lake for reporting purposes.
This post focuses on one of the undocumented quirks of Dataflows – importing data into a choice column that has been set as ‘Selecting multiple choices is allowed’.
What’s the problem?
According to Microsoft’s own documentation on the various limitations of Dataflows (which can be found here):
- 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.
So you would think that passing in:
1,2,3
into one of a multiple-select Choice column would work without a hitch – and usually, it does. But here’s where the problem comes in. If, in your field mapping, you have a mapping to another Choice field that is set to single choice, the Dataflow will fail on every row that contains multiple values. It seems very odd that it behaves this way, but it’s even odder that this quirk isn’t officially documented.
So what’s the fix?
Unfortunately, the only decent solution until Microsoft address the issue, is to have two separate Dataflows – one to update all the single-choice columns and another to update the multiple-select columns. Hopefully Microsoft will amend their Dataflow process to cater better for Choice columns, but in the meantime, I can at least hope they update their documentation soon.
