So just add as many columns as you need to get as much data from those as you need. If you wanted the Entities Accreditation Date, it would be this: To get the name of the Entities, I added this to a new column: So then I finally added the field and it returns Peru for the first row, Malawi for the second, etc. At that point it is a record that looks like this So first it pulls the Countries field as is, but then gets the first record - Power Query starts at zero. I added a column to your JSON import with this formula: Do you understand what I mean? I am not sure if I managed to explain it correctly.
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. 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). The code you provided looks very promising indeed and I think that we came much closer to finding a solution here. 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.įirst of all I want to thank you for taking the time to look into my problem. I would be very happy if you could help me! Thanks in advance! 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)?
Such a double counting must not be, because then I can no longer work with the values in the dataset. 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. 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. I am desperately trying to parse the data from a nested JSON file (that has many records and lists in it) to Excel.
0 kommentar(er)
