Forum Discussion
How to split column with list in columns?
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"
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"