Forum Discussion
Expand all .json records on import
Community SupportHi Anonymous ,
You can refer to the case , as cwebb' suggestion:
If you want all the data from the JSON file in a single table in a single query, you can click the "Expand" icon (which looks like two arrows, one pointing left, one pointing right) in the column header. You can also write some M code to automatically expand all the expandable columns in a table, similar to what I wrote about here: https://blog.crossjoin.co.uk/2014/05/21/expanding-all-columns-in-a-table-in-power-query/
Or could you please show us what the .json records and your desired output look like for further analysis?
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Amy,
Thanks for your response. Here is a portion of my code. The three values below in red font are also column headers I see in Power Query containing rows with "Record" that are links to lower level values that may or may not be useful.
<?xml version="1.0" encoding="ISO-8859-1"?>
<response><result><parent/><shadow>false</shadow><forecast_cost>0</ forecast_cost><watch_list/><phase_type>waterfall</phase_type><wbs/><upon_reject>cancel</upon_reject><sys_updated_on>2019-10-18 16:16:53</sys_updated_on><forecast_cost>0</forecast_cost><discount_rate>0</discount_rate><time_card>project_task</time_card><approval_history/><rollup>false</rollup><skills/><number>0001004</number><score>4.67</score><program/><start_date>2019-09-25 13:00:00</start_date><u_all_cis>false</u_all_cis><forecast>0</forecast><cost>5</cost><estimate>0</estimate><state>3</state><sys_created_by>san</sys_created_by><goals/><knowledge>false</knowledge><order/><phase>closing</phase><duration/><goal/><budget>0</budget><cmdb_ci/><dependency/><contract/><impact>1</impact><milestone>false</milestone><effort/><active>false</active><work_notes_list/><priority>4</priority><sys_domain_path>/</sys_domain_path><business_unit/><demand><link>https://xxxx.service-now.com/api/now/table/demand/be</link><value> be1a </value></demand
For example, when I click on the first "Record" in the Program column, the value I see looks something like this: