Forum Discussion
Parse JSON from Web
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.
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/556282Or you could import it with ODBC.
https://zappysys.com/blog/howto-import-json-rest-api-power-bi/
2 Replies
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/556282Or you could import it with ODBC.
https://zappysys.com/blog/howto-import-json-rest-api-power-bi/
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?
