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, 2019 in Power BI by Shubham
• 13,490 points
2,415 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, 2019 by Upasana
• 8,620 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, 2019 in Power BI by Cherukuri
• 33,030 points
7,582 views
0 votes
0 answers

Creating Stacked Column Chart without unpivot of data in Power Query

h READ MORE

Jan 21, 2020 in Power BI by Prakash
• 140 points

closed Mar 24, 2020 by Sirajul 580 views
0 votes
0 answers

Creating Stacked Column Chart without unpivot of data in Power Query

​Hello, For creating Stacked Column Chart into PBI, ...READ MORE

Jan 21, 2020 in Power BI by Prakash
• 140 points
826 views
0 votes
3 answers

How to add an extra column to the existing table in power bi query editor?

Click on edit queries after loading source ...READ MORE

answered Dec 16, 2020 in Power BI by Roshni
• 10,520 points
76,829 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,160 points
5,839 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,160 points
3,789 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
4,209 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,490 points
1,556 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,620 points
3,183 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,620 points
619 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP