How does Dataflow gen2 handles flat (txt) files

Handling flat .txt files in Dataflow Gen2 can be a bit tricky, especially when dealing with blank rows, headers, or footers.

Here are a few clarifications and suggestions:

🔍 Why Blank Rows Might Disappear

When you publish your Dataflow Gen2 output to a Lakehouse table, the system automatically applies schema inference and optimization, which may exclude rows that appear empty or don't match the inferred schema. This is especially true if:

• The blank rows contain no data or only whitespace.
• The file has inconsistent row structures (e.g., footers or headers mid-file).

Also, the Lakehouse UI preview is limited to 1,000 rows and may not show all data. To verify, try querying the table using the SQL Analytics Endpoint—you might find the missing rows are actually there.

✅ Recommendations

1. Explicitly Handle Blank Rows in Power Query
   In your Dataflow Gen2, add a step to preserve or flag blank rows before publishing. For example:

   • Add a conditional column to detect empty rows.
   • Replace nulls with placeholders if needed.

2. Use a Notebook for More Control
   If you need precise control over how rows are interpreted (e.g., skipping footers, preserving blanks), consider using a Fabric Notebook with PySpark to read and process the .txt file before writing to the Lakehouse.

3. Check File Encoding and Delimiters
   Ensure the file encoding (e.g., UTF-8) and delimiters are correctly interpreted by the Dataflow. Misinterpretation can lead to row loss or misalignment.

Let me know if you'd like a sample Power Query or notebook script to handle this more robustly!