How to expand all lists (from JSON) in Excel Power Query?

Question

I simply want to expand every list to reflect a regular table of columns and rows. Using the expand function, the result is the second image, which exceeds a million rows when all columns are expanded.

There must be a simple solution. Should my JSON file be reformatted? The way it is formatted now makes the most sense, and logically it should look like how it does in Power Query (first image), but shouldn't it be equally as simple to expand it into a regular table? By regular I mean no nulls, every row has a value for every column, like in the JSON.

Please help?

Thanks

Answer 1

I'm not an expert on the topic and you didn't show how your JSON was formatted, but if all you want to have is a list of records I think you would just want to have them formatted in the JSON like this:

{"Name":"Item Name", "Weight in store":12.3, "Minimum Purchase":12, "Purchase Price":19.99, "Category":"That Thing", "Location":"That Place"},
{"Name":"Other Name", "Weight in store":32.1, "Minimum Purchase":16, "Purchase Price":29.99, "Category":"Other Thing", "Location":"Other Place"}

In my test PQ reads this as a list of records that will expand as you would expect.