Forum Discussion
convert a column's data to list
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.
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
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 CustomYes - 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]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.
