Excel 365 Get Data from web-based Json API issue

Question

I am trying to Get & Transform data from a web API.

When I go to the URL through a browser I can view the data.

I copy the URL. Open Excel 365, click get data ...from web and paste the URL.

It looks like Excel is recognizing that it is a Json, because power query has Json.Document.....

However, it is not letting me view the actual data, each line only has "Record"

When I try Close & Load it loads [Record] into the rows in column A.

Any ideas on what is going on?

Kind Regards,

Dom

Answer 1

That's typically how data gets loaded in Power Query. Record's or Lists can be expanded to get the data inside them.

There are 2 things that you will want to based on what you want.

1. Click on any one record to see the data in just that one Record
2. Click on the Expand button on the column header to see data of all the Records in their respective rows. Please check out this answer

If you're not able to figure it out, please post an image.