topic Re: Shape in Geojson in ArcGIS for Power BI Questions

Shape in Geojson

torkildv — Thu, 08 Sep 2022 15:32:44 GMT

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

Re: Shape in Geojson

Anonymous User — Thu, 08 Sep 2022 15:54:41 GMT

We do not support GeoJSON at this time, and the support for 'EsriJSON' comes from users who use the REST connector in Power BI with ArcGIS Services to query the data from the service and pull it into Power BI, or those who use the ArcGIS connector in Power Automate to do it for them.

Re: Shape in Geojson

torkildv — Fri, 16 Sep 2022 12:37:42 GMT

Thanks voor your answer. Nevertheless, if I try to connect to a rest service, I get a standard column, and I don't see how I could retrieve the Shape field in order to "drag it into the location field".

torkildv_0-1663331769098.png

then i get this

torkildv_1-1663331794592.png

Do you have more detailed information or documentation how to connect in a way it works?

Re: Shape in Geojson

simoxu — Wed, 28 Sep 2022 06:49:19 GMT

To get the geometry, In your query to ArcGIS online / Enterprise endpoint, you need to set the specific parameter returnGeometry to true.

Re: Shape in Geojson

torkildv — Wed, 28 Sep 2022 13:36:10 GMT

@simoxu: I've tried a zillion things, (e.g. returNGeometry=true) but the problem is that the documentation tells you:"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." and I do noet find a way in visualising my Shape column. That's where it gets stuck

Re: Shape in Geojson

RudolfdeMunnik — Wed, 28 Sep 2022 18:36:45 GMT

@torkildv @Anonymous User @simoxu I am also struggling with the same problem to parse the EsriJSON in Power Query to get to the Shape column - it just does not work - unless i am doing something wrong. Must the Geometry be in GCS? The only reference to the method is: https://doc.arcgis.com/en/power-bi/get-started/prepare-your-data.htm

@Anonymous UserCan you please provide more detailed steps on how this can be accomplished - I don't want to use Shape Map Visualizer, because it is static, no background and also non-Esri. It will be a big selling point to our users if we can add custom polygons with rendering - not through reference layers and joins.

Thank you

Re: Shape in Geojson

TimMinter — Mon, 10 Oct 2022 23:35:03 GMT

Yep, me too. I need to use an off-menu feature class of an organization's administrative boundaries that are not in the list of recognized boundary types that the geocoders can find and supply. I can query the hosted map service in ArcGIS online and return EsriJSON in WGS84 with all fields like this:

https://services2.arcgis.com/WW3T8U6q5EkZ9U3n/arcgis/rest/services/DCYF_Service_Areas/FeatureServer/1/query?where=1=1&outFields=*&outSR=4326&geometryPrecision=5&f=json
note that returnGeometry=true is the default, so no need to specify

I'll note that there is no such thing as a "Shape" column in that EsriJSON above, so I'm guessing that the help documentation is referring to what Power BI perceives as the Column1.attribute.geometry column.

I can tell Power BI to load JSON, then get Power Query Editor to convert it to a table thingy from which I can load that Column1.attribute.geometry (or "Shape" when I rename it) into the Location well for an "ArcGIS Maps for Power BI" visualization, which then erases the basemap, creates a white canvas, and tells me "The accuracy of your locations can be improved (hint - not by any existing Power BI map visualizations, they can't) and presents a "Go to Location Type and learn more." button. I click the button and fill out this thing, which does not seem to understand what's happening:

TimMinter_0-1665442153986.png

Clicking that "OK" button results in a briefly spinning spinner. Any pointers on how to get Power BI to use an EsriJSON file (or any approach, really) for using off-menu boundaries and columns like what one would find in a SQL Server enterprise geodatabase feature class that Tableau or ArcGIS can use (i.e. WGS84, SQL Server geography spatial type) would be greatly appreciated.

I'm adding this little movie that I made to show the situation and provide the trickery necessary to get EsriJSON into Power BI as a data resource.

(view in My Videos)

Re: Shape in Geojson

AnneTetley — Tue, 10 Jan 2023 12:11:47 GMT

We're also struggling with this and would love a solution as the join in ArcGIS for Power Bi is also not working for us so we need to be able to import JSON boundary layers to visualise the data we want to use in our report.

Thanks

Anne

Re: Shape in Geojson

RudolfdeMunnik — Tue, 10 Jan 2023 20:54:25 GMT

