Help with Error when applying schema when API returns null records

JoeFields — Mon, 02 Oct 2023 21:15:26 GMT

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 in the schema.

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. I also have been following the TripPin examples to build out my connector.

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, ...],

The REST API I am connecting to sometimes has the location records filled in but sometimes location is just null as in the below examples:

I can't figure out where in the Table.ChangeType helper function I can make a tweak to cover the null record conditions but I would have thought making it nullable would have done the trick. Any thoughts?

Re: Error when applying schema with nullable records

JoeFields — Fri, 01 Sep 2023 19:30:14 GMT

This appears to be similar to this previous unanswered post from a few years ago: https://community.fabric.microsoft.com/t5/Power-Query/Error-with-nullable-record-type-when-using-Table-ChangeType/m-p/940504#M32453

topic Help with Error when applying schema when API returns null records in Developer

Help with Error when applying schema when API returns null records

Re: Error when applying schema with nullable records