Trouble loading JSON to Excel

Question

I have a JSON file that I would like to import into Excel
I have Office 365
I am using the following:
​Use Excel's Get &amp; Transform (Power Query) experience to connect to a JSON file.
Click the Data tab, then Get Data &gt; From File &gt; From JSON.
Browse to your JSON file location, select it, and click Open.
Once the Query Editor has loaded your data, click Convert &gt; Into Table, then Close &amp; Load.
&nbsp;
I only receive two rows in my excel spreadsheet but there is quite a bit of data.
&nbsp;
Help appreciated.
&nbsp;
-JP

tim_leitch · Answer

johndre&nbsp;Your answer only repeats the process that's shown on Microsoft for the process, which is what the first questioner shows he has done but then has link to pay a $59.90 licence for another XML converter.&nbsp;What is needed in someone with greater knowledge Microsoft Tech to give an answer to allow the Microsoft process to work correctly.&nbsp;I also have the same problem getting just data column headings, after clicking on the Query after conversion and downloading 1.5 GB of records but nothing showing again.&nbsp; I've done this twice and still no success.Someone on Microsoft Tech please help with a real answer ? ? ?

capnkronic · Answer

Depending on the json object, you may need to step into it to get to the records and then convert your table from the records in the json string.The two rows you are returning now, is the data what you expect to see or possibly table names?

jglaab · Answer

palmer520325&nbsp;I was having the same issue and i was able to fix this by highlighting the [Record] column in the power query editor and selecting the "expand" option under the "Structured Column" tab:&nbsp;Hope this helps others in the future too!&nbsp;&nbsp;

Forum Discussion

Trouble loading JSON to Excel

Share

Resources