Forum Discussion

mb0307's avatar
mb0307
Responsive Resident
4 years ago
Solved

Concatenate single column

Hi all,

 

I want to concatenate all values in a column with " prefix and suffix and ,

 

Example:

Column1

3000

2000

7777

 

I want result to be:  "3000", "2000", "7777"

 

I will be using this in Power Query as Input like this:

 

{Cube.ApplyParameter, "[!V99]", {{ Text.Combine ( CODE ) }}}

 

Thank you!

Need Help
  • mb0307's avatar
    mb0307
    4 years ago

    Found solution:

     

     

         {Cube.ApplyParameter, "[!V99]", {"""" & Text.Combine(TableName[ColName],""", """) & """" }}

     

8 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    If you have a list of integers in Table1[Column1], then

    = Text.Combine(List.Transform(Table1[Column1], each """" & Number.ToText(_) & """"), ",")

     

    If it's already text, then you just need _ instead of Number.ToText(_).

    • mb0307's avatar
      mb0307
      Responsive Resident
      4 years ago

      AlexisOlson  Thanks

       

      My column is Text already so used _ instead.

       

      But I get this error:

       

      Code:

      {Cube.ApplyParameter, "[!V99]", {{ Text.Combine(List.Transform( ForBW[Last Friday of Month], each """" & _ & """"), ",") }}},

       

      Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    mb0307 
    Try the following code.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorViVYyNjAwADPAhBEKzxwIlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> "")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each """"&[Column1]&""""),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2", "Column3"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

     


    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.

     

    • mb0307's avatar
      mb0307
      Responsive Resident
      4 years ago

      Anonymous Thanks but if you see my post then I do not want to create a merged column.  

       

      I am looking to insert concatenate in the code here

      {Cube.ApplyParameter, "[!V99]", {{ Text.Combine(CODE TO COMBINE ALL ROWS WITH ""}}},

       

      so the text passed to source system looks like this (WITH ""s):

      "3000", "2000", "7777"

       

      Thanks

  • mb0307's avatar
    mb0307
    Responsive Resident
    4 years ago

    Found solution:

     

     

         {Cube.ApplyParameter, "[!V99]", {"""" & Text.Combine(TableName[ColName],""", """) & """" }}