Forum Discussion

JoeFields's avatar
JoeFields
Frequent Visitor
2 years ago

Error when applying schema with nullable records

I am getting errors on Record type objects when I try to apply a schema in my custom connector even though I have added the nullable type identifier.

The below example shows whenever I have location = null, it throws an error.

 

Here is a condensed listing of how I am defining my schema in my PQ file:

ProjectType = type table [
...
    location = nullable LocationType,
...],
    LocationType = type [
        country = text,
        state = text,
        streetName = text,
        streetNumber = text,
        suite = text,
        city = text,
        zip = text,
        complete = text,
        coords = nullable CoordsType
    ]

I have defined location as being a nullable record.

 

I copied the Table.ChangeType helper function from here: https://learn.microsoft.com/en-us/power-query/helper-functions#tablechangetype.

 

If I change the schema definition to nullable any then it works but then I do not have my schema applied for the location records that are provided.

ProjectType = type table [
...
    location = nullable any,
...],

 

Need Help

1 Reply

  • jennratten's avatar
    jennratten
    Super User
    2 years ago

    Hello JoeFields - you need to add a step in your query (before the point at which the error is thrown) which converts null values into empty records so that the field can be evaluated like a record, like this:

    Table.TransformColumns(Source, {{“Column1”, each if Value.Is(_, type record) then _ else [a=_] }} )