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")
Hi OKgo , is not yet completely clear the startong and the ending point, but let me show you what I suppose and what I think can be done.
You have a column like this (which I called "country")
and you want to get a group of columns like these:
This can be done, by this code (with little adaptation, eventually):
yourTab= Table.TransformColumnTypes(Tabella1_Table,{{"country", type text}}),
nrows=Table.RowCount(yourTab),
colsMax=List.Max(List.Transform({0..nrows-1},each List.Count(Text.PositionOf(yourTab[country]{_},",",Occurrence.All))))+1,
countries=List.Transform({1..colsMax},each "col_"&Text.From(_)),
colSpl = Table.SplitColumn(yourTab, "country", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), countries)
in
colSplThe code can manage a variable number of max countries, and create as many columns as needed.
if you still want something different, give examples of the most general case that you want to be treated by giving the input and desired output.
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
split
PS
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.
mahoneypat Thank you so much for your support. I learnt a lot but a lot of it was above to transplant.
Anonymous The "Text.Split" clue was very clear - I then searched this and found my exact situation!!
https://www.youtube.com/watch?v=9krfJLv8ENk
Thank you all
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")
