Forum Discussion

Metran's avatar
Metran
New Member
2 years ago
Solved

How to convert Table/list into comma separated string

Hi,

I have 2 queries:

1. a [connection only] which is generating a parameter within Power Query from a referrence from within the spreadsheet itself - a list (single column) of numbers as text or integer. let's say this parameter is called "IDlist". 

2. a [Web API call] using the = Json.Document(Web.Contents("https://somewebapi.com/" & IDlist)) function. The intent here is to have the IDlist resolve to a string as "34,35,36,37,38,39,40"

 

I've searched the web for a solution but none have turned up yet.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Metran 

    You can put the following code to advanced editor in power query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagkkzMGkOJi3ApKVSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(#"Changed Type"[ID],","))
in
    #"Added Custom"

    Output

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

3 Replies

  • slorin's avatar
    slorin
    Super User
    2 years ago

    Hi,

     

    = """"& Text.Combine(List.Transform(Source[ID], Text.From), ",") & """"

    Stéphane 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Metran 

    You can put the following code to advanced editor in power query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagkkzMGkOJi3ApKVSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(#"Changed Type"[ID],","))
in
    #"Added Custom"

    Output

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.