'Importing and parsing JSON data from a URL in Power BI
I'm trying to import JSON data from a URL (https://www.mapping.cityoflondon.gov.uk/arcgis/rest/services/COMPASS_Epping/MapServer/9?f=pjson) into Power BI.
I click on Get Data -> URL -> put URL into "Basic" and get the following: screenshot
The data imported into Power BI is not usable in this form. How do we parse the data as we import from the URL?
Solution 1:[1]
By looking at the screenshot you shared, it seems after using the web connector, Power Query is reading the data as a CSV file.
You can
Go to Source step in Power Query
In formula bar, replace CSV with JSON. Formula for Source step should look like
= Json.Document(Web.Contents(URL))
Now Power BI will read it correctly as a JSON List. You can use the Into Table option to convert the list to table and make it more usable.
Now I am not sure how do you want to see the data and you will be using it, but the above steps should make the data usable enough to perform any further transformations further on it and make use of it. example
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Bipin Lala |