topic Re: Which types of columns are considered non-nullable? (Mashup Error 104100) in Dataflow

Which types of columns are considered non-nullable? (Mashup Error 104100)

JayJay11 — Thu, 22 Feb 2024 15:51:15 GMT

Hi all,

for a Gen2 Dataflow I am getting the almighty error 104100 regarding non-nullable columns. I tried now many things, in particular replacing null values in custom columns. So far I did not manage to run the flow successfully (destination Lakehouse table).

Can someone tell me, which types of columns are considered non-nullable in general?

Re: Which types of columns are considered non-nullable? (Mashup Error 104100)

miguel — Thu, 22 Feb 2024 16:17:32 GMT

non-nullable columns are columns that can't contain a null value. You could replace "null" with a value of your choice to make sure that your columns don't contain null values

Re: Which types of columns are considered non-nullable? (Mashup Error 104100)

JayJay11 — Thu, 22 Feb 2024 16:22:31 GMT

Thank you @miguel this I understand. But which types of columns can't contain null values? So far I figured that custom columns cannot contain null values.

Second question: Is there a way to make a column nullable in the Lakehouse table?

And last but not least: Is there a way to make the error message telling us users which columns are affected? Otherwise it is try&error really.

Re: Which types of columns are considered non-nullable? (Mashup Error 104100)

miguel — Thu, 22 Feb 2024 16:36:54 GMT

it depends on how the table was created. If you are using Dataflow Gen2 to create it, then by default it sets all columns to be non-nullable meaning that all your columns require to comply to this. If you create the table using other tools and you just use Dataflow Gen2 to put the data in that table, then it'll rely on what you defined as nullable and non-nullable fields during the creation of that table.

We're actively working on making the default option to be nullable, but will also pass this feedback to the owner of this feature.

You'd need to make sure that all your fields comply with the non-nullable aspect. Not just the custom columns.

You can also take a look at the new features that were released today as well as a section that talks about the non-nullable aspect:

Dataflows gen 2 data destinations and managed settings | Microsoft Fabric Blog | Microsoft Fabric

Re: Which types of columns are considered non-nullable? (Mashup Error 104100)

JayJay11 — Thu, 22 Feb 2024 16:41:21 GMT

It is actually not true, that all columns cannot have null values. I can without any problems load a table, only consisting of string columns that have sometimes null values to a lakehouse table.

Why did you mark your answer as the accepted solution? It is not the solution, I still do not know, which type of column is non-nullable. I would please like to have this simple question answered.

In the meantime I created a new ticket.

Re: Which types of columns are considered non-nullable? (Mashup Error 104100)

JayJay11 — Thu, 22 Feb 2024 17:59:24 GMT

Okay to anyone wondering how to solve this issue effectively in a query:

Everywhere, where either a new custom column is created or an existing column is manipulated, you have to add "nullable" for the data type determination.

Even when you are just trimming a text column:

Table.TransformColumns(#"Changed Type",{{"DocumentItemText", Text.Trim, type nullable text}, {"InfoGegenkonto", Text.Trim, type nullable text}})

Re: Which types of columns are considered non-nullable? (Mashup Error 104100)

miguel — Thu, 22 Feb 2024 20:05:57 GMT

The information the blog post shared has a way to address this and I see from your latest response that you've found it. While this is quite a manual way to address it, we don't have other ways to show you what columns accept nulls or non-nulls as its information from the destination. You can request this as a new feature using the following link:

https://aka.ms/FabricIdeas