# Power Query - split text to variable number of columns



## pepe74287 (Apr 25, 2016)

Hello,

I would need to split text by number of characters (in my case 1) into variable number of columns. For instance, text "adg" should be splitted into 3 columns (a  d  g), "adge" into 4 columns (a  d  g  e)and so on. If I use native power Query Split Column by Number of Characters, PQ creates only 3 columns for "adg" and if I change that string to "adge", it still splits into 3 columns. I know I could set Custom Number of columns to split into, but I would like PQ to split the string into dynamic number of columns. Additionaly, I'm also looking for the opposite - merge all columns (no matter how many columns there are) into 1. 

Thanks
Pepe


----------



## anvg (Apr 25, 2016)

Hi
You can do that dynamically, for example

```
let
    source = Table.FromColumns({ {"abc", "abcde"} }, {"text"}),
    added = Table.AddColumn(source,"list", each Table.FromRows({Text.ToList([text])}),type table),
    length = Table.AddColumn(added,"len", each Table.ColumnCount(
[list]),type number),
    maxCols = List.Max(length[len]),
    colNames = Table.ColumnNames(Table.SelectRows(length,each [len]=maxCols)
[list]{0}),
    return = Table.ExpandTableColumn(added,"list",colNames)
in
    return
```
Regards,


----------



## billszysz (Apr 25, 2016)

Or directly from UI

```
let
    source = Table.FromColumns({ {"abc", "abcde"} }, {"text"}),
    AddIndex = Table.AddIndexColumn(source, "Indeks", 0, 1),
    LengthOfText = Table.AddColumn(AddIndex, "Length", each Text.Length([text]), type number),
    SortByLengthDescending = Table.Sort(LengthOfText,{{"Length", Order.Descending}}),
    SplitColumn = Table.SplitColumn(SortByLengthDescending,"text",Splitter.SplitTextByRepeatedLengths(1),{"text.1", "text.2", "text.3", "text.4", "text.5"}),
    SortByIndexAscending = Table.Sort(SplitColumn,{{"Indeks", Order.Ascending}}),
    RemoveColumns = Table.RemoveColumns(SortByIndexAscending,{"Indeks", "Length"})
in
    RemoveColumns
```

Regards


----------



## billszysz (Apr 25, 2016)

ooopsss..there is a mistake, so not only from UI.
The new code should be like this below

```
let
    source = Table.FromColumns({ {"abc", "abcde"} }, {"text"}),
    AddIndex = Table.AddIndexColumn(source, "Indeks", 0, 1),
    LengthOfText = Table.AddColumn(AddIndex, "Length", each Text.Length([text]), type number),
    SortByLengthDescending = Table.Sort(LengthOfText,{{"Length", Order.Descending}}),
    ListOfColumns = List.Transform({1..SortByLengthDescending{0}[Length]}, each Text.From(_)),
    SplitColumn = Table.SplitColumn(SortByLengthDescending,"text",Splitter.SplitTextByRepeatedLengths(1), ListOfColumns),
    SortByIndexAscending = Table.Sort(SplitColumn,{{"Indeks", Order.Ascending}}),
    RemoveColumns = Table.RemoveColumns(SortByIndexAscending,{"Indeks", "Length"})
in
    RemoveColumns
```
Only "ListOfColumns" step was manualy written

Regards


----------



## pepe74287 (Apr 25, 2016)

I managed to get it work 

спасибо, dziękuję, dekuji, Thank you


----------



## anvg (Apr 25, 2016)

Thank you, billszysz. 


> Or directly from UI


 Is it always better?  
Regards, Andrey


----------



## billszysz (Apr 25, 2016)

@Pepe74287 Thanks for feedback.
@anvg (Andrey) There was only an example for not to much advanced users  Of course it is not better than good manualy written code 
Regards


----------

