Split cell by delimiter into specific columns

[Danny54]

Is it possible to use Power Query to split comma delimited data in a excel worksheet into specific new columns?

the actual input sheet has 120,000 rows
the actual input sheet has 2 cols
the comma delimited data in column b can be split into 160 new cols

Input sheet

output sheet (notice all the a's are lined up in one column, all the d's in one column ect..

Thanks

 

[GraH]

Yes, split into rows. Compare the split col to a list of possible values. Get the position of the value in the list. Pivot on this position, use no aggregation.

 

[Danny54]

Thanks for the reply.
I have successfully loaded and split the values by the comma delimiter, however I'm not certain how to align each column by its value (see last insert)

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Color", type text}, {"Values", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Values", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Values.1", "Values.2", "Values.3", "Values.4"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Values.1", type text}, {"Values.2", type text}, {"Values.3", type text}, {"Values.4", type text}})
in
#"Changed Type2"

so, how would i go about shifting and aligning cell data to create the following using mcode (if its possible)

Thanks

 

[JGordon11]

try

let
    Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
    tbl1 = Table.AddColumn(Source, "Custom", each Text.Split([Values],",")),
    tblRemoveColumns = Table.RemoveColumns(tbl1,{"Values"}),
    tblExpandCustom = Table.ExpandListColumn(tblRemoveColumns, "Custom"),
    tblDuplicateColumn = Table.DuplicateColumn(tblExpandCustom, "Custom", "Custom - Copy"),
    tblPivotColumn = Table.Buffer(Table.Pivot(tblDuplicateColumn, List.Distinct(tblDuplicateColumn[Custom]), "Custom", "Custom - Copy")),
    lstHeaders = List.Buffer(List.LastN(Table.ColumnNames(tblPivotColumn),Table.ColumnCount(tblPivotColumn)-1)),
    tbl2 = Table.AddIndexColumn(Source,"Index",0,1,Int64.Type),
    tblMergeQueries = Table.NestedJoin(tbl2, {"Color"}, tblPivotColumn, {"Color"}, "tbl2", JoinKind.LeftOuter),
    tblExpandedtbl2 = Table.ExpandTableColumn(tblMergeQueries, "tbl2", lstHeaders, lstHeaders),
    tblSortRows = Table.Sort(tblExpandedtbl2,{{"Index", Order.Ascending}}),
    tblUnsorted = Table.RemoveColumns(tblSortRows,{"Index", "Values"}),
    lstSortOrder = {"Color"} & List.Sort(List.LastN(Table.ColumnNames(tblUnsorted),Table.ColumnCount(tblUnsorted)-1)),
    tblSorted = Table.ReorderColumns(tblUnsorted,lstSortOrder)
in
    tblSorted

 

[RoryA]

You could also split into rows, duplicate the column, then pivot:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Color", type text}, {"Values", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Values", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Values"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Values", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Values", "Values - Copy"),
    #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[Values]), "Values", "Values - Copy")
in
    #"Pivoted Column"

 

[GraH]

My original idea was as such

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ValuesToList = Table.AddColumn(Source, "List", each List.Select(Text.Split([Values],","),each _<>"")),
    SortedHeaders = List.Buffer(List.Sort(List.Distinct(List.Combine(ValuesToList[List])))),
    ExpandList = Table.ExpandListColumn(ValuesToList, "List"),
    Positions = Table.AddColumn(ExpandList, "Positions", each List.PositionOf(SortedHeaders,[List])),
    PivotByPosition = Table.Pivot(Table.TransformColumnTypes(Positions, {{"Positions", type text}}, "en-BE"), List.Sort(List.Distinct(Table.TransformColumnTypes(Positions, {{"Positions", type text}}, "en-BE")[Positions])), "Positions", "List")
in
    PivotByPosition

Then like @RoryA, I thought about duplicating the column. A cup of coffee in the morning can do wonders sometimes.
Just a small tweak, I would also sort the headers.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ValuesToList = Table.AddColumn(Source, "List", each List.Select(Text.Split([Values],","),each _<>"")),
    ExpandList = Table.ExpandListColumn(ValuesToList, "List"),
    DuplicateValue = Table.DuplicateColumn(ExpandList, "List", "Value"),
    SortedPivotHeaders = Table.Pivot(DuplicateValue, List.Sort(List.Distinct(DuplicateValue[List])), "List", "Value")
in
    SortedPivotHeaders

 

[shaowu459]

for your reference:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    res = let 
              a=List.Distinct(Text.Split(Text.Combine(Source[Values],","),","))
          in
              Table.SplitColumn(
                  Table.TransformColumns(
                                          Source,
                                          {"Values", each List.Transform(a,(x)=>if Text.Contains(_,x) then x else null)}
                                        ),
                                "Values",
                                each _
                               )
in
    res

拆分列.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Color	Values		Color	Values.1	Values.2	Values.3	Values.4	Values.5	Values.6	Values.7
2	Red	a,d,j,z		Red	a	d	j	z
3	Orange	a,bb,c,d		Orange	a	d			bb	c
4	Green	bb		Green					bb
5	Brown	d,f		Brown		d					f
6
7
8
Sheet1

 

[shaowu459]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    a = Table.AddColumn(
            Table.ExpandListColumn(
                Table.TransformColumns(
                                        Source,
                                        {"Values",each Text.Split(_,",")}
                                      ),
                                  "Values"
                                  ),
                       "n",
                       each [Values]
                      ),
    b = Table.Sort(Table.Pivot(a, List.Distinct(a[n]), "n", "Values"),{"Color",1})
in
    b

拆分列.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Color	Values		Color	Values.1	Values.2	Values.3	Values.4	Values.5	Values.6	Values.7
2	Red	a,d,j,z		Red	a	d	j	z
3	Orange	a,bb,c,d		Orange	a	d			bb	c
4	Green	bb		Green					bb
5	Brown	d,f		Brown		d					f
6
7				Color	a	d	j	z	bb	c	f
8				Red	a	d	j	z
9				Orange	a	d			bb	c
10				Green					bb
11				Brown		d					f
Sheet1

 

[GraH]

Impressive!!! @shaowu459.

 

[JGordon11]

How would you change these (posts 5, 6, 8) to ensure the output color column remains in the same order as the Source?