PQ: How to split a large column into 50 characters columns but split only at whitespace?

DanHuber 

In the event where the above does what you expected feel free to Mark it as solution to help people who Search

Same goes with Split a row into several rows with almost same content. PowerQuery?

Thanks 🙂

DanHuber 

Something cleaner with a function query named ExtractString:

// ExtractString
(string as text, max_length as number) as nullable text =>
let
    Source = try Text.Range(string, 0, max_length),
    Extracted = if Source[HasError] then string
        else if Text.At(Source[Value], max_length-1) =  " " then Source[Value]
        else Text.BeforeDelimiter(Source[Value], " ", {0, RelativePosition.FromEnd}) & " "
in
    if Text.Length(Extracted) = 0 then null else Extracted

Main query code:

// ColumnSplit2
let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],

    PartOne = Table.AddColumn(Source, "Part1", each
        ExtractString([Column1], 50), type text
    ),
    PartTwo = Table.AddColumn(PartOne, "Part2", each
        ExtractString(Text.AfterDelimiter([Column1], [Part1]), 50), type text
    ),
    PartThree = Table.AddColumn(PartTwo, "Part3", each
        let
            remain = try Text.AfterDelimiter([Column1], [Part1] & [Part2])
        in
            if remain[HasError] then null
            else if Text.Length(remain[Value]) = 0 then null
            else remain[Value],
        type text
    ),
    KeptParts = Table.SelectColumns(PartThree, {"Part1", "Part2", "Part3"})
in
    KeptParts

EDIT

A generic solution in attached file 'PQ_SplitToColumnsByLengthAndDelim.xlsx' with 2 parameters in 'PARAM' sheet:

- Number of expected columns

- Max. characters per column

EDIT 2

PQ_SplitToColumnsByLengthAndDelim_Variant.xlsx only has PARAMeter Max. characters per column. The number of columns is calculated: RoundUp(MaxStringLength / MaxCharsPerColumn)

DanHuber 

There was an unecessary if in what I posted yesterday => Previous replies edited