Forum Discussion

OKgo's avatar
OKgo
Helper IV
6 years ago
Solved

A more robust Table.SplitColumn / Splitter.SplitTextByDelimiter

I have a dataset with ISO countries stored in one column e.g. "USA, CAN, MEX, GBR, DEU, POL". This will grow as the database grows. Is there a way to future proof this PQ to account for that growth? ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.

     

  • Anonymous's avatar
    Anonymous
    6 years ago

    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")

OKgo's avatar
OKgo
Helper IV
6 years ago

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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")