Forum Discussion
Handling multiple choice sharepoint columns which are not mandatory
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.
Community ChampionIt 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.
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!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
