Hi all,
I've searched high and low but haven't been able to find a solution to my exact problem, which is similar to a number of other posts, but not the same from what I can see.
I'm pulling data from an internal PostgresSQL server, which contains 2 fields, one is an integer "record_id", the other is JSONB data "data". The JSONB field contains valid JSON objects, including multiple nested JSON objects.
PBI is able to parsed the JSON, allowing me to expand almost all the nested data into columns. On two of the nested columns however, it fails with the "Cannot convert value "0" to type table" error.
Below is the query. If I remove the last step "#Expanded defect_2", the error disappears. But I need to be able to access data within 'defect_2'. In the source database, defect_2 contains a mixture of "null" and JSON data (it's a non-compulsory field). I've tried all different things, including replacing null with 0, which removes the ability to expand the column, tried transforming the columns data type, etc.
let
Source = PostgreSQL.Database("syd-pg-01.csn2swo3zzki.ap-southeast-2.rds.amazonaws.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"As I've been able to expand many other nested objects, I'm guessing the issue is somehow related to the data, but I don't understand where the 0 is coming from. After this step I would expect to ExpandRecordColumn on the result, then again to access the nested data I need.
First time posting so apologies if this is too much / not enough infomation.
Regards
Iain