Hi @AnneTetley, @TimMinter,

I am fortunate that i got the procedures from @SeanKMcGinnis and @AnthonyLatini. Thanks Guys.

Prerequisite: You need to have a Power Automate Premium subscription. I used a trial license to test.

There are two methods using ArcGIS for Power Automate to extract custom geometries and attributes from ArcGIS Online or ArcGIS Enterprise Feature Services.

1. Basic Method - Limited to 150 features (also see NOTE for complex Geometries)

2. Advanced Method - Iterate through all features in batches to get all features. (also see NOTE for complex Geometries)

Basic Method:

Overview: Using ArcGIS for Power Automate functions to extract Feature Geometries and Attributes from a Feature Service and convert it into a EsriJson CSV file. The CSV file is then imported into Power BI as a normal table. Add the ArcGIS for Power BI Visualizer. Drag the Geometry field of the CSV into the " Location" field well. Custom Features are displayed and can be used with other Visualizers.

You can run this process once if the Feature Geometries do not change. If they change you can schedule to run the process regularly or create a Power Automate button in Power BI Desktop to execute manually - I used the button method for testing.

1. Power BI Desktop - Add a New Page, Add the ArcGIS for Power BI Visualizer. Sign in to ArcGIS Online or Enterprise.

2. Add a Power Automate for Power BI Visualizer next to the Map.

RudolfdeMunnik_1-1673381010806.png

3. Edit the Power Automate to create a new flow. Click on "New"

RudolfdeMunnik_2-1673381188407.png

Select the "Instant Cloud Flow" option

4. Add flow steps to the "Power BI Button clicked" button

- Click on "New Step" and search for the "ArcGIS" functions

- Select "Get data from Feature Layer" action

- After the step is added - use the ...(3 dots/ellipses) on the step to create a ArcGIS connection - important for authentication.

- After login you should be able to use the dropdowns to select the "Layers in" and the "Feature layer" you want to use.

- Select "Output Format" as CSV

- To ensure all features (up to 150) are extracted set OBJECTID field, Greater than, 0

RudolfdeMunnik_3-1673382326197.png

5. Write CSV content to a File and folder location

- I added a OneDrive - Create File action

- Add Location of the file

- Add the name and extension of the file

- Add content type - Select CSV from the list

RudolfdeMunnik_4-1673382702218.png

6. Save the Power Automate Flow

- Save and Apply

7. Return the report and Run the flow by pressing the button (use CTRL)

- You have to use the CTRL and Left mouse to execute the button (in edit mode)

- Button will show "Triggered"

- Browse to your folder to locate the CSV file was created from the flow

- Open the file in Excel to inspect content and format

8. Load the CSV file in Power BI

9. Expand the table to locate the "geometry" field

10. Make the ArcGIS visualizer active and drop the "geometry" field in the Location field well

11. View your custom geometries

12. If there are missing polygons the content of the "geometry" field may be larger than 32,767 characters - a Power BI limitation. See the NOTE & Workaround below

Advanced Method

Here is a link to the article by @SeanKMcGinnis. Thanks Sean.

https://community.esri.com/t5/arcgis-for-power-automate-blog/using-arcgis-connectors-for-power-automate-to-get/ba-p/1142779

NOTE: The Geometry field is limited to 32,767 characters - This is a Power BI limitation.

Workaround:

Check for multi-part polygons and convert to single part and generalize complex geometries to a new Feature Class for use in ArcGIS for Power BI

I hope the above steps helps.

Rudolf

PS: Thanks again to @AnthonyLatini and @SeanKMcGinnis from the ArcGIS for Power BI Team

Re: Shape in Geojson

AnneTetley — Wed, 11 Jan 2023 10:57:45 GMT

Thanks for the solution, that's really helpful.

We have some Power Automate Premium licenses and could set up a scheduled process perhaps, I wonder if FME would be able to do the same conversion, might also look into that as an option.

Re: Shape in Geojson

RudolfdeMunnik — Wed, 11 Jan 2023 11:05:01 GMT

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

Re: Shape in Geojson

TimMinter — Wed, 11 Jan 2023 16:14:47 GMT

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

Re: Shape in Geojson

ChelseaRozek — Wed, 08 Feb 2023 20:26:51 GMT

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.

Re: Shape in Geojson

TimMinter — Wed, 08 Feb 2023 21:37:18 GMT

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.

Re: Shape in Geojson

