Shape in Geojson

[torkildv]

Hi,

maybe a stupid question, but: quoting https://doc.arcgis.com/en/power-bi/get-started/prepare-your-data.htm

"EsriJSON—If your data contains location information formatted using EsriJSON, it should appear in a Shape column. Drag the Shape field value into the Location field well to draw EsriJSON locations on the map. EsriJSON supports points, lines, and polygons." (my highlight)

Anyone who knows how to ma

[TimMinter]

@AnneTetley  @RudolfdeMunnik - good info, thanks!

I've tried making EsriJSON format files with FME's Data Interoperability extension for ArcGIS Pro and bounced off without success.  Doesn't mean it doesn't work, just that I've not yet made it work or proven that it doesn't.  I won't engage Esri support for this unless a boss shows up with a big stick and proclaims that it's ok if I don't proceed on other assignments for a while.

Also, about all I know right now of Power Automate is that there is a space between "Power" and "Automate", which I suppose is the third step after my usual starting point of complete ignorance.  My customers and I deal with confidential health and other information.  I've inquired internally to discover if Power Automate is authorized for use with our data (i.e. does not transport or process our data outside of our domain and transports and processes our data within our domain in an approved and secure manner), and I'll proceed with trying the suggested approaches after that light goes green.

[RudolfdeMunnik]

The advantage of the method is that you now have the format of the EsriJson file (CSV). I am sure FME can write to this format with some configuration. 👍

[ChelseaRozek]

Hi @TimMinter and everyone, has anyone figured out how to get the EsriJSON to work in PowerBI without Power Automate? I also don't see a SHAPE field, only "rings" for my polygon layer. I'd like to just work with a JSON export from Pro, so hoping I don't need to jump through more hoops with portal and automate.

[TimMinter]

Hi @ChelseaRozek, no, no go.  One approach I haven't looked at very hard is to use ArcGIS Pro Data Interoperability extension to dump a TopoJSON.  I gave it a few tries, but couldn't get the Power BI visualization thingy to recognize the file.  I appeared to be following the instructions correctly, and it was beginning to smell like a bug, so I ran away.  If you find a way, do post back.

edit:  What I didn't say was that the TopoJSON approach looks fairly stale in the PowerBI environment.  There's a website that will convert for you, which is great for non-confidential data.  I think I found the source code in Github and decided not to pursue going local with it.  Also, one thought I had was to have a look at the SEDF to see if I could use it to squeeze out a CSV that PowerBI recognizes.  I might poke at that one day soon.

[Kevin_Peel]

Likewise no luck either.  The only luck I've had with boundaries in the tool is using a CSV of Census boundaries and then choosing the correct Census boundary type when the visual prompts for me to pick a geography.  Unfortunately they do not yet have the latest boundaries for Canada, so it was a dead end for me.

A majority of the time I've used the "Shape map visual" plugin in PowerBI for most of my mapping needs and use MapShaper to convert SHP to TopoJSON for the Shape map visual.  MapShaper is free and processes on the client side.  You can then use the relationship tool in Power BI to connect the unique ID in the TopoJSON with the other tabular data you already have in your project.

In this case I needed the ArcGIS visual because I wanted to have some points of interest on the map as well, which Shape map doesn't allow for.

[RudolfdeMunnik]

Hi @Kevin_Peel, I have done Mapshaper, it works, but you don't get basemaps or the ability to add background reference layers ( and it's not Esri). I posted the basic and advanced procedures for the ArcGIS for Power Automate earlier in the stream. The biggest takeaway from that procedure is the format for the EsriJSON file in a CSV format. You can use other tools e.g. FME to generate it.

I have been playing around with the ArcGIS REST Api with PowerQuery in Power BI to extract the EsriJSON format with success (without Power Automate). I got it to work with publicly shared feature services, because generating tokens is a challenge. I need some time to post the procedure. Will get back to you soon.

[Kevin_Peel]

Hey Rudolfde - my use of Mapshaper was to get around the issue of creating a map in the "Shape map" visual as an alternate to the ArcGIS for PowerBI plugin.  I didn't want Tim (or others) to think that the Interop extension was the only way to go about it if they were also stuck.

I'm in the same boat as you with the API, where public services aren't a big issue to bring in but currently I need to bring in a private service from AGOL for development work.

Look forward to seeing your procedures!

[TaylorWillow]

Hi @RudolfdeMunnik, this info would be a life saver. I'm attempting to load polyline data into Power BI from a publicly shared feature service.
I've successfully gotten the raw data into Power BI using the ArcGIS REST API, and I believe it's technically in EsriJSON format according to this documentation: https://developers.arcgis.com/rest/services-reference/enterprise/output-formats.htm
I've been playing with the data in PowerQuery to massage the geometry into something the Location field in ArcGIS Maps for Power BI will recognize, but each polyline is a list of lists, so I'm having trouble concatenating them into a text field (or otherwise getting Power BI to stop parsing the data as lists). As-is, dropping the Geometry field into Location gives me a point in a town called List... amusing. Thank you for any pointers!

[NasreddineD]

Hi @Rudolfde_Munnik and @Kevin_Peel, have you found a solution to do the same thing but with private layers and not public?

Thanks very much.

[TaylorWillow]

@RudolfdeMunnik and @TimMinter I've gotten slightly farther in the process but now stumped again. An ArcGIS REST API query on a polyline feature dataset (what I'm working with) returns the feature class metadata, then the features, with each feature having attributes and geometry. The format you need to drop into the Location field in ArcGIS for Power BI is as follows (for polylines):
{"geometryType":"esriGeometryPolyline","spatialReference":{"wkid":102100,"latestWkid":3857},"paths":[[[x,y],[x,y],[x,y]]]}
Other geometry types can be found here: https://developers.arcgis.com/documentation/common-data-types/geometry-objects.htm
Crucially, the geometryType and spatialReference need to be included in the JSON for EACH feature - when using the REST API, this information is only represented once in the metadata at the beginning. This can be added using a column calculation.
The real stumper for me now is this: for the Location field to recognize this and represent it on the map, the contents of the field have to be raw JSON as a text string, as represented above. Power BI's JSON parser, however, nests the contents of the geometry field as lists of coordinates inside a list inside a list (yes, twice) inside a record. I'm coming up short on how to convert a Power BI record back into raw text. Now, you could also import the source as text instead of JSON, but then you run up against some incredibly fiddly column splitting, re-concatenation, and column calculation that can also be prohibitively slow with large datasets. 
I'm very new to Power Query so that may be my limiting factor vs. a more seasoned pro.