How to split column with list in columns?

[cgkas]

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.

let
    Source = Table.FromList(
    {
        [Col1 = 1, Data = "abc de"],
        [Col1 = 3, Data = {1,2,3}],
        [Col1 = 5, Data = "mhwu 5"],
        [Col1 = 2, Data = {"o","p","q","r","s"}]
    },
    Record.FieldValues,
    {"Col1","Data"}
),

#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

This is the output I'm trying to get, but in step #"Converted to Table", I'm getting this error

"We cannot convert a value of type Table to type List"

Thanks for any help

 

[CNENFRNL · Accepted solution]

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]

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

[adudani]

Hi cgkas,

Steps taken:

1. Duplicated the initial query.

2. Have filtered out the List and the other values in one query.

3. In the other query, extracted the columns with any delimiter.

4. Split it into columns by the delimiter in step 3.

5. Appended the filtered query (Query1) on this Query 2.

6. Sorted it using a custom sorting order as per the image.

CODE:

1) (Query 1) Filtered Query
let
Source = Table.FromList(
{
[Col1 = 1, Data = "abc de"],
[Col1 = 3, Data = {1,2,3}],
[Col1 = 5, Data = "mhwu 5"],
[Col1 = 2, Data = {"o","p","q","r","s"}]
},
Record.FieldValues,
{"Col1","Data"}
),
#"Filtered Rows" = Table.SelectRows(Source, each ([Col1] = 1 or [Col1] = 5))
in
#"Filtered Rows"

2 ) Query 2 (Appended Query)
let
Source = Table.FromList(
{
[Col1 = 1, Data = "abc de"],
[Col1 = 3, Data = {1,2,3}],
[Col1 = 5, Data = "mhwu 5"],
[Col1 = 2, Data = {"o","p","q","r","s"}]
},
Record.FieldValues,
{"Col1","Data"}
),
#"Filtered Rows" = Table.SelectRows(Source, each ([Col1] = 2 or [Col1] = 3)),
#"Extracted Values" = Table.TransformColumns(#"Filtered Rows", {"Data", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Data", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Data.1", "Data.2", "Data.3", "Data.4", "Data.5"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data.1", type text}, {"Data.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Data.1", "Data"}}),
#"Appended Query" = Table.Combine({#"Renamed Columns", Query1}),
#"Added Conditional Column" = Table.AddColumn(#"Appended Query", "colsort", each if [Col1] = 3 then 2 else if [Col1] = 2 then 4 else if [Col1] = 5 then 3 else [Col1], type any),
#"Sorted Rows" = Table.Sort(#"Added Conditional Column",{{"colsort", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"colsort"})
in
#"Removed Columns"