Forum Discussion
Sep 13, 2016
Displaying SharePoint 2010 multi-value choice columns in one query column
In my work environment SharePoint 2010 is still very much alive and I run several Power Query scenarios with SharePoint 2010 data. Yesterday, I worked on a query that includes a multi-value choice column. In the Query Editor, the column shows as "Table" and when that column is expanded, will duplicate each row for each value in the multi-value SharePoint column.
That's not what I wanted. I'd like to see the values from the choices in one row, separated with a comma.
Searching the web brought me to forum threads that discuss just that, but they were talking about "List" where I see "Table". I guess that is a difference between SharePoint 2010 and later versions, where apparently SharePoint choice columns are returned as "Lists" to Power Query. Anyway, the suggestion to use ...
Text.Combine([Choices], ", ")
... did not work for the Table column. It threw an error instead. "Expression.Error: We cannot convert a value of type Table to type List."
In Power Query, lists are one-column objects, whereas tables are multi-column objects, and even if a table contains only one column, it must be explicitly referenced. It took me a little more digging in the search engines, but then I found the solution that works with SharePoint 2010 multi-value choice columns, which show up as tables in Power Query.
In the first screenshot we can see the selected table in the lower part of the Power Query window. The column header for the data I want to extract is "Value". So all I needed was to reference "Value" column in the formula.
The correct syntax for that is
Text.Combine([Choices][Value], ", ")
And, hey, presto! It worked as I expected.
Hope that helps someone.
- Have a look at the FieldValuesAsText column. I blather on about this approach here: http://whitepages.unlimitedviz.com/2016/07/simplified-method-working-sharepoint-data-power-bi/
Hi John,
I saw that blog post, but I could not apply it. I don't see that column, neither in Power BI nor in Power Query. I'm accessing SharePoint 2010 on prem, maybe that is why it's different to SP online.