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,
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
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