Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
JoeFields
Frequent Visitor

Help with Error when applying schema when API returns null 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 in the schema.

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

JoeFields_0-1693596163674.png

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:

JoeFields_0-1696280976223.pngJoeFields_1-1696280993734.png

 

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?

 

1 REPLY 1
JoeFields
Frequent Visitor

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-Tab...

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

December 2025 Power BI Update Carousel

Power BI Monthly Update - December 2025

Check out the December 2025 Power BI Holiday Recap!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.