How to Migrate Multi-Select SharePoint Columns to Dataverse

In my Dataverse migration series, I outlined how to import Choice columns from SharePoint to Dataverse via a Dataflow. One caveat to Choice columns is the option to make them multi-select. Check out the full series here.

To begin, I'll pick up where I was in Part Five with the regular Choice column. We will need the same Choice mapping table described in that article.

To begin, let's look at a multi-select column as it appears in the Dataflow.

as you can see, the value appears as a [List] whether there are multiple selected values or just a single selection. We need to first transform those values into their related numeric Dataverse choice ID (this is detailed in Part Five).

Step one is to isolate the record into just the ID and the multi-select choice column. Do this by Duplicating the query and deleting all columns except the ID and the multi-select choice column.

Hold the CTRL key and select the ID and Choice column then select the Remove other columns option to isolate just those two. If you have multiple multi-select choice columns, then you'll need to create a new query for each column.

Now, expand the choice column by clicking the Expand icon on the choice column.

This should give you a list of all the records with the ID and a corresponding choice for each selection

In my example above, Record 1 had three selections while Record 2 only had one.

Now we need to map these choices to their numeric Dataverse choice IDs. This is outlined in Part Six of the series. To briefly recap, you'll need to have a query with the Dataverse choice values and their ID then merge those values to connect each choice to their respective numeric ID. This will leave you with something like this

Next, you may remove the text value column, leaving you with just the record ID and the ChoiceID.

Now we just need to roll these values up so they're comma-separated with no space and they'll be ready to map! To do this, select the ID column and then select Group by in the Home ribbon

Leave the settings at their default and click OK (I know this is weird, but trust me)

This will leave you with a formula like

Table.Group(#"Removed columns 1", {"Id"}, {{"Count", each Table.RowCount(_), Int64.Type}})

Let's change that. First, change the "Count" value to whatever you want to name this custom column. For my example, I'll call it "MultiChoiceIDs".

That should leave you with something like

Table.Group(#"Removed columns 1", {"Id"}, {{"MultiChoiceIDs", each Table.RowCount(_), Int64.Type}})

Now let's focus on this portion

It's the actual formula driving the row counting. We want to change that so it gives us the comma-separated list. So replace

Table.RowCount(_) 

with

Text.Combine([ChoiceID],",")

Where ChoiceID is the name of the column you had from the merge of the IDs.

Finally, replace Int64.Type with type text

You should have something like this:

Table.Group(#"Removed columns 1", {"Id"}, {{"MultiChoiceIDs", each Text.Combine([ChoiceID],","), type text}})

Keep in mind your values may be different for:

• #"Removed columns 1" (Generated by the Group by wizard, references the previous Power Query applied step)

• Id (Default Id column from SharePoint)

• MultiChoiceIDs (Name of the new column set in this formula)

• ChoiceID (The name of the column we are rolling up, set when the numerioc IDs were merged)

Depending on what you named your columns.

So there you have it! Now you can merge this query into your main import query, extract that comma-separated value, and simply map it to your multi-select choice column in the Dataflow import mapping outlines in Part Six. Ensure the Dataverse choice column is configured for multi-select

This missing column type was pointed out by a reader (thank you Cole!) If you find any other column types missing, please contact me via the contact form!