none
Power Query and multi-selection columns RRS feed

  • Question

  • I have a power query pulling data from a sharepoint list some of the list columns allow multiple selections.

    The power query seems to only be pulling in one value instead of all the selected ones in the columns.

    Thanks,

    Eric

    • Moved by John P. WhiteMVP Thursday, May 1, 2014 5:23 PM Moving as this is a Power Query specific question
    Wednesday, April 23, 2014 6:31 PM

Answers

  • Hi Eric,


    Just to add a bit more detail to what is going.

    In Power Query, SharePoint List columns with multiple selection choices are represented as nested tables with a single column called "Value". Each row of these nested tables will represent a selected choice for the list item. You can examine this by clicking on "Table" under the "Options" column on one of the rows.


    By clicking on the "Expand" icon of a column, you are making use of the Table.ExpandTableColumn function. That function will take each row in the table and do a cross product with the rows of the nested tables of the selected column. This means that each row will be duplicated as many times as there rows in the nested columns.

    In this specific case, each item in the SharePoint list will become duplicated as many times as there are selected choices for the item. This can be very useful in some cases, but I think that in your case this is not the operation that you're looking for.

    If you want to simply create a column that combines all the selected choices as a single text value, there's a simpler alternative to Alejandro's solution:

    = Table.AddColumn(RemovedColumns, "Options 2", each Text.Combine([Options][Value], ", "))

    You can simply paste the above as a blank step by clicking on the "Fx" icon next to the formula bar. Here we're creating a new column called "Options 2" that combines the text values in the "Value" column of the nested table of the Options column and separate each entry by ", ".

    -Tristan

    Monday, May 5, 2014 2:34 AM
    Moderator

All replies

  • I figured i should add some more information.

    I have created a test list in sharepoint with the default title column and then one more called options.  The options column is of type "dropdown" and the options are A,B,C,D and "checkboxes" (allow multiple selections is turned on.

    Here is what the data looks like in sharepoint.

    You can see i have selected multiple options in 2 of the 3 entries.


    In order to see the data in the options field i have to Expand that column out. My only options is value so that what i pick.

    Here is the power query that i end up with.

    let
        Source = SharePoint.Tables("sharepointsite"),
        PQtesting = Source{[Name="PQtesting"]}[Content],
        RemovedColumns = Table.RemoveColumns(PQtesting,{"ContentTypeID", "ContentType", "Modified", "Created", "CreatedById", "ModifiedById", "Owshiddenversion", "Version", "Path", "CreatedBy", "ModifiedBy", "Attachments"}),
        #"Expand Options" = Table.ExpandTableColumn(RemovedColumns, "Options", {"Value"}, {"Options.Value"})
    in
        #"Expand Options"
    And this is what it outputs.

    You can see instead of doing project 1    a,c . It has split the returned data into multiple rows.

    How do i get my power query to return the data the same way its stored in sharepoint?

    Thanks,
    Eric


    • Edited by Eric Alcott Thursday, April 24, 2014 4:19 PM
    Thursday, April 24, 2014 4:18 PM
  • This might be a better question for the Power Query forum. Thoughts on that?

    Thanks!


    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Wednesday, April 30, 2014 8:48 PM
    Owner
  • This won't be too efficient, but you can group by the title and add the strings in Options.Value together.

    = Table.Group(Source, {"Title"}, {{"Options.Value", each List.Accumulate([Options.Value], "", (state, current) => state & current & ","), type text}})

    This will leave an extra comma at the end, but we can get rid of that by using Text.Range to leave off the last character.

    = Table.TransformColumns(GroupedRows,{{"Options.Value", (x) => Text.Range(x, 0, Text.Length(x) - 1)}})

    Does that fix your problem?

    Thanks.

    -Alejandro

    Thursday, May 1, 2014 9:03 PM
  • Thanks for your reply!

    I am still working this as I have not had time to fully build it out and see what works best.

    Right now I have the multiselection columns pulled out into a separate power query and then linked by ID in the data model and that is giving my the sorting I want but not the visual.

    I will check out your formula and see if that will fix the visual part.


    Thanks,
    Eric

    Friday, May 2, 2014 3:37 PM
  • Hi Eric,


    Just to add a bit more detail to what is going.

    In Power Query, SharePoint List columns with multiple selection choices are represented as nested tables with a single column called "Value". Each row of these nested tables will represent a selected choice for the list item. You can examine this by clicking on "Table" under the "Options" column on one of the rows.


    By clicking on the "Expand" icon of a column, you are making use of the Table.ExpandTableColumn function. That function will take each row in the table and do a cross product with the rows of the nested tables of the selected column. This means that each row will be duplicated as many times as there rows in the nested columns.

    In this specific case, each item in the SharePoint list will become duplicated as many times as there are selected choices for the item. This can be very useful in some cases, but I think that in your case this is not the operation that you're looking for.

    If you want to simply create a column that combines all the selected choices as a single text value, there's a simpler alternative to Alejandro's solution:

    = Table.AddColumn(RemovedColumns, "Options 2", each Text.Combine([Options][Value], ", "))

    You can simply paste the above as a blank step by clicking on the "Fx" icon next to the formula bar. Here we're creating a new column called "Options 2" that combines the text values in the "Value" column of the nested table of the Options column and separate each entry by ", ".

    -Tristan

    Monday, May 5, 2014 2:34 AM
    Moderator
  • Tristan

    That works wonderfully!

    So now I have it displayed as I want but to get the values of the options field to still show up as individual sorting options I'm creating an additional power query to just pull the ID and Options.value expanded then adding that Query and my original query to the power pivot data model and building my reports that way.

    Is there a better way to get the options.value to display as A,B,C but still keep them as separate options in the filtering?

    Thanks,
    Eric

    EDIT

    Well it worked wonderfully for a single addcolumn but if I try to add a stage for another one it only adds the column farthest down on the steps list.  Is there a way to add more than one column? I need a total of 4.

    • Edited by Eric Alcott Tuesday, May 6, 2014 8:06 PM found new limitation
    Tuesday, May 6, 2014 3:41 PM