I have 2 tables in my database Assets and Subtypes that contain JSON data. They are joined together via "labels". This is my first time trying to parse JSON data in Power Query.
The output from this should be below:
i.e. "2dp1Label":"288" in the Asset data joins to "source":"2dp1Label" in the Subtype data and the decription: "Coil Width (Total)"
Coil Width (Total): 288
more examples:
"2dp2Label" Coil Height (Total): "131"
"1000Char1Label" Notes: "ALL OF WEST WING"
"Int2Label" Design Supply Air: 124500
Below are the 2 examples of the JSON data
Assets
{
"2dp1Label":"288",
"2dp2Label":"131",
"2dp3Label":null,
"2dp4Label":null,
"4dp1Label":null,
"4dp2Label":null,
"4dp3Label":null,
"4dp4Label":null,
"4dp5Label":null,
"4dp6Label":null,
"Int1Label":null,
"Int2Label":124500,
"Bool1Label":true,
"Bool2Label":false,
"Bool3Label":false,
"Bool4Label":true,
"Bool5Label":false,
"Bool6Label":false,
"Date2Label":null,
"Date3Label":null,
"Date4Label":null,
"Date5Label":null,
"Date6Label":null,
"30Char1Label":false,
"30Char2Label":null,
"30Char3Label":null,
"30Char4Label":null,
"60Char1Label":null,
"60Char2Label":null,
"1000Char1Label":"ALL OF WEST WING",
"1000Char2Label":null,
"1000Char3Label":null,
"1000Char4Label":null,
"1000Char5Label":null,
"1000Char6Label":null,
"1000Char7Label":"23.73.13"
}
Subtypes
{
"uv?":{
"date":"",
"type":"Boolean",
"order":16,
"action":"",
"length":null,
"source":"Bool6Label",
"options":null,
"subtype":"23.73.13",
"required":false,
"type_code":"23.73.00",
"description":"UV?",
"subtype_code":"23.73.13",
"unit_of_measure":"",
"type_description":"AHU - Indoor Central-Station",
"subtype_description":"Indoor Modular AHU"
},
"notes":{
"date":"",
"type":"String",
"order":24,
"action":"",
"length":null,
"source":"1000Char1Label",
"options":null,
"subtype":"23.73.13",
"required":false,
"type_code":"23.73.00",
"description":"Notes",
"subtype_code":"23.73.13",
"unit_of_measure":"",
"type_description":"AHU - Indoor Central-Station",
"subtype_description":"Indoor Modular AHU"
},
"supply_type":{
"date":"",
"type":"Select",
"order":5,
"action":"",
"length":null,
"source":"1000Char51Label",
"options":[
"Single Supply Path",
"Dual-Duct Single Path",
"Dual-Duct Dual Path",
"Multi-Zone",
"Terminal Boxes"
],
"subtype":"23.73.13",
"required":false,
"type_code":"23.73.00",
"description":"Supply Type",
"subtype_code":"23.73.13",
"unit_of_measure":"",
"type_description":"AHU - Indoor Central-Station",
"subtype_description":"Indoor Modular AHU"
},
"air_blender?":{
"date":"",
"type":"Boolean",
"order":15,
"action":"",
"length":null,
"source":"Bool5Label",
"options":null,
"subtype":"23.73.13",
"required":false,
"type_code":"23.73.00",
"description":"Air Blender?",
"subtype_code":"23.73.13",
"unit_of_measure":"",
"type_description":"AHU - Indoor Central-Station",
"subtype_description":"Indoor Modular AHU"
},
"outside_width":{
"date":"",
"type":"Integer",
"order":19,
"action":"",
"length":null,
"source":"Int50Label",
"options":null,
"subtype":"23.73.13",
"required":false,
"type_code":"23.73.00",
"description":"Outside Width",
"subtype_code":"23.73.13",
"unit_of_measure":"in",
"type_description":"AHU - Indoor Central-Station",
"subtype_description":"Indoor Modular AHU"
},
"outside_height":{
"date":"",
"type":"Integer",
"order":20,
"action":"",
"length":null,
"source":"Int51Label",
"options":null,
"subtype":"23.73.13",
"required":false,
"type_code":"23.73.00",
"description":"Outside Height",
"subtype_code":"23.73.13",
"unit_of_measure":"in",
"type_description":"AHU - Indoor Central-Station",
"subtype_description":"Indoor Modular AHU"
},
"pressure_class":{
"date":"",
"type":"Select",
"order":8,
"action":"",
"length":null,
"source":"1000Char56Label",
"options":[
"Low",
"Medium"
],
"subtype":"23.73.13",
"required":false,
"type_code":"23.73.00",
"description":"Pressure Class",
"subtype_code":"23.73.13",
"unit_of_measure":"",
"type_description":"AHU - Indoor Central-Station",
"subtype_description":"Indoor Modular AHU"
},
"relief_damper?":{
"date":"",
"type":"Boolean",
"order":14,
"action":"",
"length":null,
"source":"Bool3Label",
"options":null,
"subtype":"23.73.13",
"required":false,
"type_code":"23.73.00",
"description":"Relief Damper?",
"subtype_code":"23.73.13",
"unit_of_measure":"",
"type_description":"AHU - Indoor Central-Station",
"subtype_description":"Indoor Modular AHU"
},
"cabinet_thickness":{
"date":"",
"type":"Decimal",
"order":21,
"action":"",
"length":2,
"source":"2dp3Label",
"options":null,
"subtype":"23.73.13",
"required":false,
"type_code":"23.73.00",
"description":"Cabinet Thickness",
"subtype_code":"23.73.13",
"unit_of_measure":"in",
"type_description":"AHU - Indoor Central-Station",
"subtype_description":"Indoor Modular AHU"
},
"design_return_air":{
"date":"",
"type":"Integer",
"order":3,
"action":"",
"length":null,
"source":"Int53Label",
"options":null,
"subtype":"23.73.13",
"required":false,
"type_code":"23.73.00",
"description":"Design Return Air",
"subtype_code":"23.73.13",
"unit_of_measure":"CFM",
"type_description":"AHU - Indoor Central-Station",
"subtype_description":"Indoor Modular AHU"
},
"design_supply_air":{
"date":"",
"type":"Integer",
"order":1,
"action":"",
"length":null,
"source":"Int2Label",
"options":null,
"subtype":"23.73.13",
"required":false,
"type_code":"23.73.00",
"description":"Design Supply Air",
"subtype_code":"23.73.13",
"unit_of_measure":"CFM",
"type_description":"AHU - Indoor Central-Station",
"subtype_description":"Indoor Modular AHU"
},
"coil_width_(total)":{
"date":"",
"type":"Decimal",
"order":17,
"action":"",
"length":2,
"source":"2dp1Label",
"options":null,
"subtype":"23.73.13",
"required":false,
"type_code":"23.73.00",
"description":"Coil Width (Total)",
"subtype_code":"23.73.13",
"unit_of_measure":"in",
"type_description":"AHU - Indoor Central-Station",
"subtype_description":"Indoor Modular AHU"
},
"departments_served":{
"date":"",
"type":"MultiSelect",
"order":22,
"action":"",
"length":null,
"source":"1000Char60Label",
"options":[
"OPERATING ROOMS",
"SURGICAL DEPARTMENT",
"STEM CELL/BONE MARROW/BMT",
"BURN UNIT",
"ICU/CCU",
"NEONATAL ICU",
"EMERGENCY DEPARTMENT/TRAUMA",
"CENTRAL STERILE/STERILE PROCESSING",
"PHARMACY",
"LABORATORY",
"IMAGING/RADIOLOGY/MRI/CT",
"NURSERY/OB/GYN/L&D",
"C-SECTION ROOM",
"PACU/RECOVERY",
"PEDIATRICS",
"RESPIRATORY CARE",
"CARDIOVASCULAR SERVICES",
"ENDOSCOPY/GI/BRONCHOSCOPY",
"UROLOGY/LITHOTRIPSY",
"MED/SURG PATIENT ROOMS",
"WOUND CARE/HYPERBARIC",
"ONCOLOGY",
"THERAPY",
"BEHAVIORAL/PSYCHIATRIC",
"ADMINISTRATION",
"GENERAL OFFICE SPACE",
"NURSING UNIT SUPPORT",
"PLANT OPERATIONS",
"MATERIAL MGMT",
"ENVIRONMENTAL SERVICES",
"INFORMATION SERVICES",
"FOOD SERVICE/DINING",
"BODY HOLD/MORGUE",
"QUALITY/CASE MGMT",
"EDUCATION/CONFERENCE",
"OTHER"
],
"subtype":"23.73.13",
"required":false,
"type_code":"23.73.00",
"description":"Departments Served",
"subtype_code":"23.73.13",
"unit_of_measure":"",
"type_description":"AHU - Indoor Central-Station",
"subtype_description":"Indoor Modular AHU"
},
"design_outside_air":{
"date":"",
"type":"Integer",
"order":2,
"action":"",
"length":null,
"source":"Int52Label",
"options":null,
"subtype":"23.73.13",
"required":false,
"type_code":"23.73.00",
"description":"Design Outside Air",
"subtype_code":"23.73.13",
"unit_of_measure":"CFM",
"type_description":"AHU - Indoor Central-Station",
"subtype_description":"Indoor Modular AHU"
},
"economizer_damper?":{
"date":"",
"type":"Boolean",
"order":13,
"action":"",
"length":null,
"source":"Bool2Label",
"options":null,
"subtype":"23.73.13",
"required":false,
"type_code":"23.73.00",
"description":"Economizer Damper?",
"subtype_code":"23.73.13",
"unit_of_measure":"",
"type_description":"AHU - Indoor Central-Station",
"subtype_description":"Indoor Modular AHU"
},
"supply_fan_control":{
"date":"",
"type":"Select",
"order":7,
"action":"",
"length":null,
"source":"1000Char55Label",
"options":[
"Constant Volume",
"Variable Volume"
],
"subtype":"23.73.13",
"required":false,
"type_code":"23.73.00",
"description":"Supply Fan Control",
"subtype_code":"23.73.13",
"unit_of_measure":"",
"type_description":"AHU - Indoor Central-Station",
"subtype_description":"Indoor Modular AHU"
},
"coil_height_(total)":{
"date":"",
"type":"Decimal",
"order":18,
"action":"",
"length":2,
"source":"2dp2Label",
"options":null,
"subtype":"23.73.13",
"required":false,
"type_code":"23.73.00",
"description":"Coil Height (Total)",
"subtype_code":"23.73.13",
"unit_of_measure":"in",
"type_description":"AHU - Indoor Central-Station",
"subtype_description":"Indoor Modular AHU"
},
"min_outdoor_damper?":{
"date":"",
"type":"Boolean",
"order":12,
"action":"",
"length":null,
"source":"Bool1Label",
"options":null,
"subtype":"23.73.13",
"required":false,
"type_code":"23.73.00",
"description":"Min Outdoor Damper?",
"subtype_code":"23.73.13",
"unit_of_measure":"",
"type_description":"AHU - Indoor Central-Station",
"subtype_description":"Indoor Modular AHU"
},
"integral_return_fan?":{
"date":"",
"type":"Boolean",
"order":9,
"action":"",
"length":null,
"source":"Bool4Label",
"options":null,
"subtype":"23.73.13",
"required":false,
"type_code":"23.73.00",
"description":"Integral Return Fan?",
"subtype_code":"23.73.13",
"unit_of_measure":"",
"type_description":"AHU - Indoor Central-Station",
"subtype_description":"Indoor Modular AHU"
},
"supply_fan_arrangement":{
"date":"",
"type":"Select",
"order":6,
"action":"",
"length":null,
"source":"1000Char52Label",
"options":[
"Single",
"Double",
"Array"
],
"subtype":"23.73.13",
"required":false,
"type_code":"23.73.00",
"description":"Supply Fan Arrangement",
"subtype_code":"23.73.13",
"unit_of_measure":"",
"type_description":"AHU - Indoor Central-Station",
"subtype_description":"Indoor Modular AHU"
},
"pressurization_fan_position":{
"date":"",
"type":"Select",
"order":11,
"action":"",
"length":null,
"source":"1000Char54Label",
"options":[
"Return",
"Mixed",
"Exhaust/Relief",
" Outside Air"
],
"subtype":"23.73.13",
"required":false,
"type_code":"23.73.00",
"description":"Pressurization Fan Position",
"subtype_code":"23.73.13",
"unit_of_measure":"",
"type_description":"AHU - Indoor Central-Station",
"subtype_description":"Indoor Modular AHU"
},
"design_leaving_air_temperature":{
"date":"",
"type":"Integer",
"order":4,
"action":"",
"length":null,
"source":"int54Label",
"options":null,
"subtype":"23.73.13",
"required":false,
"type_code":"23.73.00",
"description":"Design Leaving Air Temperature",
"subtype_code":"23.73.13",
"unit_of_measure":"° F",
"type_description":"AHU - Indoor Central-Station",
"subtype_description":"Indoor Modular AHU"
},
"return/relief/exhaust_fan_arrangement":{
"date":"",
"type":"Select",
"order":10,
"action":"",
"length":null,
"source":"1000Char53Label",
"options":[
"Single",
"Double",
"Array"
],
"subtype":"23.73.13",
"required":false,
"type_code":"23.73.00",
"description":"Return/Relief/Exhaust Fan Arrangement",
"subtype_code":"23.73.13",
"unit_of_measure":"",
"type_description":"AHU - Indoor Central-Station",
"subtype_description":"Indoor Modular AHU"
}
}
