Shape in Geojson
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?
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/...
- 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:
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.
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.
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