Cannot convert value "0" to type table

Hi all,

I have searched for days trying to find a solution to this problem, but none of the posts I find match my issue exactly.

I'm reporting from an internal PostgreSQL database, which contains JSONB columns. The JSONB columns contain JSON objects with multiple nested JSON objects going down a few layers.

PBI is able to parse the JSON, and allows me to expand to new rows most of the JSON records. However, when I try to expand on some of the columns, I get the specific Cannot convert the value "0" to type Table error.

As you can see from the query, I've been able to parse the JSON, and expand records and lists to expose the data I need for a number of the columns. However, the last line "Expanded_defect_2" causes the error. I'm assuming that it's possibly data related - some of the defect_2 records are null, the rest contain valid JSON. Other columns I've been able to successfully expand also contain a mixture of null and actual data.

let
    Source = PostgreSQL.Database("XYZ.com", "mgpg"),
    iform_mg_qa_product = Source{[Schema="iform",Item="mg_qa_product"]}[Data],
    #"Parsed JSON" = Table.TransformColumns(iform_mg_qa_product,{{"data", Json.Document}}),
    #"Expanded data" = Table.ExpandRecordColumn(#"Parsed JSON", "data", {"record"}, {"record"}),
    #"Expanded record" = Table.ExpandRecordColumn(#"Expanded data", "record", {"dest", "house", "signed", "quantity", "supplier", "container", "line_text", "CREATED_BY", "description", "line_number", "CREATED_DATE", "product_code", "product_type", "qr_code_scan", "MODIFIED_DATE", "product_count", "product_weight", "text_from_scan", "assessment_line", "supplier_ref_no", "assessment_line1", "container_qrcode", "person_completing_qa", "please_complete_product_check", "text_from_container_report_scan"}, {"dest", "house", "signed", "quantity", "supplier", "container", "line_text", "CREATED_BY", "description", "line_number", "CREATED_DATE", "product_code", "product_type", "qr_code_scan", "MODIFIED_DATE", "product_count", "product_weight", "text_from_scan", "assessment_line", "supplier_ref_no", "assessment_line1", "container_qrcode", "person_completing_qa", "please_complete_product_check", "text_from_container_report_scan"}),
    #"Expanded please_complete_product_check" = Table.ExpandListColumn(#"Expanded record", "please_complete_product_check"),
    #"Expanded please_complete_product_check1" = Table.ExpandRecordColumn(#"Expanded please_complete_product_check", "please_complete_product_check", {"record"}, {"record"}),
    #"Expanded record1" = Table.ExpandRecordColumn(#"Expanded please_complete_product_check1", "record", {"sizes", "photo_1", "photo_2", "product", "comments", "defect_2", "defect_3", "firmness", "temp_rec", "container", "appearance", "fumigation", "shelf_life", "defect_2_pc", "defect_3_pc", "nett_weight", "rots_moulds", "sample_size", "carton_photo", "count_numbers", "defect_2_type", "defect_3_type", "maturity_brix", "defect_2_total", "defect_3_total", "eating_quality", "rots_moulds_pc", "colour_external", "colour_internal", "defect_comments", "grower_lot_code", "pulp_temp_lowest", "pulp_temp_highest", "rots_moulds_total", "appearance_comment", "shelf_life_comment", "sizing_discrepancy", "marketability_score", "newest_packed_on_date", "oldest_packed_on_date", "packed_on_date_exists", "eating_quality_comment", "temperature_related_issue", "further_inspection_required", "additional_photos_if_required", "quality_characteristics_comments"}, {"sizes", "photo_1", "photo_2", "product", "comments", "defect_2", "defect_3", "firmness", "temp_rec", "container.1", "appearance", "fumigation", "shelf_life", "defect_2_pc", "defect_3_pc", "nett_weight", "rots_moulds", "sample_size", "carton_photo", "count_numbers", "defect_2_type", "defect_3_type", "maturity_brix", "defect_2_total", "defect_3_total", "eating_quality", "rots_moulds_pc", "colour_external", "colour_internal", "defect_comments", "grower_lot_code", "pulp_temp_lowest", "pulp_temp_highest", "rots_moulds_total", "appearance_comment", "shelf_life_comment", "sizing_discrepancy", "marketability_score", "newest_packed_on_date", "oldest_packed_on_date", "packed_on_date_exists", "eating_quality_comment", "temperature_related_issue", "further_inspection_required", "additional_photos_if_required", "quality_characteristics_comments"}),
    #"Expanded defect_3" = Table.ExpandListColumn(#"Expanded record1", "defect_3"),
    #"Expanded defect_1" = Table.ExpandRecordColumn(#"Expanded defect_3", "defect_3", {"record"}, {"defect_3.record"}),
    #"Expanded defect_3.record" = Table.ExpandRecordColumn(#"Expanded defect_1", "defect_3.record", {"defect_count"}, {"defect_3.record.defect_count"}),
    #"Expanded additional_photos_if_required" = Table.ExpandListColumn(#"Expanded defect_3.record", "additional_photos_if_required"),
    #"Expanded additional_photos_if_required1" = Table.ExpandRecordColumn(#"Expanded additional_photos_if_required", "additional_photos_if_required", {"record"}, {"additional_photos_if_required.record"}),
    #"Expanded additional_photos_if_required.record" = Table.ExpandRecordColumn(#"Expanded additional_photos_if_required1", "additional_photos_if_required.record", {"photo"}, {"additional_photos_if_required.record.photo"}),
    #"Expanded defect_2" = Table.ExpandListColumn(#"Expanded additional_photos_if_required.record", "defect_2")
in
    #"Expanded defect_2"

I've tried replacing the null values and transforming the data type among other things, all to no avail.

Can anyone provide any suggestions on what I need to look for here?

Any help is much, much appreciated.

Regards,

Iain