How to import JSON data into EXCEL columns?

Question

I have an excel spreadsheet like following:

with a first column the name of something, and then two columns "date" and "owner". I also have the following JSON file which I want to import via "Get Data" -> "From File" -> "From JSON" to populate the empty columns automatically from the data in the JSON file.

JSON file content:

{
    "TEST-1": {
        "date": 2,
        "owner": "Alfred"
    },
    "TEST-2": {
        "date": 4,
        "owner": "Berta"
    },
    "TEST-3": {
        "date": 6,
        "owner": "Cesar"
    },
    "TEST-42": {
        "date": 84,
        "owner": "Dave"
    },
}

in the end I want to have the excel table look like following:

But I have no clue on how to do this with this "Power Query". Any ideas? Tutorials? Simple explanations?

Answer 1

You can achieve it in 3 parts.

A. Part 1

1. Select the range. Go to Data -> Get Data -> From Table/Range. Select my table has headers. Click OK.
2. Power query editor opens. Close and Save.

B. Part 2

1. Go to Data -> Get Data -> From File -> From JSON. This will be the output

2. Go to Convert -> To Into Table

3. Click on the arrow next to Value. Select the items to show. Click OK.

4. The image below will be the result. Right click on the column header, select Rename to change the column name. This will be the output. Close and save.

C. Part 3

1. Go to Data -> Get Data -> Combine Queries -> Merge
2. When Power Query Editor opens, set the first table, then the data from the json file as the second table. Select the columns from both tables that will be the basis of the merge. On Join Kind listbox, select "Left outer (all from first, matching from second)"

3. Expand the arrow from the last column. Select the columns whose values must be displayed, click OK.

This will be the result.

4. Remove unnecessary columns and rename (if necessary) others.

5. Close and save.