convert a column's data to list

[ianbruckner]

I've seen a lot of examples of changing a list to a csv type format, but how do you go the other way?

With this data:

Header
{"A","B","C ee"}
{"A","D","C ee"}

I'm hoping to end up with this:

Header

list

list

...so I can expand the data to new rows. However, the data is of type "text". I can't, for the life of me, figure out how to switch it over a type list. Isn't that the format of a list above?

[ianbruckner]

That was my first attempt... but this is what it does when you expand one of the list items: 

I'm hoping for
List

A

B

C ee

[alena2k]

My appologies. Please look into Text.Split([Header], ",")

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wqo5RcoxR0olRcgKTzgqpqTFKtUqxOkhSLqhSsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Header = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Header", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","{","",Replacer.ReplaceText,{"Header"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","}","",Replacer.ReplaceText,{"Header"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","""","",Replacer.ReplaceText,{"Header"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value2", "Custom", each Text.Split([Header], ",")),
    Custom = #"Added Custom"{0}[Custom]
in
    Custom

[ianbruckner]

Yes - that does function and was what I was hoping was just a workaround :). I was hoping there was a simple way to say "treat the text that's formatted as a list... as a list!". Something like

= Table.AddColumn(#"Promoted Headers", "Custom", each Value.As([Header], List.Type))

But that doesn't work: Expression.Error: We cannot convert the value "{"A", "B", "C ee"}" to type List.
Details:
Value={"A", "B", "C ee"}
Type=[Type]

[ianbruckner · Accepted solution]

Found it! A bit more complicated than I hoped for, but it works:

= Table.AddColumn(#"Changed Type", "Custom", each Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)([Header]))

Now if only I could remember how to do it "in column" where it replaces the value in the existing column instead of adding a new one, that'd save another step or two.

[Nruzicka]

Hello, maybe a late reply but to transform your existing column to a list type without adding an additional column, try this method:

 = Table.TransformColumns("tableName", {"ColumnName", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)})

 The "transformOperation" argument in "TransformColumns" can have multiple operations. So if you want to convert multiple columns to a list type:

 = Table.TransformColumns("tableName", {
    {"column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)},
    {"column2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)}
    })

See Table.TransformColumns 

[alena2k]

@ianbruckner sorry if I misunderstood, but doesn't Text.ToList() do just that?

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