Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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:
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?
Solved! Go to Solution.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |