Power Query Split Column by field lengths

Question

In PowerQuery I need to import a text file in which each line is the concatenation of a number of fields and each field has a fixed specific length(fixed width file). When I import it I get a table with the following format.

AAAABBCCCCCDDD

Now, I need to add more columns in the given fashion.

Column1: AAAA
Column2: BB
Column3: CCCCC
Column4: DDD

I'm trying to achieve this by using the Split Column > By number of character utility but it seems to able to insert only one single length at a time. This way I'd have to repeat the entire process 3 times, adding one column each time and using the Once, as far left as possible option for the aforementioned utility.

This will cause practical difficulty in a real-time scenario. Can anybody help me with this?

Thanks :)

Answer 1

Start by creating a query with the following formula. I'll be calling it SplitText:

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

Add a custom column that uses the following formula.

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