Forum Discussion

Igorpole's avatar
Igorpole
Helper I
5 years ago
Solved

Column values to list (by delimiter)

Hi!

 

Let's say I have such a table:

 

RowID Value

Row1 a,b,c,d,a

Row2 a,c,c,c,c,a

Row3 a,c,f,h,j,r

 

It's very easy using UI split a "Value" column by delimiter (comma in this case) and get a bunch of new columns. But is it possible to get a list of the values splitted by delimiter? So the output should be:

 

RowID Value

Row1 List = {a, b, c, d, a}
Row2 List = {a, c, c, c, c, a}

Row3 List = {a, c, f, h, j, r}

 

Thank you!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    try this

     

    let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsovN1TSUUrUSdJJ1knRSVSK1QELGoEFk6EQLmwMFU7TydDJ0ilSio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RowID = _t, Value = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"RowID", type text}, {"Value", type text}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "list_Values", each Text.Split([Value],","))
in
    #"Aggiunta colonna personalizzata"

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    try this

     

    let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsovN1TSUUrUSdJJ1knRSVSK1QELGoEFk6EQLmwMFU7TydDJ0ilSio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RowID = _t, Value = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"RowID", type text}, {"Value", type text}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "list_Values", each Text.Split([Value],","))
in
    #"Aggiunta colonna personalizzata"