Power Query - split column by variable field lengths - account for null values

Question

This other Power Query question and answer provides a solution to split character delimited text file into columns based on column character count widths.

But it doesn't account for nulls. When null value is encountered it gives an error at one of the columns to right. I can't quite say exactly what is happening. The error is

An error occurred in the ‘SplitText’ query. Expression.Error: The 'count' argument is out of range.

THe code for split function is:

let
    SplitText = (text, lengths) => 
    let
        LengthsCount = List.Count(lengths),
        // Keep track of the index in the lengths list and the position in the text to take the next characters from. Use this information to get the next segment and put it into a list.
        Split = List.Generate(() => {0, 0}, each _{0} < LengthsCount, each {_{0} + 1, _{1} + lengths{_{0}}}, each Text.Range(text, _{1}, lengths{_{0}}))
    in
        Split,
    // Convert the list to a record to 
    ListToRecord = (text, lengths) => 
    let
        List = SplitText(text, lengths),
        Record = Record.FromList(List, List.Transform({1 .. List.Count(List)}, each Number.ToText(_)))
    in
        Record
in
    ListToRecord

Then, in your table, add a custom column that uses this formula:

each SplitText([Column1], {4, 2, 5, 3})

I am using Excel 2010 64 bit and Power Query Version: 2.29.4217.1861

How to modify this to account for nulls?

Bill Szysz Bill Szysz · Accepted Answer · 2016-04-29T09:56:46

Split = List.Generate(() => {0, 0}, each _{0} < LengthsCount, each {_{0} + 1, _{1} + lengths{_{0}}}, each try Text.Range(text, _{1}, lengths{_{0}}) otherwise null)

Power Query - split column by variable field lengths - account for null values

1 Answers