none
How to do group_concat with power query? group rows by key and concatenate strings in a column? RRS feed

  • Question

  • Hi all,

    The data I am working with comes from a Sharepoint List.

    It has a multiple-value column, where its values come up inside Power Query  as a [List].

    To be able to read the individual values, I have to expand the column, which multiplies my rows, having each registry repeat, except by the value in the [List]. One row is created for each different value in the [List].

    So I end up with a table where I have a bunch of rows mostly equal. A single one has strings which are different between rows.

    I would like to group the repeated rows (that would be a Table.Group I believe), and have the differing values in that specific column to be concatenated and show up in a new column as a single text value.

    I don't know if a List.Accumulate() would be the way to go or if there would be a better (righter) way.

    I am having a hard time trying to combine both together to make a solution, and I don't know if am even on the right path.

    Thursday, April 7, 2016 6:34 PM

Answers

  • You can do a group by, with "All Rows" operation, then you can add a custom column that takes the AllRows table value on that row, and does a List.Accumulate on that column? For a table, you can get one of its columns as a list by typing "table[columnName]"
    Thursday, April 7, 2016 6:41 PM
    Moderator
  • Actually in that case you don't have to expand the first pull (just in order to group again in the next step).

    Use Text.Combine([ColumnReturningList]).

    List.Accumulate could be a bit slow, as it contains sort of recursive element that you don't need.

    If you need a separator, add it as the last argument (comma separated).


    Imke Feldmann TheBIccountant.com

    Thursday, April 7, 2016 7:34 PM
    Moderator

All replies

  • You can do a group by, with "All Rows" operation, then you can add a custom column that takes the AllRows table value on that row, and does a List.Accumulate on that column? For a table, you can get one of its columns as a list by typing "table[columnName]"
    Thursday, April 7, 2016 6:41 PM
    Moderator
  • Actually in that case you don't have to expand the first pull (just in order to group again in the next step).

    Use Text.Combine([ColumnReturningList]).

    List.Accumulate could be a bit slow, as it contains sort of recursive element that you don't need.

    If you need a separator, add it as the last argument (comma separated).


    Imke Feldmann TheBIccountant.com

    Thursday, April 7, 2016 7:34 PM
    Moderator
  • You should use a different code instead in order to provide for null or non-text-fields within the returned list:

    Text.Combine(List.Transform(Source[ColumnReturningList], each Text.From(_)))


    Imke Feldmann TheBIccountant.com

    Friday, April 8, 2016 7:30 PM
    Moderator