Forum Discussion

ianbruckner's avatar
ianbruckner
Frequent Visitor
3 years ago
Solved

convert a column's data to list

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?

Need Help
  • ianbruckner's avatar
    ianbruckner
    3 years ago

    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.

6 Replies

  • alena2k's avatar
    alena2k
    Resolver IV
    3 years ago

    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"
  • ianbruckner's avatar
    ianbruckner
    Frequent Visitor
    3 years ago

    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's avatar
      alena2k
      Resolver IV
      3 years ago

      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's avatar
        ianbruckner
        Frequent Visitor
        3 years ago

        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]