Manual Fixed Length Text Extract

Nick Burns

Member
Joined
May 24, 2017
Messages
162
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: 3
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: 3
Last edited:
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
 
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)
 
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
 
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:
Back
Top