Importing data from a nested JSON file to Excel

You will need to coax out the values. I added a column to your JSON import with this formula:

 

[Countries]{0}[CountryName]

 

 So first it pulls the Countries field as is, but then gets the first record {0} - Power Query starts at zero. At that point it is a record that looks like this

So then I finally added the [CountryName] field and it returns Peru for the first row, Malawi for the second, etc.

To get the name of the Entities, I added this to a new column:

 

[Entities]{0}[Name]

 

If you wanted the Entities Accreditation Date, it would be this:

 

[Entities]{0}[AccreditationDate]

 

So just add as many columns as you need to get as much data from those as you need. Just remember to set the data types when done lasse0hlsen 

 

EDIT: if you want EVERY field from all of these fields (entities, countries, etc) then use this function:

Table.FromRecords([Entities]))

 Now you will have a table you can expand into many columns.

Then you can hit the expand buttin in the green box above and expand multiple or all fields at once.

 

Hello lasse0hlsen 

 

another approach could be to transform your list-columns

 

in order that only the frist list items it used and then expand the record. To achive this you have to add a new step at the bottom (right click on your last step and choose "Insert Step after" and input this formula... be aware that you have to replay my "PreviousStep with the name of your previous step

= Table.TransformColumns
    (
        PreviousStep, 
        {
            {
                "Countries",
                each _{0}
            },
            {
                "Entities",
                each _{0}
            },
            {
                "Disbursements",
                each _{0}
            },
            {
                "Funding",
                each _{0}
            },
            {
                "ResultAreas",
                each _{0}
            }
        }
    )

after that you can expand only the first record ... so no double lines anymore

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy