Forum Discussion
Handling multiple choice sharepoint columns which are not mandatory
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.
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.
7 Replies
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.
Thanks- yes right idea, I did need to add else "NotRecorded" to handle the blanks.
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 ?
I don't know what you mean by transforming with the list contents. What are you trying to do Anonymous
