Parse JSON from Web

[simonGIS]

I can hit this endpoint to request some JSON of World Cities, no authentication required.

I can use the 'Get Data - Web' from PowerBI Desktop, and I have tried a few variations, but it would appear that it is expecting a CSV, which I do not have available to me.

What would be the right way to get the Cities from the features in the JSON into corresponding rows in a table?

I would want the ability to refresh the data from the endpoint, so hitting the URL is important.

[v-eachen-msft · Accepted solution]

Hi @simonGIS ,

You could use "Json.Document" to replace "Csv.Document" and remove other extra codes in the "Source".

Here is the document for your reference.
https://community.powerbi.com/t5/Desktop/rest-api-json-issue-column-issue/td-p/556282

Or you could import it with ODBC.

https://zappysys.com/blog/howto-import-json-rest-api-power-bi/

[simonGIS]

Thanks.

So, started fresh. 

• Power Query Editor, new query
• Added JSON from URL
• Applied Steps, Changed Type, Open Advanced Editor
• Removed the Csv.Document( and 2nd param
• Changed to JSON type
• Drilled down into features
• Tried converting this to table 

From here I have two nested properties that I need to flatten into a table, the attributes and geometry

End goal table:

CITY_NAME	POP	POP_RANK	POP_CLASS	X	Y
London	70000	3	700 - 900	12345.1	12345.2
..	..	..	..	..	..

What would be the next steps to attempt to flatten out the JSON to get the above desired table?