Power Query: How merge row results and separate by comma

[Anonymous]

Hi,

I have a table I'm bringing in to Power BI.  I would like to merge several rows together and have the results separated by a comma.  The issue is, sometimes there are blanks for some of the fields and I do not want commas included if there are blanks in these fields.

Here is an example of my data/table in Power BI:

Name         Engine       Cylinders       Turbo       Boost       Exhaust

Andy          400            8                    Turbo       5psi         Duel

Bob            427                                                  10psi       Duel

Dan            255           6                                                    Single

I would like to merge the results together in fields Cylinder, Turbo, Boost and Exhaust so the newly created column would have the following results (highlighted in Red):

Name         Engine       Cylinders       Turbo       Boost       Exhaust       New Column      

Andy          400            8                    Turbo       5psi         Duel             8, Turbo, 5psi, Duel

Bob            427                                                  10psi       Duel             10psi, Duel

Dan            255           6                                                    Single           6, Single

I know Power Query has the 'Group By' trandformation but I don't believe that is what I need in this situation.  I'm not sure what the custom column would need to be in order to accomplish this merge tat I'm trying to do.

[MFelix · Accepted solution]

Hi @Anonymous ,

Add a new column with the following code:

[Cylinders] & (if [Cylinders] = ""  then "" else "," )
& [Turbo]
 &

(if [Turbo] = "" then "" else "," ) & [Boost]
& 
(if [Boost] = "" then "" else "," ) & [Exhaust]

[Greg_Deckler]

@Anonymous You could use the "&" concatenation operator like this:

[Cylinders] & "," & [Turbo] & "," & [Boost] & "," & [Exhaust]