Select JSON records as Excel table via Power Query

[Blue1971]

I have a URL that points to a sample server that returns JSON records:
https://sampleserver6.arcgisonline....pServer/0/query?where=1=1&outFields=*&f=pjson

I want to use the JSON records in Excel 2016 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?

Thank you.

 

[smozgur]

Delete second step, Changed Type, then select Source and change the function with the following:

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

Or open Advanced Editor, delete everything and paste the following:

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

You'll get the following:

I think you can continue from here by knowing which data you need, but keep posting if you need more help - also explaining which data / how you need to transform the source data. There are many Power Query experts in MrExcel Message Board who'd try to help.

 

[Blue1971]

Delete second step, Changed Type, then select Source and change the function with the following:

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

Or open Advanced Editor, delete everything and paste the following:

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

You'll get the following:

View attachment 18311

I think you can continue from here by knowing which data you need, but keep posting if you need more help - also explaining which data / how you need to transform the source data. There are many Power Query experts in MrExcel Message Board who'd try to help.

Thanks. That helped me retrieve my data.

-Ben