Testies

[sandy666]

Split multiple columns at the same time


Email01234Email0123456789

xxx@abc.comsth1|somethingsth2|somethingxxx@abc.com sth1somethingsth2something

zzz@abc.comsth1|somethingsth2|somethingsth3|someth ingzzz@abc.comsth1somethingsth2somethingsth3someth ing


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(Sort Asc, {{"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