How does Dataflow gen2 handles flat (txt) files

[junminn30]

My txt file has blank rows, headers and footers. I do see these rows when load as txt file into dataflow gen2 but after publishing it to lakehouse, I don't see those rows anymore. I didn't perform any transformation steps in the dataflow gen2 though 

[v-prasare]

Hi @junminn30,

As we haven’t heard back from you, did you get a chance to look at this issue?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[v-prasare]

Hi @junminn30,

As we haven’t heard back from you, we wanted to kindly follow up to check if the issue is resolved? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[burakkaragoz]

Hi @junminn30 ,

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!

[junminn30]

Thank you so much. I've been quite busy with other projects and didn't have the time to respond. I will try and get back to this message if I face any problems.

[v-prasare]

Hi @junminn30,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

@burakkaragoz, @suparnababu8  Thanks for your prompt response

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[burakkaragoz]

Hi @junminn30 ,

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.

[junminn30]

Thank you so much. I've been quite busy with other projects and didn't have the time to respond. I will try and get back to this message if I face any problems.

[suparnababu8 · Accepted solution]

Hi @junminn30 

In Lakehouse, you will not see blank rows becuase it's has limitation (show upto 1000 rows). I replicated your scenario. 
Lets's say I have a txt file(this i uploaded from my machine into dataflow gen2) called Sales_Fabric with column called SalesOrderLinenumber having blanks

Later I published this into my Sample_LH lakehouse. At that I'm able to see like this. Assume that I'm not able to see blank rows.

But if i query the table in SQL analytics end point, It clearly showing blanks in SalesOrderLinenumber column

I think it's sorting in differet way in the lakehouse. 

Hope you are clear now. Let me know if it works

Thank you!!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

[junminn30]

Thank you so much. I've been quite busy with other projects and didn't have the time to respond. I will try and get back to this message if I face any problems.