How can I make a complex record type nullable? We cannot convert the value null to type Record.

[DougTheDev]

I get the error "We cannot convert the value null to type Record." when a record is null for a column. I thought making a field type "nullable" would be enough to handle this but I'm missing something.

Here's a minimal example of the issue using the Table.ChangeType from the TripPin tutorial here.

Note that the 2nd element in the example "Source" has a null "Address" while the others are filled out and that Addres is nullable in the "Schema".

let
    Source = {
        [ID = 1, Name = "Alice", Age = 30, IsActive = true, Address = [Street = "123 Main St", City = "Metropolis", ZipCode = 12345]],
        [ID = 2, Name = "Bob", Age = 35, IsActive = false, Address = null], // Address is null here to demonstrate nullable complex fields
        [ID = 3, Name = "Charlie", Age = 25, IsActive = true, Address = [Street = "456 Elm St", City = "Gotham", ZipCode = 67890]]
    },
    ConvertedToTable = Table.FromRecords(Source),
    Address = type [
        Street = text,
        City = text,
        ZipCode = Int64.Type
    ],
    Schema = type table [
        ID = Int64.Type,
        Name = text,
        Age = Int64.Type,
        IsActive = logical,
        Address = nullable Address
    ],
    ChangedType = Table.ChangeType(ConvertedToTable, Schema)
in
    ChangedType{1}[Address]

ChangedType{1}[Address] throws the error while ChangedType{0}[Address] and ChangedType{2}[Address] work fine. Any help would be greatly appreciated. Thanks!

[DougTheDev · Accepted solution]

Thanks for the reply! If anyone else comes accross this issue, I ended up modifing the function from https://learn.microsoft.com/en-us/power-query/helper-functions#tablechangetype to accept nulls

...
Record.ChangeType = (record as nullable record, recordType as type) =>
        if record = null then record else
...

Same idea for list

[v-junyant-msft]

Hi @DougTheDev ,

To resolve this issue, you can use the Table.TransformColumnTypes function in combination with a conditional check to ensure that null values are properly handled.
Please try this M function:

let
    Source = {
        [ID = 1, Name = "Alice", Age = 30, IsActive = true, Address = [Street = "123 Main St", City = "Metropolis", ZipCode = 12345]],
        [ID = 2, Name = "Bob", Age = 35, IsActive = false, Address = null], // Address is null here to demonstrate nullable complex fields
        [ID = 3, Name = "Charlie", Age = 25, IsActive = true, Address = [Street = "456 Elm St", City = "Gotham", ZipCode = 67890]]
    },
    ConvertedToTable = Table.FromRecords(Source),
    // Use a custom function to handle nullable complex types
    HandleNullableComplexType = (table as table, columnName as text, complexType as type) as table =>
        Table.TransformColumns(table, {
            columnName, each if _ is null then null else _ as complexType
        }),
    // Apply the custom function to the Address column
    HandledNullableAddress = HandleNullableComplexType(ConvertedToTable, "Address", type nullable record),
    // Define the schema
    Schema = type table [
        ID = Int64.Type,
        Name = text,
        Age = Int64.Type,
        IsActive = logical,
        Address = nullable type [Street = text, City = text, ZipCode = Int64.Type]
    ],
    // Change the type according to the schema
    ChangedType = Table.ChangeType(HandledNullableAddress, Schema)
in
    ChangedType

In this solution, we introduce a custom function HandleNullableComplexType that explicitly checks if the value of the specified column is null before attempting to cast it to the complex type. This ensures that null values are preserved as-is without causing errors during the type conversion process.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.