Query Editor to Split the Text String

[BhaveshPatel]

Hi Everyone,

I have come aross a typical problem of splitting a text using query editor.

Problem: I have a text string of more than 255 characters long and I would like to split the text into less than and equal to 50 characters.

However, if the 50th character is falls in the middle of the full word, it should not split the full words but split the text by the space character.

For Example:

Split the below text into columns described underneath:

Text ='AutoCAD 2017 Commercial New Single-user ELD Annual Subscription with Advanced Support SPZD AutoCAD 2017 Commercial New Single-user ELD Annual Subscription with Basic Support AutoCAD 2017 Commercial New Single-user ELD Annual Subscription with' 

Col1:AutoCAD 2017 Commercial New Single-user ELD

Col2: Annual Subscription with Advanced Support SPZD

Col3:AutoCAD 2017 Commercial New Single-user ELD

Col4:Annual Subscription with Basic Support AutoCAD

Col5:2017 Commercial New Single-user ELD Annual

Col6:Subscription with

Col7:null

Col8:null

Col9:null

I think this is the one of the toughest problem and I would like you to test your skills on this:

Thank you in advance for your time and hard work.

Best Regards,

Bhavesh

[MarcelBeug · Accepted solution]

It was tough indeed:

(TextString as text, LineLength as number) as list =>
    let
    fnWT = List.Generate(() => 
                  [TextPart      = if Text.Length(TextString) <= LineLength
                                   then TextString 
                                   else if Text.PositionOf(Text.Start(TextString,LineLength + 1)," ",Occurrence.Last) > -1
                                        then Text.Start(TextString,List.Min({LineLength + 1,Text.PositionOf(Text.Start(TextString,LineLength + 1)," ",Occurrence.Last)}))
                                        else Text.Start(TextString,List.Min({LineLength,Text.Length(TextString)})),
                   RemainingText = if Text.Length(TextString) <= LineLength
                                   then "" 
                                   else if Text.PositionOf(TextPart," ") > -1 
                                        then Text.Trim(Text.End(TextString,Text.Length(TextString)-Text.Length(TextPart)-1))
                                        else Text.Trim(Text.End(TextString,Text.Length(TextString)-Text.Length(TextPart)))],

                   each Text.Length([TextPart])>0,

                   each [TextPart      = if Text.Length([RemainingText]) <= LineLength
                                         then [RemainingText]
                                         else if Text.PositionOf(Text.Start([RemainingText],LineLength + 1)," ",Occurrence.Last) > -1
                                              then Text.Start([RemainingText],List.Min({LineLength + 1,Text.PositionOf(Text.Start([RemainingText],LineLength + 1)," ",Occurrence.Last)}))
                                              else Text.Start([RemainingText],List.Min({LineLength,Text.Length([RemainingText])})),
                         RemainingText = if Text.Length([RemainingText]) <= LineLength
                                         then ""
                                         else if Text.PositionOf(TextPart," ") > -1
                                              then Text.Trim(Text.End([RemainingText],Text.Length([RemainingText])-Text.Length(TextPart)-1))
                                              else Text.Trim(Text.End([RemainingText],Text.Length([RemainingText])-Text.Length(TextPart)))],

                   each [TextPart])
in
    fnWT

[MarcelBeug · Accepted solution]

It was tough indeed:

(TextString as text, LineLength as number) as list =>
    let
    fnWT = List.Generate(() => 
                  [TextPart      = if Text.Length(TextString) <= LineLength
                                   then TextString 
                                   else if Text.PositionOf(Text.Start(TextString,LineLength + 1)," ",Occurrence.Last) > -1
                                        then Text.Start(TextString,List.Min({LineLength + 1,Text.PositionOf(Text.Start(TextString,LineLength + 1)," ",Occurrence.Last)}))
                                        else Text.Start(TextString,List.Min({LineLength,Text.Length(TextString)})),
                   RemainingText = if Text.Length(TextString) <= LineLength
                                   then "" 
                                   else if Text.PositionOf(TextPart," ") > -1 
                                        then Text.Trim(Text.End(TextString,Text.Length(TextString)-Text.Length(TextPart)-1))
                                        else Text.Trim(Text.End(TextString,Text.Length(TextString)-Text.Length(TextPart)))],

                   each Text.Length([TextPart])>0,

                   each [TextPart      = if Text.Length([RemainingText]) <= LineLength
                                         then [RemainingText]
                                         else if Text.PositionOf(Text.Start([RemainingText],LineLength + 1)," ",Occurrence.Last) > -1
                                              then Text.Start([RemainingText],List.Min({LineLength + 1,Text.PositionOf(Text.Start([RemainingText],LineLength + 1)," ",Occurrence.Last)}))
                                              else Text.Start([RemainingText],List.Min({LineLength,Text.Length([RemainingText])})),
                         RemainingText = if Text.Length([RemainingText]) <= LineLength
                                         then ""
                                         else if Text.PositionOf(TextPart," ") > -1
                                              then Text.Trim(Text.End([RemainingText],Text.Length([RemainingText])-Text.Length(TextPart)-1))
                                              else Text.Trim(Text.End([RemainingText],Text.Length([RemainingText])-Text.Length(TextPart)))],

                   each [TextPart])
in
    fnWT

[BhaveshPatel]

Hi @MarcelBeug

Sorry to disturb you once again.

What if I would like to split it into columns.

[MarcelBeug · Accepted solution]

The result of the function is a list with text parts.

You can convert this to a table, transpose and expand it.

Below some example code, including a nice trick to expand the tables with a dynamic number of columns.

    #"Invoked Custom Function" = Table.AddColumn(#"Added Custom3", "Splitted", each SplitText([TextField], 10)),
    #"Added Custom1" = Table.AddColumn(#"Invoked Custom Function", "Table", each Table.Transpose(Table.FromList([Splitted], Splitter.SplitByNothing()))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "ColCount", each Table.ColumnCount([Table])),
    NoOfColumns = List.Max(#"Added Custom2"[ColCount]),
    #"Expanded Table" = Table.ExpandTableColumn(#"Added Custom2", "Table", Table.ColumnNames(#table(NoOfColumns,{})))

[BhaveshPatel]

Hi @MarcelBeug,

Brilliant Work. This is what driving me to come to PowerBI community. 

Thank you for taking a time to solve the problem.

I can say that you are the master in PowerQuery.

Thank you once again for your time and hard work.