Extracting ArcGIS Data Directly into Power BI and Icon Map Pro
Icon Map Pro already provides comprehensive support for ArcGIS feature layers, allowing them to be seamlessly connected to your Power BI data model via data-bound layers. This approach works by matching a field from the ArcGIS feature layer to a corresponding field in your Power BI dataset, enabling an effective connection.
While this method is robust, feature layers often contain additional valuable data attributes alongside the geometry. Currently, Icon Map Pro can display these attributes within tooltips, but there are scenarios where you may want to filter or leverage these attributes elsewhere in your Power BI reports. Previously, extracting this additional data required external tools and manual processes.
To streamline and enhance this workflow, we have developed custom Power Query functions that allow you to directly extract attribute data from ArcGIS feature layers into your Power BI model. This method has significant advantages, including automated updates with each Power BI refresh and the option to extract geometry data as Well-Known Text (WKT). By embedding geometry directly within the data model, the entire visualisation process becomes faster and simpler, eliminating real-time references to external feature layers. This approach is recommended, provided the geometry remains within Power BI's 30,000-character field limit. For larger geometries, we suggest a hybrid approach: storing attributes in the data model while loading geometry separately.
This guide explains how to use two custom Power Query functions ExtractArcGISFeatureLayerOAuth and ExtractandCombineEsriFeatureOAuth to seamlessly bring ArcGIS data into Power BI. These functions simplify the process of extracting ArcGIS feature layers, either as attribute data alone or along with their geometry in WKT format, making it ready for visualisation in Icon Map Pro.
The functions are currently in preview. They have been tested against numerous example features but we may not have covered every possible scenario. If you encounter any issues please reach out at support@tekantis.com
1. Initial Setup
Before starting, you'll need to configure ArcGIS OAuth authentication:
- Obtain your Client ID and Client Secret by registering an OAuth app in your ArcGIS Online or ArcGIS Enterprise portal. Follow the ArcGIS documentation to set this up.
Note: The above step is only necessary if you wish to connect to a secured ArcGIS Environment. If the ArcGIS Features are publically available then you can simply use the non OAuth versions of the functions without a clientid and secret.
Next:
- Download and open the following example PBIX file in Power BI Desktop, which contains the two custom functions.
- Navigate to Transform data → Manage parameters.
- Enter your Client ID and Client Secret, and optionally specify your ArcGIS portal URL if different from ArcGIS Online (
https://www.arcgis.com
). - Click Close & Apply.
You are now set up to start using the functions!
2. Understanding Extraction Modes
These functions can extract ArcGIS data in two ways:
Mode | What it Does | When to Use It |
---|---|---|
Attributes Only | Extracts only the attribute data. Geometry is not included. | Ideal for datasets containing complex geometry where the WKT values are likely to be bigger the the 30,000 character limit in Power BI. You can separately import geometry into Icon Map Pro using the existing feature layer method. |
Attributes + WKT Geometry | Includes geometry data in Well-Known Text (WKT) format. | Recommended approach, if feasible, as all data sits within your Power BI model. This simplifies the management especially with multiple layers, and will likely increase performance as only the required data is rendered at run time. |
3. Overview of Functions
- ExtractArcGISFeatureLayerOAuth: Extracts a single ArcGIS feature layer.
- ExtractandCombineEsriFeatureOAuth: Extracts multiple layers from an ArcGIS Feature Service into one combined table.
- ExtractArcGISFeatureLayer: As above but without the authentication. Used for publicly available feature layers.
- ExtractandCombineEsriFeature: As above but without the authentication. Used for publicly available feature layers.
4. Detailed Function Guides
Function: ExtractArcGISFeatureLayerOAuth
Purpose: Extract data from a single ArcGIS feature layer.
How to use:
Option 1 (recommended):
- In Power BI Desktop, go to Transform Data to open the Power Query Editor.
- In the left-hand pane, click on the function ExtractArcGISFeatureLayerOAuth.
- Enter the required parameters into the displayed fields.
- Click Invoke to run the query immediately.
Option 2 (Advanced users):
- Create a new query (Get Data → Blank Query → Advanced Editor) and enter:
let
Source = ExtractArcGISFeatureLayerOAuth(
"https://services.arcgis.com/.../FeatureServer/0",
true // or false
)
in
Source
- Replace the URL with your actual feature layer URL.
- Set the second parameter to
true
if you need geometry in WKT format, orfalse
if you only want attributes.
Parameters:
- featureLayerUrl (required): URL of your ArcGIS feature layer.
- convertToWKT (required): Choose
true
to include geometry as WKT orfalse
to exclude geometry. - portalUrl (optional): Custom portal URL, if you're not using ArcGIS Online.
Output:
- A clean table ready for Power BI, with or without a WKT column for geometry.
Function: ExtractandCombineEsriFeatureOAuth
Purpose: Extract multiple layers from an ArcGIS Feature Service and combine them into one dataset.
How to use:
Follow the same procedure as above:
Option 1 (recommended):
- Click the function ExtractandCombineEsriFeatureOAuth in the Power Query Editor.
- Fill in the parameter fields provided.
- Click Invoke to execute.
Option 2 (Advanced users):
- Create a new query and enter:
let
Source = ExtractandCombineEsriFeatureOAuth(
"https://services.arcgis.com/.../FeatureServer",
true, // Include geometry as WKT
"medium", // Geometry detail ("low", "medium", "high")
true, // Expand coded domain values
"0,1", // Layer IDs to include (leave blank for all layers)
null // Portal URL (leave null for ArcGIS Online)
)
in
Source
Parameters Explained:
- FeatureServiceUrl (required): Base URL for the ArcGIS Feature Service.
- IncludeGeometryWKT (optional, default true): Choose if you want geometry included in WKT format.
- GeometryDetail (optional, default "low"): Adjusts the precision of geometry. Use:
- "low" to reduce the size of the WKT reducing the chance of any overflows past the 30K character limit,
- "medium" for more detailed maps,
- "high" for maximum precision.
- ReturnDomainValues (optional, default true): Automatically replaces coded values with readable labels (assuming this has been configured in ArcGIS).
- FilterLayers (optional): Specify specific layers using IDs. For example, "0,2,3" extracts only layers with those IDs. Leave blank to extract all available layers. Note: you could use this function to extract a single layer as an alternate to the above function, if you need the additional control.
- portalUrl (optional): Provide your ArcGIS Enterprise URL if not using ArcGIS Online.
Output:
- A single combined table including:
- GeometryUID: A unique ID for each row.
- LayerName: Indicates the original ArcGIS layer.
- Attribute fields with readable names.
- WKT geometry if selected.