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,710 points
28 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
• 8,110 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
• 14,860 points
61 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
• 36,220 points
30 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
• 36,220 points
2,669 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
1,011 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
160 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
79 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
85 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,710 points
74 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
• 8,110 points
145 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
• 8,110 points
26 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.