Splitting and Processing Fixed-Width and Delimited Text Files in Power Query: Parsing Legacy Flat File Formats

Introduction

You've just been handed a file. It came from the mainframe, or the ERP system, or the ancient AS/400 that nobody wants to talk about. It's a .txt file, and when you open it in Notepad, it looks like someone typed a novel with no spaces between words — or alternatively, like someone was deeply committed to the pipe character as an artistic medium. This is the reality of legacy flat file formats, and if you work with any system that predates the modern data stack, you will encounter them constantly.

Power Query handles these situations elegantly once you understand the underlying mechanics — but the default "just import it" wizard will fail you in creative ways when your file doesn't conform to simple CSV conventions. Fixed-width files with no delimiters, multi-character delimiters, files with headers buried on row three, files where some fields are quoted and some aren't — these all require deliberate parsing strategies rather than point-and-click hope.

By the end of this lesson, you'll be able to confidently import, parse, and reshape both fixed-width and delimited flat files in Power Query, including the ones that behave badly. You'll understand why Power Query's parsing functions work the way they do, which means you'll be able to adapt them to files you've never seen before.

What you'll learn:

• How to import raw text files and control encoding, line endings, and header detection manually
• How to parse fixed-width flat files by splitting lines at precise character positions
• How to handle non-standard delimiters, multi-character delimiters, and inconsistent quoting
• How to clean and type columns after parsing, including handling packed numeric fields
• How to structure a robust, reusable query that won't break when the source file changes slightly

---

Prerequisites

You should already be comfortable with the Power Query interface — loading data, applying basic transformations, and understanding that the M formula language is behind every step. You don't need to be an M expert, but you should know how to open the Advanced Editor and not panic when you see a let...in block. Familiarity with Table.TransformColumns, Text.Split, and basic list operations will help.

---

Understanding What You're Actually Dealing With

Before writing a single formula, you need to correctly diagnose your file. This sounds obvious, but it's the step most people skip — and then they spend an hour debugging a problem that would have been obvious in thirty seconds.

Open the file in a plain text editor (Notepad, Notepad++, VS Code) before loading it into Power Query. Look for:

• Fixed-width vs. delimited: In a fixed-width file, every record has the same character length, and fields are identified by their position. There is no delimiter. In a delimited file, fields are separated by a character (comma, pipe, tab, semicolon) — though that character may be unusual.
• Encoding: Is it ASCII? UTF-8? Windows-1252 (very common with legacy exports)? Notepad++ can tell you this under Encoding in the menu bar. Getting this wrong produces garbled characters in fields with accented letters or special symbols.
• Line endings: Unix files use LF (\n); Windows files use CRLF (\r\n); old mainframe exports sometimes use just CR (\r). Power Query usually handles this, but sometimes it doesn't.
• Header rows: Are they on line 1? Line 3? Is there a metadata block at the top of the file you need to skip? Does the file have a trailer record (a summary line at the bottom you need to exclude)?
• Quoting: Delimited files sometimes wrap fields in double quotes, especially if a field contains the delimiter itself. Mainframe exports often don't quote at all.

Once you know what you're dealing with, you can pick the right strategy.

---

Loading a Raw Text File Without Letting Power Query "Help"

Power Query's default text file import is designed for well-behaved CSVs. For anything else, you want to load the file as raw binary and parse it yourself. This gives you full control.

