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
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.
@PaddyFurlong - Nice! Maybe we can string that up with a little ModelBuilder thing to generalize the geometry for detailed polygons, export to JSON, then use the resulting graphic art in Power BI. Ideally, the REST service would have a generalize capability built in, but it seems a bit more complex than that.
This is brilliant, you are an absolute lifesaver.
I can also confirm that the procedure works for an Esri JSON file exported from ArcGIS Pro using the "Features to JSON" tool. Once you have exported the json file you can then import it into Power BI as a JSON source. Then continue from step 3 above.
@RudolfdeMunnik I just got a chance to follow your lead and got it to work with our data. It looks to me like you've tested it thoroughly. I'm forcing WGS84 decimal degrees (WKID 4326) in my REST request like this:
https://services1.arcgis.com/bKDOijB8sneN5004/arcgis/rest/services/AGRIC_GIS_DATA/FeatureServer/1/query?where=1=1&outFields=*&geometryPrecision=4&outSR=4326&f=json
Then, I adjust the "Shape" custom field code like this:
Text.Replace(Text.FromBinary(Json.FromValue([geometry])), "]]]" ,"]]], ""spatialReference"":{""wkid"":4326,""latestWkid"":4326}")
Then, finally, I add your optional ShpTxtLen so I can see how things worked out and tweak the coordinate precision if needed.
Thanks again!
tim
@TimMinterThank you for the feedback, Yes, I have tested the precision in the REST request. If you are using DD with 6 decimals it helps to "generalize" the polys, but if you use Web Mercator it does not really help because the coordinate numbers are big, even if you remove all decimals you still have 1m accuracy - maybe i am missing something with the precision parameters?
@RudolfdeMunnik Nice! One quick thought on dealing with the 32k character constraint is that the REST service request can specify an appropriately lower precision. So, going from 7 in your example to a lower precision that is appropriate for use in Power BI may be some good trickery. I'm thinking that Power BI doesn't know or care about topology and is happiest with cartoon geometries anyway, so it kind of doesn't matter as long as the area looks ok to the eye.
Hi @RudolfdeMunnik ,
I am trying the REST API solution, but I don't understand the step 1.
Should I take the link you shared? It doesn't work or something happened when you copied pasted it.
Or should I find my own link? If yes, where and how?
Hi @NasreddineD,
Please find the links to my proposed solutions as posted previously.
- The methods does not use the existing/standard boundaries
- The methods does not use "join layer"
- It uses the true geometry of your custom geometries linked to Power BI visualisers
- You can change symbology/styling based on a field for color or size
1. Using Power Automate procedure - Basic and Advanced Method (from @SeanKMcGinnis )
https://community.esri.com/t5/arcgis-for-power-bi-questions/shape-in-geojson/m-p/1247011/highlight/true#M1408
2. Using REST API (without Power Automate)
https://community.esri.com/t5/arcgis-for-power-bi-questions/shape-in-geojson/m-p/1265256/highlight/true#M1443
Hope this helps.
I was wondering if there is a way to create custom polygons (not existing boundaries like country, or region...) And add it to Power Bi? I managed to do it using the "join layer" in power bi, the issue is that is not connected to my power bi data... What I mean is I can only visualise the polygons and clic on it. But I would like to add colors on it depending on a status column I have in my data...
Here is an example of someone having the same issue.
https://community.esri.com/t5/arcgis-for-power-bi-questions/using-custom-polygon-locations-from-layer-in-power/td-p/1214256
Many thanks @RudolfdeMunnik!! The final piece of the puzzle!
@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
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
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
Apply and Close the Power Query changes
Test:
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:
Acknowledgements to Derck Vonck @DerckVonck, from Esri South Africa, who assisted me with custom column "formulas". Thanks Derck
@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.htmCrucially, 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.
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.htmI'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!
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!
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.
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.
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.
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.
@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.
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. 👍
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.
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.
3. Edit the Power Automate to create a new flow. Click on "New"
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
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
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
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
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:
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:
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.
@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
@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
To get the geometry, In your query to ArcGIS online / Enterprise endpoint, you need to set the specific parameter returnGeometry to true.
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".
then i get this
Do you have more detailed information or documentation how to connect in a way it works?
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.
Signed in members can post, follow updates, and more. New here? Register a free account.
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.
