Is there another way that has less steps

[mduntley]

Wondering if there is a way to do what i want to do with fewer steps

Starting Table

Book2
	A	B
1	Column 1	Product±Color
2	Store 1	Avocado±Red Banana±Pink
3	Store 2	Bilberry±Orange Blackberry±Yellow Blackcurrant±Purple
Sheet3

Table I want

Book2
	A	B	C
1	Column 1	Product	Color
2	Store 1	Avocado Banana	Red Pink
3	Store 2	Bilberry Blackberry Blackcurrant	Orange Yellow Purple
Sheet2

My M code

let
    Source = #table({"Column 1", "Product±Color"}, {{"Store 1","Avocado±Red
Banana±Pink"},{"Store 2","Bilberry±Orange
Blackberry±Yellow
Blackcurrant±Purple"}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Product±Color", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Product±Color"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Product±Color", Splitter.SplitTextByDelimiter("±", QuoteStyle.Csv), {"x.1", "x.2"}),
    #"Grouped Rows" = Table.Group(#"Split Column by Delimiter1", {"Column 1"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Product", each Text.Combine([Count][x.1],"#(lf)")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Color", each Text.Combine([Count][x.2],"#(lf)")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Count"})
in
    #"Removed Columns"

 

[Kerryx]

tried using Text to columns before importing table to PQ but not much shorter

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1", type text}, {"Product±Color", type text}, {"Column1", type text}, {"Column2", type text}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Product±Color", "Column1", "Column2"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Only Selected Columns",{"Attribute"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Value", Splitter.SplitTextByDelimiter("±", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type text}})
in
    #"Changed Type1"

Book1
	A	B	C	D
1	Column 1	Product±Color	Column1	Column2
2	Store 1	Avocado±Red	Banana±Pink
3	Store 2	Bilberry±Orange	Blackberry±Yellow	Blackcurrant±Purple
4
5
6	Column 1	Value.1	Value.2
7	Store 1	Avocado	Red
8	Store 1	Banana	Pink
9	Store 2	Bilberry	Orange
10	Store 2	Blackberry	Yellow
11	Store 2	Blackcurrant	Purple
Sheet1

 

[JGordon11]

let
    Source = #table({"Column 1", "Product±Color"}, {{"Store 1","Avocado±Red
Banana±Pink"},{"Store 2","Bilberry±Orange
Blackberry±Yellow
Blackcurrant±Purple"}}),
    tbl = Table.RenameColumns(Source,{{"Product±Color", "Product"}}),
    tbl1 = Table.TransformColumns(tbl, {"Product", each List.Combine(List.Transform(Text.Split(_, "#(lf)"), (x)=>Text.Split(x, "±")))}),
    tbl2 = Table.TransformColumns(Table.DuplicateColumn(tbl1, "Product", "Color"),
            {{"Product", each Text.Combine(List.Alternate(_,1,1,1),"#(lf)")}, {"Color", each Text.Combine(List.Alternate(_,1,1),"#(lf)")}})
in
    tbl2