How does Dataflow gen2 handles flat (txt) files

Dataflow Gen2 automatically applies schema inference and may filter out blank rows, headers, or footers during the publishing process to the Lakehouse—even if you don’t explicitly define transformation steps.

To retain those rows, try the following:

• In the source step, disable any automatic schema detection or column promotion.
• Use the “Use first row as headers” option carefully—if your file has multiple header/footer rows, this might skip or misinterpret them.
• Add a custom transformation step to explicitly preserve all rows, including blanks, by treating the file as raw text and parsing it manually (e.g., using Power Query’s Lines.FromBinary and Table.FromList).

Let me know if you want help writing that transformation step.

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!