When using the Dataflow g2 feature to ingest data into a Lakehouse or Warehouse (create a new table and ingest), it would be nice to have a UI to check and set "Allow nulls" for columns.
Dataflow g1 and the Power Query editor in the desktop environment, I didn't have much concern about this. However, in Dataflow g2, it is possible to specify ingest destinations and create new tables.
For example, the following query was defined using only GUI(Power Query online) operations.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTYMIETJoqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed column type" = Table.TransformColumnTypes(Source, {{"Column1", Int64.Type}}),
#"Inserted addition" = Table.AddColumn(#"Changed column type", "Addition", each [Column1] + 0, Int64.Type)
in
#"Inserted addition"
This query will cause Data ingest to fail for the following reasons.
Mashup Exception Data Source Error, Error Details: Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: DataSource.Error: Failed to insert a table. Details: Message = We can't insert null data into a non-nullable column.;Message.Format = We can't insert null data into a non-nullable column.
The next query, though, Data ingest succeeds as expected,
let
Source = Table.FromColumns(
{
{1,2,null,4,5}
},
type table [Column1 = nullable Int64.Type]
)
in
Source
The following query fails Data ingest.
let
Source = Table.FromColumns(
{
{1,2,null,4,5}
},
type table [Column1 = Int64.Type]
)
in
Source
So,
[Choose destination settings] - [Column mapping] - [Destination type]
[Power Query editor] - [Schema view]
It would be nice to be able to check or set "Allow nulls" in any of the GUIs.
---
いつもありがとう
Takeshi
4 Comments
+1
I've come across the same issue, and I'm curious why the developers opted to create new tables in the Lakehouse and Warehouse with all columns set to NOT NULL. There are many situations in business where it's more beneficial to keep NULL values in numerical columns instead of filling them with zeros.
What is the current workaround for this?
- Earlier this month, we released an update that makes all dataflow output columns when loading to a new table allow nulls. If you're looking for more granular user controls for this behavior, please create a new idea that is more specific. The current fix should address all comments shared in this idea.
- Status added:Completed
