Power Query: transpose column to one comma seperated cell

[tombamber]

Hi

I have a list of 4 digit numbers in a column in Power Query, for example:

1234

3827

3845

4028

3957


The file is refreshed with a new list each week with a differing amount of numbers.

I want PQ to combine them so they end up in a single cell in the following format.

1234,3827,3845,4028,3957

Any help would be appreciated.

Thanks

 

[ExcelToDAX]

Is your Power Query only referencing the one column? Or are there multiple columns in the Power Query?

 

[Matt Allington]

Can I ask why you want this format. Are you using it to migrate data to another tool, or are you using it in Excel this way?

 

[tombamber]

Is your Power Query only referencing the one column? Or are there multiple columns in the Power Query?

This query is a reference of another query with multiple columns. Those columns have been removed to leave this one column.

 

[tombamber]

Can I ask why you want this format. Are you using it to migrate data to another tool, or are you using it in Excel this way?

The data in comma separated list will be inputted into another system.

 

[alansidman]

One way would be to transpose your column of data and then combine the columns with a comma delimiter.

 

[GraH]

Or using the group by function...

Assuming data looks like something below:

Col1	Col2
A	1234
A	3827
A	3845
B	4028
B	3957
C	5487
C	1248
C	3258
C	3333
A	4847
A	3214
B	2014

This grouping

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Col1"}, {{"Values", each Text.Combine([Col2], ", "), type nullable text}})
in
    #"Grouped Rows"

creates this table