How do you use GeoJSON in Power BI? Keeps deleting half the coordinate pair!

[jsaue003]

I got my GeoJSON data from Airbnb's public data. Feel free to get a copy yourself, completely safe http://insideairbnb.com/get-the-data.html. I chose the following Amsterdam geojson file:

Amsterdam

neighbourhoods.geojson

Here is a video demonstration of the issue (don't worry--no sound). You can CLEARLY see when I drill in there is a coordinate pair, but when I expand all the rows, there's just one coordinate. It's deleting everything.

For those who can't watch the video:

I loaded it into PowerBI with GetData and chose the option to convert to table:

Here are where things get truly confusing....

As you can see, there are no split arrows next to these values. There are two columns, and a header row 'type' and 'FeatureCollection'. I tried first filterout out type from the Name column to show only features and its associated List. Then, the expand arrows appeared

From here, I expand to new rows repeatedly until everything has expanded:

The problem exists here. This data is BAD! This is supposed to be neighborhood data of Amserdam. There's only one coordinate field, when the data in its raw form has two. I found out there is two by drilling down in another Power BI session without any of these previous steps.

I drilled down by doing the following starting from my second image above:

1. Click list, series of records appear
2. Click Record 1
3. Click Geometry
4. Click List
5. Click List
6. Click List
7. Click first List
8. Finally, we see the coordinates!

So we KNOW there are two coordinates, but in my very last image before this one, we only see the -97.89 coordinate! The other is GONE!

Here is my question.... How do you actually work with this GeoJSON data in PowerBI properly, and graph it on a map? 

[v-yuta-msft · Accepted solution]

@jsaue003 ,

The solution which can meet your requirement is to pivot columns. So you can create a custom column which contains values "Longitude", “Latitude”, "Depth" and also create an index column group by every point(Need some tricky M code here). Finally you can pivot the new column based on the index column. For more details, you can refer to this blog:

https://chris.koester.io/index.php/2016/04/15/json-array-in-power-query/

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.