Power Query - split text to variable number of columns

[pepe74287]

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]

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]

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]

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]

I managed to get it work

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

 

[anvg]

Thank you, billszysz.

Or directly from UI

Is it always better?
Regards, Andrey

 

[billszysz]

@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