JSON data to excel

[JannetteChristie]

How can I convert the following JSON data to an excel table.

{"primary":{"name":"5SV05","weight":"0","price":"0.00","efficiency":0.6490910099999995,"efficiency_graph":[{"flow":1.1804999999999999,"efficiency":66},{"flow":1.2986666666666666,"efficiency":67},{"flow":1.4166666666666667,"efficiency":68},{"flow":1.5346666666666666,"efficiency":69},{"flow":1.6528333333333334,"efficiency":69},{"flow":1.7708333333333333,"efficiency":68},{"flow":1.8888333333333334,"efficiency":67},{"flow":2.007,"efficiency":65},{"flow":2.125,"efficiency":62},{"flow":2.2430000000000003,"efficiency":57.99999999999999},{"flow":2.3611666666666666,"efficiency":53}],"max_head_pressure":23.4576208,"tolerance":0.14942341920000007,"tolerance_ok":true,"low_tolerance_finder":0.14942341920000007,"head_pressure_graph":[{"flow":1.1804999999999999,"head":3.2536000000000005},{"flow":1.2986666666666666,"head":3.15364},{"flow":1.4166666666666667,"head":3.0429000000000004},{"flow":1.5346666666666666,"head":2.91942},{"flow":1.6528333333333334,"head":2.78222},{"flow":1.7708333333333333,"head":2.63228},{"flow":1.8888333333333334,"head":2.4676400000000003},{"flow":2.007,"head":2.2883000000000004},{"flow":2.125,"head":2.09328},{"flow":2.2430000000000003,"head":1.8825800000000001},{"flow":2.3611666666666666,"head":1.6552200000000001}],"power_consumption":0.7013503999999999,"power_consumption_graph":[{"flow":1.1804999999999999,"power":0.58},{"flow":1.2986666666666666,"power":0.6},{"flow":1.4166666666666667,"power":0.63},{"flow":1.5346666666666666,"power":0.65},{"flow":1.6528333333333334,"power":0.67},{"flow":1.7708333333333333,"power":0.68},{"flow":1.8888333333333334,"power":0.69},{"flow":2.007,"power":0.7},{"flow":2.125,"power":0.71},{"flow":2.2430000000000003,"power":0.71},{"flow":2.3611666666666666,"power":0.71}],"curves":[{"flow_curve_name":"Efficiency","unit":"%","a0":"0.045309","a1":"2.47593","a2":"-0.041522","a3":"0.000409518","a4":"-0.00000214","a5":"0.00000000409","a6":"0","a7":"0","a8":"0","a9":"0"},{"flow_curve_name":"Max Head Pressure","unit":"kPa","a0":"38.0236","a1":"-0.015448","a2":"-0.000543193","a3":"-0.00000283","a4":"0","a5":"0","a6":"0","a7":"0","a8":"0","a9":"0"},{"flow_curve_name":"Net Positive Suction Head","unit":"kPa","a0":"141.667","a1":"0.488936","a2":"-0.0107766","a3":"0.000195426","a4":"0.0000000249","a5":"0.00000000000238","a6":"1.79e-16","a7":"2.79e-20","a8":"-5.66e-23","a9":"8.54e-26"},{"flow_curve_name":"Power Consumption","unit":"kW","a0":"0.223406","a1":"0.00601015","a2":"-0.00000979","a3":"-0.0000000592","a4":"0","a5":"0","a6":"0","a7":"0","a8":"0","a9":"0"}],"consumption_score":36,"model":"MSV1-0505-LHM","power_supply":"230\/1\/50","dwg_link":"https:\/\/www.dutypoint.com\/systems\/products","pdf_link":"https:\/\/www.dutypoint.com\/systems\/products","model_link":"https:\/\/www.dutypoint.com\/systems\/products\/5-Cold-Water-Booster-Sets\/32-MSV"},"alternative_3":{"name":"5SV06","weight":"0","price":"0.00","efficiency":0.6560092300000006,"efficiency_graph":[{"flow":1.1804999999999999,"efficiency":66},{"flow":1.2986666666666666,"efficiency":67},{"flow":1.4166666666666667,"efficiency":69},{"flow":1.5346666666666666,"efficiency":69},{"flow":1.6528333333333334,"efficiency":69},{"flow":1.7708333333333333,"efficiency":69},{"flow":1.8888333333333334,"efficiency":67},{"flow":2.007,"efficiency":65},{"flow":2.125,"efficiency":63},{"flow":2.2430000000000003,"efficiency":59},{"flow":2.3611666666666666,"efficiency":55.00000000000001}],"max_head_pressure":28.0606848,"tolerance":0.3749735552000001,"tolerance_ok":true,"low_tolerance_finder":0.3749735552000001,"head_pressure_graph":[{"flow":1.1804999999999999,"head":3.9160800000000004},{"flow":1.2986666666666666,"head":3.79554},{"flow":1.4166666666666667,"head":3.6603000000000003},{"flow":1.5346666666666666,"head":3.50938},{"flow":1.6528333333333334,"head":3.3418},{"flow":1.7708333333333333,"head":3.15756},{"flow":1.8888333333333334,"head":2.95568},{"flow":2.007,"head":2.7361600000000004},{"flow":2.125,"head":2.49802},{"flow":2.2430000000000003,"head":2.24028},{"flow":2.3611666666666666,"head":1.9629400000000001}],"power_consumption":0.8371679999999999,"power_consumption_graph":[{"flow":1.1804999999999999,"power":0.7},{"flow":1.2986666666666666,"power":0.73},{"flow":1.4166666666666667,"power":0.76},{"flow":1.5346666666666666,"power":0.78},{"flow":1.6528333333333334,"power":0.8},{"flow":1.7708333333333333,"power":0.82},{"flow":1.8888333333333334,"power":0.83},{"flow":2.007,"power":0.84},{"flow":2.125,"power":0.84},{"flow":2.2430000000000003,"power":0.84},{"flow":2.3611666666666666,"power":0.84}],"curves":[{"flow_curve_name":"Efficiency","unit":"%","a0":"0.032779","a1":"2.32411","a2":"-0.035586","a3":"0.00032329","a4":"-0.00000159","a5":"0.00000000282","a6":"0","a7":"0","a8":"0","a9":"0"},{"flow_curve_name":"Max Head Pressure","unit":"kPa","a0":"45.3426","a1":"-0.00364176","a2":"-0.000806185","a3":"-0.00000303","a4":"0","a5":"0","a6":"0","a7":"0","a8":"0","a9":"0"},{"flow_curve_name":"Net Positive Suction Head","unit":"kPa","a0":"141.667","a1":"0.488936","a2":"-0.0107766","a3":"0.000195426","a4":"0.0000000249","a5":"0.00000000000238","a6":"1.79e-16","a7":"2.79e-20","a8":"-5.66e-23","a9":"8.54e-26"},{"flow_curve_name":"Power Consumption","unit":"kW","a0":"0.29529","a1":"0.00644885","a2":"-0.00000267","a3":"-0.000000112","a4":"0","a5":"0","a6":"0","a7":"0","a8":"0","a9":"0"}],"consumption_score":35,"model":"MSV1-0506-LHM","power_supply":"230\/1\/50","dwg_link":"https:\/\/www.dutypoint.com\/systems\/products","pdf_link":"https:\/\/www.dutypoint.com\/systems\/products","model_link":"https:\/\/www.dutypoint.com\/systems\/products\/5-Cold-Water-Booster-Sets\/32-MSV"}}

 

[JannetteChristie]

When I run Powerquery from Web, it looks like this:

[TABLE="width: 149"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]primary[/TD]
[TD][Record][/TD]
[/TR]
[TR]
[TD]alternative_3[/TD]
[TD][Record][/TD]
[/TR]
</tbody>[/TABLE]

 

[Russell Hauf]

Hi Jannette,

I can try to help you with this later (sorry, quite busy right now), but I suggest that you post this into the Power BI forum (https://www.mrexcel.com/forum/power-bi/) instead of the Excel forum. Let me know if you don't get an answer over there.

 

[JannetteChristie]

Thanks for your reply - someone has answered and helped on the BI forum. I have however left another query if you are available to help https://www.mrexcel.com/forum/power-bi/1057323-power-query.html#post5077667

 

[tiredofit]

Thanks for your reply - someone has answered and helped on the BI forum. I have however left another query if you are available to help https://www.mrexcel.com/forum/power-bi/1057323-power-query.html#post5077667

Does this help?

https://github.com/VBA-tools/VBA-JSON