Dataflow Gen2 write no value in Lakehouse

[Theo_MS]

Hello,

The dataflow gen2 write rows with no-value in Lakehouse, but the structure is created.

Dataflow refresh is exectued with success

The pipeline can execute this dataflow with same result.

In the dataflow developement, data are visible and can be manipulate.

Nothing specific, it is a simple extraction from an Excel file in a SharePoint folder.

I have try recreate the elements after deleting the dataflow and the lakehouse. 

The result is identical.

Yesterday, everything works perfectly. 

What is the reason behind this behaviour? and how to fix it?

Regards,

Théo

[AlfredOne]

Is it possible that we are the only ones who have this problem?

[AlfredOne]

I've the same issue. this is very odd and I had to recreate all my pipelines in Gen2 removing underscores. Also in the SQL endpoint, when I want to copy data after a query, the result doesn't work. 

[Bayernboy23]

Ya, there is a user for my company who is experincing the same issue. We tried removing spaces and are still experincing the issue. I submitted a ticket to Microsoft in hopes that they can identify what is going on. Weird thing is that there table has worked for several months and is now all the sudden failing. No alterations or metadata changes have been made to the Dataflow or backend table. 

[Bayernboy23]

Quick update. We submitted the ticket to Microsoft and found the following: 

1) Removing spaces in Dataflow Gen2 column names and writing those back to the lakehouse worked. 

2) Microsoft recommeneded upgrading our gateways to version 3000.266.4, as this version seems to remideate this issue. We were unable to verify if this worked, since our company has to vet new upgrades before relaesing them. Here is the msft doc that explains this issue Known issue - Gateway causes lakehouse tables with null values - Microsoft Fabric | Microsoft Learn

3) Here is the verbiage we received from Microsoft as to why this issue all the sudden happend:

"

We’ve reached out to our Product Group (PG) to try to get more information, and they shared the following back. In the past, SQL endpoint in Fabric did not support a Delta feature called “column mapping” which is required for column names to contain spaces. While Dataflows had support for reading and writing this feature, it was disabled by a feature switch in the UI that would prevent such column names from being created. When SQL endpoint finally enabled support, the Dataflows UI was changed to finally allow these column names through the feature switch. However, this feature switch also required an update in the On-premises data gateway software. Unfortunately, due to a (rare) oversight, the switch was enabled before the On-premises data gateway was released. If you have modified the dataflow (after the feature switch was turned on) and added a new field with a column name containing space, then that could trigger this known issue".

[AlfredOne]

Thank you for the update. do you know how to upgrade the gateway version? 

[Gmeza · Accepted solution]

@Theo_MS, I have the exact experience you are having, and it started about the same time you raised this post. I have a running theory I have tested on my end for a solution, but if you could confirm if it works for you that would be great.

My Dataflow is a regular CSV to Lakehouse flow. For simplicity, I have two columns to load, 

Here is the change I have noticed since I started experiencing this error. When using the Automatic Settings in the Lakehouse Data Destination screen, the "Destination Columns" no longer add an underscore if the column names have a space. It will stick to the column names with a space in them.

This results in NULL values in my entire table

 My Current Solution: I rename the columns to add an underscore where needed so as not to have any spaces in the column name. I kept the Lakehouse settings the same. 

After the refresh was done, the values then appeared.

I guess that this has to do something with Lakehouses columns and spaces; seems like it's causing some issues with our dataflow. I hope this helps troubleshoot your issue.

[AlfredOne]

When we have hundreds of columns, this is not a feasible solution.

[jennratten]

Hello @AlfredOne  - in the interim, here is a code you can use to automatically update all column headers in a table to replace the spaces with underscores.

let
    Source = YourSourceHere,  // Replace with your actual data source
    RenameColumns = Table.TransformColumnNames(Source, each Text.Replace(_, " ", "_"))
in
    RenameColumns

[Theo_MS]

Hi @Gmeza,

thank you for your clear explication.

I found out the resolution the same way as you. 

With the support ticket, they didn't provide a clear answer about this changing behaviour.

It seems that they rollback on this "futur" feature.

[miguel]

Could you please raise a support ticket so an engineer can investigate this a much deeper? Here's the link to raise the support ticket:

https://support.fabric.microsoft.com/support

Do feelf ree to share the support ticket# here or as a private message to me so we can take a closer look at it.