Manual Fixed Length Text Extract

Nick Burns

Member
Joined
May 24, 2017
Messages
157
Reaction score
0
Points
16
Excel Version(s)
Office 365
There's a method to my madness, but go with me here.
I'm reading a Fixed-Length Text file. I'd like to parse it manually rather than PQ doing automatically.
I've got the logic down to the List of each field length, but I'm not sure how to get a list of each "field" from these lengths.
I'm not sure if I'd use List.Accumulate or another List.Transform.

I've attached a file that gets as far as the list of the lengths not sure what my next step is.

1663282905365.png
 

Attachments

  • FixedLength.xlsx
    15.4 KB · Views: 2

pinarello

Member
Joined
Jun 21, 2019
Messages
190
Reaction score
2
Points
18
Location
Germany
Excel Version(s)
Office 365
See attached my solution. You can shorten the steps of the query a bit.
 

Attachments

  • xlguru - Manual Fixed Length Text Extract (PQ).xlsx
    22.1 KB · Views: 2
Last edited:

Nick Burns

Member
Joined
May 24, 2017
Messages
157
Reaction score
0
Points
16
Excel Version(s)
Office 365
See attached my solution. You can shorten the steps of the query a bit.
Thanks! That gave me the directions I needed.

Here's what I came up with using the steps I had in my original file:

M:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source", type text}}),
    SourceHeaders = Table.Skip(#"Changed Type",each not Text.Contains([Source], "Location")),
    #"Added Conditional Column" = Table.AddColumn(SourceHeaders, "Custom", each if Text.StartsWith([Source], "-------- ")
    then
        List.Combine({
            {0},
            List.Transform(
               List.PositionOf(
                   Text.ToList([Source]),
                   " ", Occurrence.All),
               each _ + 1 )
            })
    else null),
    #"Filled Up" = Table.FillUp(#"Added Conditional Column",{"Custom"}),
    FieldBreaks = #"Filled Up"{0}[Custom],
    SplitColumns = Table.SplitColumn(SourceHeaders, "Source", Splitter.SplitTextByPositions(FieldBreaks) )
in
    SplitColumns
 

Nick Burns

Member
Joined
May 24, 2017
Messages
157
Reaction score
0
Points
16
Excel Version(s)
Office 365
On further inspection, I could append a space to [Source] in my ToList() function and save another step or two!

M:
#"Added Conditional Column" = Table.AddColumn(SourceHeaders, "Custom", each if Text.StartsWith([Source], "-------- ") then List.PositionOf(Text.ToList(" " & [Source]), " ",Occurrence.All) else null)
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,170
Reaction score
12
Points
38
Excel Version(s)
365
Another?:
M:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitPosns = List.RemoveLastN(Text.PositionOf(" " & Table.SelectRows(Source, each Text.StartsWith([Source], "--"))[Source]{0}," ",Occurrence.All),1),
    SplitColumnbyPositions = Table.SplitColumn(Source, "Source", Splitter.SplitTextByPositions(SplitPosns)),
    PromotedHeaders = Table.PromoteHeaders(SplitColumnbyPositions, [PromoteAllScalars=true]),
    FilteredRows = Table.SelectRows(PromotedHeaders, each not Text.StartsWith([#"Customer "], "---"))
in
    FilteredRows
 

Nick Burns

Member
Joined
May 24, 2017
Messages
157
Reaction score
0
Points
16
Excel Version(s)
Office 365
Another?:
M:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitPosns = List.RemoveLastN(Text.PositionOf(" " & Table.SelectRows(Source, each Text.StartsWith([Source], "--"))[Source]{0}," ",Occurrence.All),1),
    SplitColumnbyPositions = Table.SplitColumn(Source, "Source", Splitter.SplitTextByPositions(SplitPosns)),
    PromotedHeaders = Table.PromoteHeaders(SplitColumnbyPositions, [PromoteAllScalars=true]),
    FilteredRows = Table.SelectRows(PromotedHeaders, each not Text.StartsWith([#"Customer "], "---"))
in
    FilteredRows

Interesting...The text is a part of a much larger dataset, so the space at the end may have been my mistake when creating the sample. I'll have to check the data to see if there is a trailing space. A Text.Trim() could also remove it as well.

An additional step after Promoting Headers is to clean up the Column Names:
= Table.TransformColumnNames( #"Promoted Headers", Text.Trim)
 
Last edited:
Top