Forum Discussion
Importing data from a nested JSON file to Excel
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
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
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
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,
LasseHello 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