In Power Query, go to Get Data > From File > From Text/CSV, select your file, and then — before clicking Load or Transform — look at the preview. If Power Query has split your fixed-width file into one column (good, that's actually what you want at this stage), or has done something clearly wrong with a delimited file, proceed into the editor and then immediately open the Advanced Editor.

For a truly raw approach, use File.Contents combined with Lines.FromBinary:

let
    RawBinary = File.Contents("C:\Data\payroll_export_2024.txt"),
    AsText = Lines.FromBinary(
        RawBinary,
        QuoteStyle.None,
        false,
        1252    // Windows-1252 encoding for legacy mainframe files
    )
in
    AsText

Lines.FromBinary returns a list where each element is one line of the file as a text string. This is your starting point for manual parsing. The parameters are:

• QuoteStyle.None — don't try to interpret quote characters
• false — don't treat the first row as a header
• 1252 — the code page for Windows-1252 encoding (use 65001 for UTF-8, 20127 for ASCII)

To turn this list into a table with one row per line:

let
    RawBinary = File.Contents("C:\Data\payroll_export_2024.txt"),
    LineList = Lines.FromBinary(RawBinary, QuoteStyle.None, false, 1252),
    AsTable = Table.FromList(LineList, Splitter.SplitByNothing(), {"RawLine"}),
    // Remove header rows and trailer records
    DataOnly = Table.Skip(AsTable, 2),    // skip the first 2 rows (metadata/header)
    RemoveTrailer = Table.RemoveLastN(DataOnly, 1)  // remove the final summary record
in
    RemoveTrailer

You now have a single-column table called RawLine where each row contains one complete record as a text string. This is your foundation for everything that follows.

Tip: Table.RemoveLastN is invaluable for files with trailer records. Many mainframe exports end with a totals row or a record count line that would corrupt your analysis if you included it. Always check the last few rows of your file before building the rest of the query.

---

Parsing Fixed-Width Files

Fixed-width files are the format that most intimidates Power Query beginners, because there's no obvious separator. The trick is that you always have a specification — either in documentation, in the file header, or obtained by measuring the fields manually.

Here's a realistic example. You've received a payroll flat file from an HR system. The layout specification says:

Field	Start Position	Length
Employee ID	1	8
Last Name	9	20
First Name	29	15
Department Code	44	4
Pay Rate	48	9
Pay Type	57	1
Hours Worked	58	6

A raw line looks like this (with the understanding that spaces are meaningful padding):

10042891HENDERSON           MARCUS         FIN 002500.00S040.00

Power Query's Text.Middle function is your primary tool here. It takes a text string, a starting position (zero-indexed, so subtract 1 from the spec), and a length:

Text.Middle("10042891HENDERSON           MARCUS         FIN 002500.00S040.00", 0, 8)
// Returns: "10042891"

Text.Middle("10042891HENDERSON           MARCUS         FIN 002500.00S040.00", 8, 20)
// Returns: "HENDERSON           "

To apply this across your entire table, use Table.AddColumn for each field:

let
    RawBinary = File.Contents("C:\Data\payroll_export_2024.txt"),
    LineList = Lines.FromBinary(RawBinary, QuoteStyle.None, false, 1252),
    AsTable = Table.FromList(LineList, Splitter.SplitByNothing(), {"RawLine"}),
    DataOnly = Table.Skip(AsTable, 2),
    RemoveTrailer = Table.RemoveLastN(DataOnly, 1),

    // Parse each field by position
    AddEmployeeID = Table.AddColumn(RemoveTrailer, "EmployeeID",
        each Text.Trim(Text.Middle([RawLine], 0, 8)), type text),

    AddLastName = Table.AddColumn(AddEmployeeID, "LastName",
        each Text.Trim(Text.Middle([RawLine], 8, 20)), type text),

    AddFirstName = Table.AddColumn(AddLastName, "FirstName",
        each Text.Trim(Text.Middle([RawLine], 28, 15)), type text),

    AddDeptCode = Table.AddColumn(AddFirstName, "DepartmentCode",
        each Text.Trim(Text.Middle([RawLine], 43, 4)), type text),

    AddPayRate = Table.AddColumn(AddDeptCode, "PayRate",
        each Number.From(Text.Trim(Text.Middle([RawLine], 47, 9))), type number),

    AddPayType = Table.AddColumn(AddPayRate, "PayType",
        each Text.Trim(Text.Middle([RawLine], 56, 1)), type text),

    AddHours = Table.AddColumn(AddPayType, "HoursWorked",
        each Number.From(Text.Trim(Text.Middle([RawLine], 57, 6))), type number),

    // Drop the raw line column now that we've extracted everything
    CleanTable = Table.RemoveColumns(AddHours, {"RawLine"})
in
    CleanTable

Warning: Positions in the spec are usually 1-indexed (field starts at character 1), but Text.Middle is 0-indexed (first character is position 0). Always subtract 1 from the spec's start position. This is the single most common mistake when parsing fixed-width files.

Notice that Text.Trim wraps every extraction. Fixed-width fields are padded with spaces to fill their full width. Without trimming, "HENDERSON " stays as-is and will cause problems in any lookup or comparison later.

Handling Packed and Implied Decimal Fields

Some mainframe systems store numbers in packed formats. An "implied decimal" is a common one: the number 002500.00 might actually be stored as 000250000 with the last two digits being the decimal places. The file spec will tell you — look for notes like "9(7)V99" (COBOL notation meaning 7 whole digits, implied decimal point, 2 decimal places).

// If PayRate is stored as "000250000" meaning $2500.00
AddPayRate = Table.AddColumn(AddDeptCode, "PayRate",
    each Number.From(Text.Trim(Text.Middle([RawLine], 47, 9))) / 100,
    type number)

For signed packed decimal fields (where the last character encodes the sign), you'll need a more sophisticated approach:

// Some systems use "{" to mean positive zero, "}" to mean negative zero,
// letters A-I for positive 1-9, letters J-R for negative 1-9
// This is rare but real — handle it with a lookup if you encounter it

---

Parsing Non-Standard Delimited Files

Now let's tackle the other category: delimited files that aren't simple CSVs.

Pipe-Delimited Files

Pipe (|) delimiters are extremely common in enterprise data exports. Power Query's Csv.Document function accepts a custom delimiter:

let
    RawBinary = File.Contents("C:\Data\customer_extract_2024.txt"),
    ParsedTable = Csv.Document(
        RawBinary,
        [
            Delimiter = "|",
            Columns = 12,
            Encoding = 1252,
            QuoteStyle = QuoteStyle.None
        ]
    ),
    PromoteHeaders = Table.PromoteHeaders(ParsedTable, [PromoteAllScalars = true])
in
    PromoteHeaders

The Columns parameter tells Power Query how many columns to expect. If you omit it, Power Query will infer it from the first row — which works if your file is consistent, but can fail if some rows have extra pipe characters in data fields.

Tip: If your data fields can contain the delimiter character, you need quoting. The QuoteStyle = QuoteStyle.Csv option tells Power Query to respect double-quote wrapping. QuoteStyle.None ignores all quoting, which is what you want for files that genuinely contain no quotes.

Multi-Character Delimiters

Some legacy systems delimit fields with multiple characters — ||, ~|, or even something like <|>. Power Query's Csv.Document only handles single-character delimiters. For multi-character delimiters, you need to split manually.

Here's the approach using Text.Split on each row:

let
    RawBinary = File.Contents("C:\Data\inventory_feed.txt"),
    LineList = Lines.FromBinary(RawBinary, QuoteStyle.None, false, 1252),
    AsTable = Table.FromList(LineList, Splitter.SplitByNothing(), {"RawLine"}),
    SkipHeader = Table.Skip(AsTable, 1),

    // Split each line on the multi-character delimiter "~|"
    SplitLines = Table.TransformColumns(
        SkipHeader,
        {"RawLine", each Text.Split(_, "~|"), type list}
    ),

    // Expand the list into columns
    ExpandColumns = Table.ExpandListColumn(SplitLines, "RawLine"),

    // At this point each row has repeated—we need to pivot this differently
    // Better approach: use List.Zip to build a structured table

    // Alternative: build table directly from lists
    ParsedRows = List.Transform(
        List.Skip(LineList, 1),
        each Text.Split(_, "~|")
    ),

    ColumnNames = Text.Split(List.First(LineList), "~|"),
    FinalTable = Table.FromRows(ParsedRows, ColumnNames)
in
    FinalTable

Let me show that pattern more cleanly, because List.Transform with Table.FromRows is the right approach for multi-character delimiters:

let
    RawBinary = File.Contents("C:\Data\inventory_feed.txt"),
    LineList = Lines.FromBinary(RawBinary, QuoteStyle.None, false, 1252),

    // First line is the header
    HeaderLine = List.First(LineList),
    ColumnNames = Text.Split(HeaderLine, "~|"),

    // All remaining lines are data
    DataLines = List.Skip(LineList, 1),

    // Split each data line into a list of field values
    ParsedRows = List.Transform(DataLines, each Text.Split(_, "~|")),

    // Build the table
    ResultTable = Table.FromRows(ParsedRows, ColumnNames)
in
    ResultTable

This pattern — split each line into a list, then use Table.FromRows with a list of lists — is extremely versatile. It works for any delimiter, any number of characters.

Handling Inconsistent Column Counts

Real-world legacy files sometimes have rows with missing trailing fields — the ERP system just didn't write the delimiter if there was no value. This causes Table.FromRows to fail because the lists have different lengths.

Fix it by padding each row to the expected length:

let
    RawBinary = File.Contents("C:\Data\inventory_feed.txt"),
    LineList = Lines.FromBinary(RawBinary, QuoteStyle.None, false, 1252),
    HeaderLine = List.First(LineList),
    ColumnNames = Text.Split(HeaderLine, "~|"),
    ExpectedColumns = List.Count(ColumnNames),

    DataLines = List.Skip(LineList, 1),

    // Split and pad each row to exactly ExpectedColumns elements
    ParsedRows = List.Transform(
        DataLines,
        each
            let
                Fields = Text.Split(_, "~|"),
                CurrentCount = List.Count(Fields),
                PaddedFields = if CurrentCount < ExpectedColumns
                    then Fields & List.Repeat({""}, ExpectedColumns - CurrentCount)
                    else List.FirstN(Fields, ExpectedColumns)
            in
                PaddedFields
    ),

    ResultTable = Table.FromRows(ParsedRows, ColumnNames)
in
    ResultTable

The padding logic adds empty strings to short rows and truncates rows that are unexpectedly long. This makes your query resilient to the minor inconsistencies that are so common in legacy exports.

---

Splitting by Position vs. Splitting by Character: Knowing When to Use Each

You now have two main strategies. Here's how to choose:

Use Text.Middle (position-based splitting) when:

• The file has no delimiter characters at all
• The file specification gives you field positions and lengths
• Fields can legally contain any character, including what would otherwise be a delimiter
• You're dealing with a true mainframe COBOL extract

Use Text.Split or Csv.Document (delimiter-based splitting) when:

• Fields are separated by a consistent character or character sequence
• Field lengths vary (names, descriptions, addresses)
• The file came from a modern-ish system, even an old one, that used delimited export

Sometimes you have a hybrid: a file where each line is a fixed-width record, but within that record, one field contains a delimited sub-string. In that case, extract the fixed-width fields first, then apply Text.Split to the specific column that contains the delimited value.

---

Typing and Cleaning Columns After Parsing

After you've split your fields out, everything is text. Now you need to make it useful.

Type Conversion

let
    // ... previous steps ...

    TypedTable = Table.TransformColumnTypes(CleanTable, {
        {"EmployeeID", type text},
        {"LastName", type text},
        {"FirstName", type text},
        {"DepartmentCode", type text},
        {"PayRate", type number},
        {"PayType", type text},
        {"HoursWorked", type number}
    })
in
    TypedTable

Warning: Don't use the automatic type detection step that Power Query offers to insert. It samples rows and guesses — it will guess wrong on numeric codes that look like numbers (employee IDs, zip codes, department codes) and will make them integers, silently dropping leading zeros. Always type columns manually in flat file imports.

Cleaning Common Flat-File Artifacts

Leading zeros on ID fields: Fixed-width ID fields like 00042891 should stay as text. Make sure you type them as type text before any other operation touches them.

Date formats: Mainframe dates are often in YYYYMMDD or MMDDYYYY format with no separator. Parse them with Date.FromText using the format parameter:

// Convert "20241115" to a proper date
Table.AddColumn(TypedTable, "PayDate",
    each Date.FromText(Text.Trim([RawDate]), [Format = "yyyyMMdd"]),
    type date)

Negative numbers: Some legacy systems represent negative numbers by putting the minus sign at the right end (12345-) or by using a special character for the last digit (an old EBCDIC convention). Handle it:

// Handle trailing-sign negative numbers like "012345-"
Table.AddColumn(TypedTable, "Amount",
    each
        let
            Raw = Text.Trim([RawAmount]),
            HasTrailingMinus = Text.End(Raw, 1) = "-",
            NumericString = if HasTrailingMinus
                then "-" & Text.Start(Raw, Text.Length(Raw) - 1)
                else Raw
        in
            Number.From(NumericString),
    type number)

Empty strings vs. null: After trimming, fields with only spaces become empty strings "". Decide whether you want them as null or "" — for most purposes, null is more useful:

Table.ReplaceValue(TypedTable, "", null, Replacer.ReplaceValue, {"LastName", "FirstName"})

---

Building a Reusable, Parameterized Fixed-Width Parser

If you process the same file format regularly, you don't want to hard-code every Text.Middle call. Build a function that accepts a field specification as a table and applies it dynamically.

// Create a query called "ParseFixedWidth" as a function
(RawLines as list, FieldSpec as table) as table =>
let
    // FieldSpec should have columns: FieldName, StartPos (1-indexed), Length

    // Convert FieldSpec to a list of records for easier iteration
    SpecRecords = Table.ToRecords(FieldSpec),

    // Build a table from the raw lines
    RawTable = Table.FromList(RawLines, Splitter.SplitByNothing(), {"RawLine"}),

    // Dynamically add a column for each field in the spec
    ParsedTable = List.Accumulate(
        SpecRecords,
        RawTable,
        (CurrentTable, SpecRow) =>
            Table.AddColumn(
                CurrentTable,
                SpecRow[FieldName],
                each Text.Trim(
                    Text.Middle([RawLine], SpecRow[StartPos] - 1, SpecRow[Length])
                ),
                type text
            )
    ),

    // Remove the raw line column
    Result = Table.RemoveColumns(ParsedTable, {"RawLine"})
in
    Result

Now create a second query that defines your field specification as a table:

// Query: PayrollFieldSpec
let
    SpecTable = Table.FromRows(
        {
            {"EmployeeID",    1,  8},
            {"LastName",      9,  20},
            {"FirstName",     29, 15},
            {"DepartmentCode",44, 4},
            {"PayRate",       48, 9},
            {"PayType",       57, 1},
            {"HoursWorked",   58, 6}
        },
        {"FieldName", "StartPos", "Length"}
    )
in
    SpecTable

And call it from your main query:

// Query: PayrollData
let
    RawBinary = File.Contents("C:\Data\payroll_export_2024.txt"),
    LineList = Lines.FromBinary(RawBinary, QuoteStyle.None, false, 1252),
    DataLines = List.Skip(List.RemoveLastN(LineList, 1), 2),

    Result = ParseFixedWidth(DataLines, PayrollFieldSpec)
in
    Result

This architecture means that when the field spec changes — and it will change — you update one table, not a dozen Table.AddColumn calls.

---

Hands-On Exercise

Here's a real scenario to work through. You'll parse a fixed-width bank transaction export.

Setup: Create a text file called bank_transactions.txt with the following content (use a fixed-width font to verify alignment):

BANK EXPORT FILE - FIRST NATIONAL BANK - ACCOUNT 789456123
GENERATED 20241115
ACCTNUM  TRANDATE TRANTYPE AMT      DESCRIPTION                  STATUS
10045678 20241101 CR       000125050DIRECT DEPOSIT PAYROLL       C
10045678 20241103 DB       000045000AMAZON.COM                   C
10045678 20241105 DB       000008750NETFLIX                      C
10045678 20241108 CR       000010000TRANSFER FROM SAVINGS        C
10045678 20241110 DB       000156789RENT PAYMENT                 C
99 RECORDS IN EXPORT 99 TOTAL RECORDS

The field specification:

Field	Start	Length	Notes
AccountNumber	1	8
TransactionDate	10	8	YYYYMMDD
TransactionType	19	2	CR or DB
Amount	22	9	Implied 2 decimal places
Description	31	29
Status	61	1	C=Cleared, P=Pending

Your tasks:

1. Load the file as raw binary using Lines.FromBinary with Windows-1252 encoding.
2. Skip the first 3 header rows and the last trailer row.
3. Parse each field using Text.Middle with the correct zero-indexed positions.
4. Convert TransactionDate from YYYYMMDD text to a proper date type using Date.FromText.
5. Convert Amount from the 9-character packed string to a proper number by dividing by 100.
6. Add a calculated column called SignedAmount that makes CR amounts positive and DB amounts negative.
7. Type all columns correctly — AccountNumber as text (to preserve leading zeros), everything else as appropriate.

Expected result for the first data row:

• AccountNumber: "10045678"
• TransactionDate: 2024-11-01
• TransactionType: "CR"
• Amount: 1250.50
• Description: "DIRECT DEPOSIT PAYROLL"
• Status: "C"
• SignedAmount: 1250.50

Bonus challenge: Refactor your solution to use the List.Accumulate pattern shown in the reusable parser section above.

---

Common Mistakes & Troubleshooting

Mistake 1: Off-by-one errors in Text.Middle positions

The single most common error. The spec says a field starts at position 9 — you put 9 in Text.Middle. But Text.Middle is zero-indexed, so position 9 means the 10th character. Subtract 1 from every spec position.

How to debug: Add a step that just returns Text.Middle([RawLine], 0, 50) on the first row and count characters manually. Use a monospace font in your text editor.

Mistake 2: Letting Power Query auto-detect types

Power Query's auto-type detection will silently convert "00042891" to the number 42891 and you'll never notice until someone complains that employee IDs are wrong. Always manually specify types for every column, immediately.

Mistake 3: Not handling encoding correctly

If your file contains any non-ASCII characters (accented letters, currency symbols, special characters) and you use the wrong encoding, those characters will be garbled. The symptoms: fields look right except for one or two characters that appear as ? or random symbols.

Fix: Use code page 1252 for Western European Windows files, 65001 for UTF-8, 850 for DOS code page, 37 for EBCDIC (rare but exists). Notepad++ shows the encoding in the status bar.

Mistake 4: Empty lines at end of file

Lines.FromBinary often returns a final empty string if the file ends with a newline character. This causes Text.Middle to return empty strings for all fields, which then fail type conversion. Always check for and remove empty rows:

RemoveEmpty = Table.SelectRows(RawTable, each [RawLine] <> "" and [RawLine] <> null)

Mistake 5: Misidentifying the delimiter

A file that looks pipe-delimited might actually use \t (tab) — and tabs look like spaces in many text editors. In Notepad++, turn on View > Show Symbol > Show All Characters to see tabs, spaces, and line endings explicitly. In VS Code, the status bar shows the file encoding and you can toggle whitespace display.

Mistake 6: Using Text.Split when fields contain the delimiter

If a description field says "Red|Blue Widgets" in a pipe-delimited file (and the file doesn't quote it), Text.Split will give you an extra column. The right fix is to get proper quoting from whoever generates the file. The workaround is to use Csv.Document with QuoteStyle.Csv if the file uses quoting, or to limit the split count using Splitter.SplitTextByDelimiter with a max column count.

Mistake 7: Row count mismatches after filtering

If you skip headers and trailers but get more or fewer rows than expected, check whether the file has Windows line endings (\r\n) and whether Lines.FromBinary is handling them correctly. On some systems, you might see \r appearing as the last character in each line. Check with:

Text.End(List.First(DataLines), 1) = Character.FromNumber(13)  // returns true if CR present

Strip it with Text.TrimEnd([RawLine], Character.FromNumber(13)) in a Table.TransformColumns step.

---

Performance Considerations

For files under 50,000 rows, performance is rarely a concern with the approaches shown here. For larger files, keep these in mind:

List.Accumulate for dynamic column generation is elegant but can be slower than explicit Table.AddColumn chains for very wide files with many columns. If you have 50+ columns and millions of rows, benchmark both approaches.

Lines.FromBinary vs. Csv.Document — Csv.Document is internally optimized and will outperform the manual Lines.FromBinary + Text.Middle approach for delimited files. Use Csv.Document whenever the file format allows it.

Avoid row-by-row operations in M — Table.AddColumn is vectorized and efficient. Functions that iterate row by row in M (like custom functions called per row) are slower. Keep your column expressions simple.

Load to data model, not to Excel table — for flat files you process regularly, loading to the Power Pivot data model rather than to a worksheet table removes the 1M row Excel limit and generally improves refresh performance.

---

Summary & Next Steps

You now have a complete toolkit for handling the flat files that modern import wizards can't touch. To summarize what you've built:

• Lines.FromBinary gives you raw control over how a text file is read, including encoding and line handling.
• Text.Middle is the core tool for fixed-width parsing — zero-indexed, always trim the result.
• Table.FromRows with List.Transform handles multi-character and non-standard delimiters elegantly.
• List.Accumulate lets you build dynamic, spec-driven parsers that adapt to layout changes without rewriting formulas.
• Manual type assignment is non-negotiable for flat file imports — never let auto-detection near your ID fields.

What to tackle next:

• Parameterized file paths and folder connectors — if you receive the same file format daily with a new filename, learn how to use Folder.Contents combined with the parsers you've built here to process all files in a folder automatically.
• Splitting multi-record-type flat files — some legacy formats interleave different record types in one file (identified by a record type code in column 1). Learn how to filter by record type and process each subset differently.
• Error handling in M — use try...otherwise patterns to make your parsing resilient to individual bad records without failing the entire query.
• Combining flat file parsing with lookups — once you have your parsed table, join it to reference data (department names, cost center codes, product catalogs) using Table.NestedJoin.

The files you've been handed are problems someone else created — but with these tools, they don't have to stay problems for long.