Using GetData on JSON file only gives two columns without data

[Tony Girgenti]

Hello,

I'm using Excel 2016.  I tried to use the Data ribbon to Get Data\From File\From JSON and imported a file that looks like this:

{
 "categories": [
  {
   "name": "Cheap & Cool Gadgets",
   "url": "http://www.chinavasion.com/china/wholesale/Electronic_Gadgets/",
   "image": "http://cdn.chv.me/images/_zSqANqA.jpg",
   "subcategories": [
    {
     "name": "Cool Gadgets",
     "url": "http://www.chinavasion.com/china/wholesale/Electronic_Gadgets/Cool_Gadgets/",
     "image": "http://cdn.chv.me/images/zASqnkuu.jpg"
    },
    {
     "name": "Camera Equipment / Accessories",
     "url": "http://www.chinavasion.com/china/wholesale/Digital_Cameras-Camcorders/Camera_Accessories/",
     "image": "http://cdn.chv.me/images/3qsmbvZc.jpg"
    },
    {
     "name": "Laser Gadgets & Measuring Tools",
     "url": "http://www.chinavasion.com/china/wholesale/Electronic_Gadgets/Laser_Gadgets/",
     "image": "http://cdn.chv.me/images/TWvnHhzt.jpg"
    },
    {
     "name": "Special LED Lights",
     "url": "http://www.chinavasion.com/china/wholesale/LED_Lights/Special_LED_Lights/",
     "image": "http://cdn.chv.me/images/rVbp3Cxi.jpg"
    }
   ]
  }
 ]
}

After the Query Editor opens, I click Convert\Into Table and then I click Close and Load.

It shows this screen:

Does anyone know how to get it to show the actual data?

I validated the json data here: https://jsonformatter.curiousconcept.com/

Thanks,

Tony

[#2]

Hi Tony,

JSON connector doesn't do all transformations for you, you need to add them manually. See, for example, this conversation https://techcommunity.microsoft.com/t5/Get-and-Transform-Data/parse-two-level-JSON-data/m-p/186947#M...

[#3]

Hi, have you tried to analysed your json? you can use this tool to do that. JSON Formatter 

[#4]

Jimmi, Power Query is enough for me