@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/qu...
- 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
Step 3: Open Power Query
Power Query opens, scroll to the end of the record.
Step 4: Expand features
First expand the "features" column by clicking on the "expand" icon
Select "Expand to New Rows"
Step 5: Expand features
Un-tick "Use original....."
Step 6: Expand attributes
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
- 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
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
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.
Geometries under 32 000 characters will have the "Spatial Reference" at the end - see sample below
Geometries above 32 000 characters will not end correctly and therefore not appear on the map.
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