Forum Discussion
A more robust Table.SplitColumn / Splitter.SplitTextByDelimiter
a probably more efficient way would be this:
nrows=Table.RowCount(yourTab), split=Table.FromColumns(List.Zip(List.Transform({0..nrows-1},each Text.Split(yourTab[country]{_},",")))) in splitPS
If Table.TransformRows had, as Table.TransformColumns has, the ability to manage lists of different lengths, there would have been no need to use the List.zip function to do an intermediate step.
aahhh ... 😀 ok, now I see.
you have a table like this (ONLY ONE COUNTRY LIST PER PartNumber. Is it true?)):
and want a table like this (only first rows are displayed):
all you need is in this line (no adda column, no unpivot, no delete lines,...) :
Table.ExpandListColumn(Table.Group(yourTab, {"PartNumber"}, {{"split", each Text.Split(_[country]{0},",")}}),"split")
PS
or more directly e I tink clearer
Table.ExpandListColumn(Table.TransformColumns(yourTab, {"country", each Text.Split(_,",")}),"country")
if you are sure the length/composition of string is constant between different rows, you can manage the future different input in in this way:
cols=Text.Split(yourTab[country]{0},","),
colSpl = Table.SplitColumn(yourTab, "country", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), cols)
in
colSpl
If the length/strcture of string could varies between the different lines, you need some more line of code to manage the situation.