Forum Discussion

cgkas's avatar
cgkas
Helper V
3 years ago
Solved

How to split column with list in columns?

Hi, I have a column with text values, null values and others are lists of variable size for which I'm trying to split into columns.   This is the sample input   This is the sample I have. ...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    3 years ago

    Simple enough

     

     

    let
    Source = Table.FromRecords(
        {
            [Col1 = 1, Data = "abc de"],
            [Col1 = 3, Data = {1,2,3}],
            [Col1 = 5, Data = "mhwu 5"],
            [Col1 = 2, Data = {"o","p","q","r","s"}]
        }
    ),
    #"Added Column" = Table.AddColumn(Source, "tb", each Table.FromRows(if Value.Is([Data], type text) then {{[Data]}} else {[Data]})),
    #"Expanded Data" = let cnt = List.Max(List.Transform(#"Added Column"[tb], Table.ColumnCount)) in Table.ExpandTableColumn(#"Added Column", "tb", List.Transform({1..cnt}, each "Column" & Text.From(_)))
in
    #"Expanded Data"

     

     

CNENFRNL's avatar
CNENFRNL
Community Champion
3 years ago

Simple enough

 

 

let
    Source = Table.FromRecords(
        {
            [Col1 = 1, Data = "abc de"],
            [Col1 = 3, Data = {1,2,3}],
            [Col1 = 5, Data = "mhwu 5"],
            [Col1 = 2, Data = {"o","p","q","r","s"}]
        }
    ),
    #"Added Column" = Table.AddColumn(Source, "tb", each Table.FromRows(if Value.Is([Data], type text) then {{[Data]}} else {[Data]})),
    #"Expanded Data" = let cnt = List.Max(List.Transform(#"Added Column"[tb], Table.ColumnCount)) in Table.ExpandTableColumn(#"Added Column", "tb", List.Transform({1..cnt}, each "Column" & Text.From(_)))
in
    #"Expanded Data"

 

 

  • cgkas's avatar
    cgkas
    Helper V
    3 years ago

    Hi CNENFRNL. Thanks for the help. It works, but I'm kind of lost with your solution.

     

    What does mean {{[Data]}} and {[Data]}?

     

    Additionally, I see you used a block "let - in" and defined a variable inside the step #"Expanded Data". That is new for me. What are the rules to use or when to use another/nested "let - in" ? Thanks