Importing data from a nested JSON file to Excel

[lasse0hlsen]

Hey guys,

I am desperately trying to parse the data from a nested JSON file (that has many records and lists in it) to Excel. I managed to view the basic information about the 158 projects that are contained in the JSON file by transforming the list to a table and then expanding the other columns. The problem occured when I tried to expand the columns "Countries", "Entities", "Disbursements", "Funding" and "ResultAreas", the values in the rows to the left of these columns are copied down - when I expanded everything I got 109256 rows. Such a double counting must not be, because then I can no longer work with the values in the dataset. 

Is there a way to extract the values from all columns so that the dataset still has 158 rows (which corresponds to the number of projects)? Can the values from the columns "Countries", "Entities", "Disbursements", "Funding" and "ResultAreas" simply be expanded to new columns instead of rows (to avoid duplication of the data)? 

I would be very happy if you could help me! Thanks in advance!

Cheers,
Lasse

PS: I already checked out the forum post (Nested JSON and never end Records), but unfortunately the advice given there didn't help me to solve the above-mentioned problem. 

[Jimmy801]

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

[lasse0hlsen]

Hey Jimmy,

first of all I want to thank you for taking the time to look into my problem. The code you provided looks very promising indeed and I think that we came much closer to finding a solution here. But is there possibly a way that would allow me to expand not only the first record of a list to a new column, but all records instead (without doubling the lines)? Let's take the "Countries" column as an example: Project "FP152" has a list with 42 records in the "Countries" column - this is obviously a multi-country project and for my analysis I would also need the information provided in the other records, not only the first records of each list (otherwise important information gets lost). Hence, it would be ideal if there was a way to extract all records in all lists contained in the JSON file to new columns and at the same time keeping the information, that was expanded to new columns, in the same row than the project it belongs to. Do you understand what I mean? I am not sure if I managed to explain it correctly...

Cheers,

Lasse

[Jimmy801]

Hello @lasse0hlsen 

but that would lead into an incredible amount of columns. Would be that suit for your? or would it better to aggregate the information of multiple record-fields  into  one column ... meaning the column Country could then look like Italy, Germany, Austria etc.... instead of having Country1 with Italy, Country 2 with Germany etc.

BR

Jimmy

[lasse0hlsen]

Hey Jimmy,

thanks for your prompt reply! I fully agree with you that for a few multi-country projects it doesn't really make sense to expand 42 (or more) countries to new columns. Aggregating these countries for multi-country projects into a single column with comma separated values in the its cells would be absolutely sufficient for my analysis. Do you know how this could be done step-by-step in PQ?

Best,
Lasse

[Jimmy801 · Accepted solution]

Hello @lasse0hlsen 

instead of this

            {
                "Disbursements",
                each _{0}
            }

you have to maintain this for every column. 

            {
                "Disbursements",
                each Table.FromColumns(List.Transform(Table.ToColumns(Table.FromRecords(_)), each{Text.Combine(List.Transform(_, each try  Text.From(_) otherwise "" ),", ")}),Table.ColumnNames(Table.FromRecords(_)))
            }

Be aware that with this codes sub-lists or records will be converted to a space only. So you will loose this information. This transformation will transfrom a list of records to a table with all rows aggregated to one row. You can afterwards easily expand the table. Here a screenshot

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

[edhans]

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.