Kevin_Peel — Wed, 08 Feb 2023 21:40:54 GMT

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.

Re: Shape in Geojson

RudolfdeMunnik — Thu, 09 Feb 2023 06:51:22 GMT

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.

Re: Shape in Geojson

Kevin_Peel — Thu, 09 Feb 2023 14:19:06 GMT

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!

Re: Shape in Geojson

TaylorWillow — Fri, 03 Mar 2023 19:59:17 GMT

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!

Re: Shape in Geojson

TaylorWillow — Tue, 07 Mar 2023 16:34:25 GMT

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

Re: Shape in Geojson

RudolfdeMunnik — Tue, 07 Mar 2023 22:43:42 GMT

@TaylorWillow and @TimMinter I hope i can assist with the following explanation that worked for me using the REST API to generate a EsriJSON format that works in the location fieldwell. Please find the steps below:

Step 1: Get the REST Endpoint: https://services1.arcgis.com/bKDOijB8sneN5004/arcgis/rest/services/AGRIC_GIS_DATA/FeatureServer/1/query?where=OBJECTID>0&outFields=*&f=json

OBJECTIDS > 0, ALL FIELDS, FORMAT JSON
PUBLIC SHARED

Step 2: Add "Web" data to Power BI using the Get Data tool

Copy the REST Endpoint into the URL space

RudolfdeMunnik_0-1678225049305.png

Step 3: Open Power Query

Power Query opens, scroll to the end of the record.

RudolfdeMunnik_1-1678225290044.png

Step 4: Expand features

First expand the "features" column by clicking on the "expand" icon

2023-03-07 23_57_23-ARCGIS_POWER_AUTOMATE_01 - Power Query Editor.png

Select "Expand to New Rows"

2023-03-08 00_06_49-ARCGIS_POWER_AUTOMATE_01 - Power Query Editor.png

Step 5: Expand features

2023-03-08 00_08_29-ARCGIS_POWER_AUTOMATE_01 - Power Query Editor.png

Un-tick "Use original....."

Step 6: Expand attributes

2023-03-08 00_10_16-ARCGIS_POWER_AUTOMATE_01 - Power Query Editor.png

Un-tick "Use original....."

After expanding - scroll to the last "geometry column"

Step 7: Add custom column to format EsriJSON geometry and add Spatial reference to each record

The critical step is to add a "Custom" column from the Power Query "Add Column" main menu

2023-03-08 00_15_07-ARCGIS_POWER_AUTOMATE_01 - Power Query Editor.png

2023-03-08 00_17_14-ARCGIS_POWER_AUTOMATE_01 - Power Query Editor.png

Provide a column name e.g. "shape"
Add the following syntax after the "=" sign Text.Replace(Text.FromBinary(Json.FromValue([geometry])), "]]]" ,"]]], ""spatialReference"":{""wkid"":102100,""latestWkid"":3857}")
Text.FromBinary will create a single string from the nested rings
Text.Replace will add the Spatial Reference after each records - Note the correct Spatial Reference

2023-03-08 00_23_25-ARCGIS_POWER_AUTOMATE_01 - Power Query Editor.png

Apply and Close the Power Query changes

Test:

Add the ArcGIS for Power BI visualizer
Move the "shape" column to the Location fieldwell
Check if geometries are drawn correctly

2023-03-08 00_28_38-ARCGIS_POWER_AUTOMATE_01 - Power BI Desktop.png

Step 8 (optional): Add geometry length field to view large geometries over 32 000 characters

You can add an additional "Custom Column" to count the Length of the geometry characters. This is to see which records exceed the 32 000 character limit of Power BI. You can then identify missing geometries.

2023-03-08 00_33_11-Custom Column.png

Geometries under 32 000 characters will have the "Spatial Reference" at the end - see sample below

2023-03-08 00_34_26-ARCGIS_POWER_AUTOMATE_01 - Power BI Desktop.png

Geometries above 32 000 characters will not end correctly and therefore not appear on the map.

2023-03-08 00_37_16-ARCGIS_POWER_AUTOMATE_01 - Power BI Desktop.png

Workarounds to prevent this issue:

Create a "Power BI" feature services where you "Generalize" the geometries in Pro and publish
Look for multipart polygons and convert to single part, then generalize

Hope this helps.

Acknowledgements to Derck Vonck @DerckVonck, from Esri South Africa, who assisted me with custom column "formulas". Thanks Derck