CODEWITHSUNDEEP
excelpowerquery
Victor
Victor

Reputation: 1205

Convert column to cell string Power Query

I need to fit all the values of a column in Power Query into a 1-cell string separated by commas, as the example below:

enter image description here

To do this, I have the following piece of code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2", "Column3"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"KeepString" = #"Merged Columns"[Merged]{0}
in
    #"KeepString"

The problem with this code is that it assumes there will always be 3 columns, which is not always the case. How can I merge all columns (regardless of how many there are) into one?

Upvotes: 1

Views: 5207

Answers (2)

Gil Raviv
Gil Raviv

Reputation: 31

You can also use a shorter code, like this:

let
    Source=Excel.CurrentWorkbook( {[Name="Table1"]}[Content],
    Result = Text.Combine(Source[User], ",")
in
    Result

Upvotes: 3

Alejandro Lopez-Lago - MSFT
Alejandro Lopez-Lago - MSFT

Reputation: 4134

You can do this with List.Accumulate:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    KeepString = List.Accumulate(Source[User], "", (state, current) => if state = "" then current else state & "," & current)
in
    KeepString

You can also use Table.ColumnNames to get the list of all the column names. You can pass this into Table.CombineColumns, so your modified solution would be:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table", Table.ColumnNames(#"Transposed Table"),Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"KeepString" = #"Merged Columns"[Merged]{0}
in
    #"KeepString"

Upvotes: 6

Related Questions