Testies

Split multiple columns at the same time

Email	0	1	2	3	4		Email	0	1	2	3	4	5	6	7	8	9
xxx@abc.com		sth1|something		sth2|something			xxx@abc.com			sth1	something			sth2	something
zzz@abc.com	sth1|something		sth2|something		sth3|something		zzz@abc.com	sth1	something			sth2	something			sth3	something

Code:

---

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UOC = Table.UnpivotOtherColumns(Source, {"Email"}, "Attribute", "Value"),
    SortAsc = Table.Sort(UOC,{{"Attribute", Order.Ascending}}),
    Split2Rows = Table.ExpandListColumn(Table.TransformColumns(SortAsc, {{"Value", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"),
    Index0 = Table.AddIndexColumn(Split2Rows, "Index", 0, 1),
    RC = Table.RemoveColumns(Index0,{"Attribute"}),
    Pivot = Table.Pivot(Table.TransformColumnTypes(RC, {{"Index", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(RC, {{"Index", type text}}, "en-GB")[Index]), "Index", "Value")
in
    Pivot

---