Transforming a column of Tables into a column of listed items separated by commas

[PualT]

Hi all,

I dont need to use Power BI too often and so my skills are pretty basic. I think what I'm trying to do is easy but I'm still stuck after a couple of hours of trawling forums and other help guides so hopefully someone can help.

Basically I want to create a column from the Labels column shown below, where the records in a rows table are listed, separated by commas. So in my example below, in row 9, I would want my new column to display B2B_Junifer, DA268, MEL. Some rows are null in which case they can stay as null and some have a single entry (which could be made up of more than 1 string) in which case I would want to just return that row as it is.

Is this possible - just being able to display the table as a list separated by commas would be an amazing help.

Thanks,

Paul

[AlexisOlson · Accepted solution]

You can add a transformation step like this:

= Table.TransformColumns(
    #"Name of previous step goes here",
    {{
      "Labels",
      each try Text.Combine(
                   Table.Column(_, Table.ColumnNames(_){0}),
                   ", "
               )
           otherwise null,
      type text
    }}
)

[CNENFRNL]

Text.Combine(Table.ToColumns(_){0},",")

[PualT]

Woooaaahhhh!! That seems to have worked, now I'll spend time deciphering this wizardry. Thankyou @AlexisOlson !!

FYI, here is how this column looks now with row 9 exactly as I need it:

Absolutely brilliant mate, I can't believe it. I would never have got to this,

Paul

[AlexisOlson]

In the code, "_" is a placeholder corresponding to "each" for the table we're transforming. What we want is to convert each of these tables into a list of values and then combine them. I used

Table.Column(_, Table.ColumnNames(_){0})

 to do the conversion but as @CNENFRNL points out, this can be simplified using Table.ToColumns but I think Table.ToList is even a tiny bit cleaner in this case.

each try Text.Combine(Table.ToList(_), ", ") otherwise null

Text.Combine transforms the list into a text string separating each item with the specified delimiter. Finally, I use the try ... otherwise ... construction to handle the nulls that can't be converted to a list.