Some help on understanding nullable types

[Dicken]

Can someone help in my undertanding of nullable, for example if I create a table ; 

= #table ( type table [A =  text  , B =nullable  text  ], 
 { {"aa", "x"}, { "x", "aa" }} ) 

 and then add a could of columns ; 

= Table.AddColumn( 
Table.AddColumn(Source, "M", each
Type.IsNullable(  Value.Type( [A]))), 
"N", each Type.IsNullable( Value.Type( [B])))

 Both result in false,   but if I use Tabel.Schemal  'is nullable' = true for A and false for B which is what I would have expected, 
Can somone explain or point me in the direction of some detailed information on these functions. 

Richard 

[MarkLaf · Accepted solution]

In your table definition, you are saying the column / row field is nullable - i.e. we are saying for a structured data value, it can contain multiple types of primitive values (text or null).

In your AddColumn test, you're basically doing:

1: Type.IsNullable( Value.Type( "x" ) )

2: Type.IsNullable( Value.Type( "aa" ) )

So, it makes sense when you are looking at a primitive text value that PQ would say that it is not nullable.

So, where does the nullability apply? We know [B] is nullable because we just defined the table, but how would we find out otherwise?

Table.Schema shows you the column types and whether they are nullable, as you already pointed out. Although, it provides a sort of analytical output (primitive type, isNullable, NumericPrecision, etc.) rather than a straight here is the type.

TableTest = Table.Schema( Source )

So, is it possible to do some test/expression on [B] that results in nullable text? Yes! As I mentioned at the top, it's all about looking at the type from a structured data perspective as that actually is the only context in which the "nullable" modifier makes sense. Again, besides null, which is its own type, no primitive value is nullable by definition.

ColTypeTest = Type.TableColumn( Value.Type( Source ), "B" )

RowTypeTest = Type.RecordFields( Type.TableRow( Value.Type( Source ) ) ) [B] [Type]

ColListTest = Type.ListItem( Value.Type( Source[B] ) )

RowRecordTest = Type.RecordFields( Type.ListItem( Value.Type( Table.ToRecords( Source ) ) ) ) [B] [Type]

[MarkLaf · Accepted solution]

In your table definition, you are saying the column / row field is nullable - i.e. we are saying for a structured data value, it can contain multiple types of primitive values (text or null).

In your AddColumn test, you're basically doing:

1: Type.IsNullable( Value.Type( "x" ) )

2: Type.IsNullable( Value.Type( "aa" ) )

So, it makes sense when you are looking at a primitive text value that PQ would say that it is not nullable.

So, where does the nullability apply? We know [B] is nullable because we just defined the table, but how would we find out otherwise?

Table.Schema shows you the column types and whether they are nullable, as you already pointed out. Although, it provides a sort of analytical output (primitive type, isNullable, NumericPrecision, etc.) rather than a straight here is the type.

TableTest = Table.Schema( Source )

So, is it possible to do some test/expression on [B] that results in nullable text? Yes! As I mentioned at the top, it's all about looking at the type from a structured data perspective as that actually is the only context in which the "nullable" modifier makes sense. Again, besides null, which is its own type, no primitive value is nullable by definition.

ColTypeTest = Type.TableColumn( Value.Type( Source ), "B" )

RowTypeTest = Type.RecordFields( Type.TableRow( Value.Type( Source ) ) ) [B] [Type]

ColListTest = Type.ListItem( Value.Type( Source[B] ) )

RowRecordTest = Type.RecordFields( Type.ListItem( Value.Type( Table.ToRecords( Source ) ) ) ) [B] [Type]

[Dicken · Accepted solution]

Thank you 

I'm going to have to go through this a few times, (or more)  my musunderstanding I think is that if I define a column as a nullable type then all values in that column should be nullable but this does not seem to be the case. One thing I have notices, the change type step  does not say nullable but if  group 

= Table.Group(#"Changed Type", {"Column1"}, {{"Count", each _, type table [Column1=nullable text]}})

 Table.Group(Source , {"Column1"}, {{"Count", each _, type table [Column1=text]}})

 types are defined as nullable or not, not sure what use it is just thought interestign. 

Richard. 

= Table.Group(#"Changed Type", {"Column1"}, {{"Count", each _, type table [Column1=nullable text]}})