Blank rows in Lakehouse

[lovishsood1]

Hi Team,

I'm trying to push data coming from Excel File (Blob Storage) inside a lakehouse using DataFlow Gen 2 and multiple times I have encountered this issue that blank rows are coming from Row 2 - Row 30.

I tried re-checking the Dataflow but there are no blank rows present inside the table and it keeps on coming in almost all tables being pushed into LAKEHOUSE.

Attaching the snapshot for the reference :

[v-prasare]

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.


If we don’t hear back, we’ll go ahead and close this thread. For any further discussions or questions, please start a new thread in the Microsoft Fabric Community Forum we’ll be happy to assist.
Thank you for being part of the Microsoft Fabric Community.

[v-prasare]

Hi @lovishsood1,

We would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

Thanks,

Prashanth Are

MS Fabric community support

[v-prasare]

Hi @lovishsood1,

We would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

@jennratten& @AmiGarala , thanks for your prompt response.

Thanks,

Prashanth Are

MS Fabric community support
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

[jennratten · Accepted solution]

Hello @lovishsood1 -

Can you please check the destination configuration in the dataflow gen2 and see if it is set to overwrite or append?  If data is being appended and blank rows were already in the table, then they will still be present after future runs of the dataflow.  Please also try writing the dataflow table to a new lakehouse table and then check it. 

In order to apply a filter in the dataflow query that removes rows in which all columns are blank or null, you will need to add a step to your query with the following script, or add the step using the ribbon buttons, also shown as an option below.  If using one of the scripts below, be sure to change YourPreviousStepNameHere to the actual name of your previous step.

Use this version if you are adding this step in the advanced editor or if you are adding this step in the formula bar and have View > Script > Query Script enabled:

  #"Removed blank rows" = Table.SelectRows(YourPreviousStepNameHere, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))

 Use this version if you are adding this step in the formula bar and have View > Script > Step Script enabled:

  #"Removed blank rows" = Table.SelectRows(YourPreviousStepNameHere, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))

To add this step using the ribbon buttons, select the entire table and then choose Home > Remove Rows > Remove Blank Rows.

Please let me know if you have any questions about this.  Thanks!

[AmiGarala]

One thing you can do is add a filter in Dataflow Gen2.
In your Dataflow, add a Filter step before writing to the Lakehouse:

NOT (isNull(Column1) AND isNull(Column2) AND isNull(Column3))

Replace cloumns with the actual column names.
This will filter out any rows where all columns are null or blank.