Power Query: Split Column by field lengths

0 votes

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 :)

Jan 30 in Power BI by Shubham
• 12,150 points
23 views

1 answer to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
0 votes

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})

answered Jan 30 by Upasana
• 7,560 points

Related Questions In Power BI

0 votes
1 answer

How can I get a column value from previous row in Power Query?

Hi Sindhu, add this line as your ...READ MORE

answered Mar 18 in Power BI by CHERUKURI
• 13,620 points
37 views
0 votes
1 answer

Group by columns on Power BI

On power BI desktop, do the following Add ...READ MORE

answered Sep 25, 2018 in Power BI by Kalgi
• 35,750 points
29 views
0 votes
1 answer

Distinct count filtered by condition using Power BI Dax

Try this, it should work: DistinctCountActiveMonths = CALCULATE( ...READ MORE

answered Oct 5, 2018 in Power BI by Kalgi
• 35,750 points
1,987 views
+1 vote
1 answer

Have column with multiple values in powerBI, for use with a slicer in power bi

It can be achieved depending on the result you ...READ MORE

answered Oct 22, 2018 in Power BI by Hannah
• 14,080 points
811 views
0 votes
1 answer

How to export Power Queries from One Workbook to Another with VBA?

Try solving it using the Workbook. Query ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,190 points
127 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,190 points
59 views
0 votes
1 answer

How can I search for multiple strings?

A simple solution is this: List.ContainsAny(Text.SplitAny("This is a test ...READ MORE

answered Oct 24, 2018 in Power BI by Upasana
• 160 points
70 views
0 votes
1 answer

Power Query Web request results in “CR must be followed by LF” Error

What I think is, it might look ...READ MORE

answered Oct 29, 2018 in Power BI by Shubham
• 12,150 points
61 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 7,560 points
106 views
0 votes
1 answer

Power Query: Custom Columns

This call: FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader) replaces the column names ...READ MORE

answered Dec 12, 2018 in Power BI by Upasana
• 7,560 points
24 views

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.