cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jsaue003 Frequent Visitor
Frequent Visitor

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

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? 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

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

@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.

1 REPLY 1
Highlighted
Community Support Team
Community Support Team

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

@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.

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 42 members 945 guests
Please welcome our newest community members: