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
• 13,290 points
56 views

1 answer to this question.

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,530 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
• 20,870 points
253 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
• 39,310 points
44 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
• 39,310 points
4,967 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,090 points
1,635 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
280 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
157 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
132 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
• 13,290 points
116 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,530 points
211 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,530 points
35 views