Select JSON records as Excel table via Power Query

Question

I have a URL that points to a sample server that returns JSON records:

https://sampleserver6.arcgisonline.com/arcgis/rest/services/LocalGovernment/Events/MapServer/0/query?where=1%3D1&outFields=*&f=pjson

I want to use the JSON records in Excel as a table.

---

I've attempted to do this by connecting to the URL via Excel Power Query:

However, it seems like Power Query is treating the data as CSV records, not as JSON records.

So the data is not coming into Power Query as a proper table.

---

How can I get Power Query to select the JSON records as a table in Excel?

Answer 1

I just loaded the data as shown in your post, then used Home>Advanced Editor to change the query to this:

let
    Source = Json.Document(Web.Contents("https://sampleserver6.arcgisonline.com/arcgis/rest/services/LocalGovernment/Events/MapServer/0/query?where=1%3D1&outFields=*&f=pjson"))
in
    Source

So it showed this:

After a little inspection of your link, it seems the features are what you want to return.

So, I clicked on the features List shown in the image above and got this:

I right-clicked the column and chose "To Table" and configured it with Delimiter None and show extra columns as errors.

Next, I renamed the column "Features" then clicked the double-arrow to expand each record:

I then do the same thing to Features.Attributes and Features.Geometry to expand all the feature columns.

I then click Home>Close & Load and have the data in my workbook:

Answer 2

Indeed at first Power BI tries to open the file as csv. Let it go and press "transform".

You'll have two steps generated, just delete "change type" and select cogwheel icon of "source" to change it's settings.

Here you can change file type and open it as JSON

Now you can expand the file as you need