Forum Discussion

KENNEU's avatar
KENNEU
Regular Visitor
2 years ago
Solved

Column of many values to a single comma separated string

I'm trying to collapse a single column of data into a comma separated string in power querty.  I can do it with group by if first get a count (they are all ones as they are all unique values) but it adds extra steps that slow things down.  I'm assuming I can get there from here with a single custom column.  Essentially:

From:

System Name

ABC123

12345

2345XYZ

 

To:

ABC123 , 12345 , 2345XYZ

 

I'm doing a bunch of work to get the list down to something that is pasted into another system that "processes" the comma-delimited list.  It is generally 600-1000 entries.

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi KENNEU, add this as new step (replace Source in my code with your previous step reference if needed):

     

    = Text.Combine(Source[System Name], " , ")

     

    Whole code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjQyVorViVYC0iamYBaIEREZpRQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"System Name" = _t]),
    Cobimed = Text.Combine(Source[System Name], " , ")
in
    Cobimed

     

2 Replies

  • jgeddes's avatar
    jgeddes
    Icon for Super User rankSuper User
    2 years ago

    You can try the following code to see if it solves your concern...

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjQyVorViVYC0iamYBaIEREZpRQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"System Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"System Name", type text}}),
    Custom1 = Table.FromList(Table.ToList(Table.Transpose(#"Changed Type"), Combiner.CombineTextByDelimiter(",")), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"



  • dufoq3's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi KENNEU, add this as new step (replace Source in my code with your previous step reference if needed):

     

    = Text.Combine(Source[System Name], " , ")

     

    Whole code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjQyVorViVYC0iamYBaIEREZpRQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"System Name" = _t]),
    Cobimed = Text.Combine(Source[System Name], " , ")
in
    Cobimed