Forum Discussion

lasse0hlsen's avatar
lasse0hlsen
Frequent Visitor
5 years ago
Solved

Importing data from a nested JSON file to Excel

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 a...
Need Help
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    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's avatar
edhans
Community Champion
5 years ago

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.