Handling multiple choice sharepoint columns which are not mandatory

[Lee123]

Hi All

I'm trying to get some multiple choice sharepoint columns into powerbi and I can't see what the best way to do that is. They turn up in powerquery showing as 'list' but the extract values button does not appear on the column unless I filter out the blanks which I do not want to do as it filters out many otherwise good rows.

What shall I do? I'm using the v2 sharepoint connector.

[edhans · Accepted solution]

It depends on what you want to do with it. For example, I've mocked this up in Excel (ignore that I do have an expand button)

You could extract the choices and put in comma delimited list, like so in a Custom Column - use this formula:

if Value.Is([List], type list) then Text.Combine([List], ", ")

That looks at my List column, and if there is a list in there, combine the values with a comma delimiter.

Now you can separate those out with the Transform, Split Column feature.

[santiago-a-n]

Hi @edhans ! Thank you for your solution, where in PowerBI should this code be added ? Is the only way to create a new column ? Could it be possible to only transform the column with the list contents ?

[edhans]

I don't know what you mean by transforming with the list contents. What are you trying to do @santiago-a-n 

[edhans]

@Lee123 - did this help?

[Lee123]

Thanks- yes right idea, I did need to add else "NotRecorded" to handle the blanks.

[edhans]

Great! Glad I was able to help. Can you mark my response as the solution so others know that it works and this thread can be shown to be solved?

Thanks!

[BrotherCole94]

I am having the same issue in power query my data source comes from a sharepoint list and one of the columns is a multi select choice column, which allows the user to select 1 or more optiosn from the list in the column choices.
When trying to being this data into power query I get a mashup error that it cannot convert values of type list to type text.
Unlike the single select column I am not able to extract values to a table and table to a record.

Can you help?
.Error: Expression.Error: We cannot convert a value of type List to type Text. Details: Reason = Expression.Error;Value