As shown in a previous Exprodat blog, ArcGIS Maps for Power BI is a valuable visualization tool for adding maps and spatial context to your Power BI reports. Any data source with WGS84 coordinates or an address can be added directly to the map in Power BI.
Connecting to ArcGIS Online or ArcGIS Enterprise allows you to add content as reference layers, which can be joined to data layers to enrich your report data. However, if you want to use those ArcGIS hosted feature layers or other REST services as data sources for your report, it can be a challenge getting to the raw data: to date, Esri and Microsoft have not released a certified ArcGIS connector for Power Query, leaving web services out of reach for most Power BI users.
To help with this, we will walk you through the steps to connect to ArcGIS REST service URLs to use as data sources in your Power BI projects.
This first part of two blogs, describes a step-by-step process for manually connecting to and reformatting the service in Power Query as a table.
In part two, you will find a useful Power BI report template and a guide to getting your data integrated quickly and easily.
Get the Data Source URL
Before you can add a web service data source to your report, you’ll need to find the REST endpoint URL where the data you want in Power BI can be queried.
You may already have a URL in mind, but if you’re trying to get data from a layer item in ArcGIS Online or ArcGIS Enterprise, simply go to the item details and find the source URL at the bottom of the Overview page.
For example, the URL found at NSTA Field Production, PPRS (WGS84) is https://data.nstauthority.co.uk/arcgis/rest/services/Public_WGS84/UKCS_PPRS_Fields_WGS84/MapServer.
Click the View button to open the URL.
At that map service URL, you will find a section called Layers. Each Power BI data source can only include one layer or table. While the example below only has one layer in the Map Service, others can include multiple layers, so select the layer containing the data needed for your report.
Click on the one you want to use for your query.
The goal is to find a URL ending with a numeric layer ID. In this case, we have: https://data.nstauthority.co.uk/arcgis/rest/services/Public_WGS84/UKCS_PPRS_Fields_WGS84/MapServer/0
Once there, make a note of the Fields shown on the page and make a list of the fields you need for your report.
Crucially, to use this layer as a data source, it must have Query listed in the Supported Operations section at the bottom of the page.
If you are using a Feature Service (where the URL includes “FeatureServer”), this will always be enabled. However, using a Map Service (“MapServer”) may not always include that capability. If the Query link exists, click it! If not, you won’t be able to access the attribute table for this service in Power BI.
The Query form builds the URL you will need to use and requires just a few fields:
Condensed query form example, excluding additional parameters.
- Where – a definition query using SQL expression. Filtering data at this point makes the Power BI query download data much faster, so it is recommended to define only the records you need for your report. If you need all records, enter “1=1”. For the example URL, I’ll retrieve only data reported 1 January 2024 or later by entering “PERIODDATE >= DATE ‘2024-01-01’”.
- Out Fields – comma-separated list of field names you want to have included in your table. Again, limiting queries to only the fields you need greatly improves performance. Enter the list of fields you need from the previous page and add a comma between each. If you need all fields, enter an asterisk “*”. In the example shown above, I’ve included just a few fields to simplify the dataset.
- Return Geometry – if you want to add this data source to the map visualization, leave this set to True. If you don’t need to see this data on the map, change it to False – this will vastly improve the response time of your query.
- Output Spatial Reference – because the ArcGIS Maps for Power BI visualization requires coordinates in WGS84 or Web Mercator, the well-known ID (WKID) for either of those must be included here. Enter “4326” to set output geometry to WGS84 latitude/longitude coordinates.
- Datum Transformation (optional) – if the spatial reference of the service does not use the WGS84 datum, it’s highly recommended you enter the transformation well-known ID (WKID) here. This parameter was added at ArcGIS Server 10.8, so not all services will support it. You can find more information on transformation WKIDs at the Esri developer help page Using spatial references. For example, using a service in European Datum 1950 (ED 1950) for data in the UK North Sea, you’d likely use code “1311” for the ED_1950_To_WGS_1984_18 You can also search for details on transformations at the EPSG Geodetic Parameter Dataset. Because our example source is already in WGS84, we do not need to specify a transformation.
- Format – change this to JSON. JSON is a format recognised by Power BI, and the geometry format can be easily used in the ArcGIS map visualization.
Click the Query (GET) button to retrieve the JSON text. The URL in the address bar will be updated to include the parameters from the query form.
Finally, copy the URL. You will use this as your data source in Power BI.
Enter the Query URL in Power BI
In your Power BI report, go to the Get data button and select Web.
Paste the URL from your query into the URL. If your URL ends with “f=pjson” you must change it to “f=json” for it to work. The “f=pjson” is a pretty formatting of the json file including line breaks and indentation, but Power BI doesn’t appreciate readability. Changing it to “f=json” will keep it in simple, raw, unformatted form.
Click OK to proceed to the data transformation in the Power Query Editor window.
Transform the Raw JSON Data Using Power Query Editor
There are a few steps you’ll need to accomplish to drill down to get to the table of features you’re looking for. Power Query has already tried to automatically convert the JSON to a table, expand the fieldAliases and spatialReference records in the data to multiple columns and determine the column data types. But none of these steps are particularly useful, so let’s start from the source.
- Go to the Query Settings pane on the right and click the gear icon next to Source at the top of the Applied Steps
- In the popup window, “Json” should be already selected for the Open file as Change the File origin to “65001: Unicode (UTF-8)” – it won’t make much noticeable difference unless your data have non-ASCII characters, but it’s a good habit to follow. Click OK.
- In the main view of the Power Query Editor, find the row that says features and click the List You’ll be warned that you will “create a navigation step that replaces the subsequent steps.” This is expected, so click Continue.
- In the List Tools: Transform menu, click To Table to convert this list of features to a table. Keep the default settings (no delimiter, extra columns shown as errors) and click OK.
- You now have a table with single column named Column1 with rows of records. At the top of the table, next to the column name, click the Expand Columns Keep the attributes and geometry items selected but deselect the Use original column name as prefix option and click OK.
- Do the same with the attributes column – click the Expand Columns button on the header and select all the field names you want to use. Click OK.
You now have a recognizable table of feature records.
There are just a couple of more steps required for you to make it useful in Power BI.
Convert JSON Geometry to String Field
Sadly, there is no one-click solution to convert the geometry records in the table to string format, but here is the simplest method:
- Go to the Add Column menu and click the Custom Column Enter “Shape” as the New column name and enter this Custom column formula:
= Text.FromBinary( Json.FromValue( [geometry] ) )
This formula identifies the record as a JSON object and converts it to a text string. Clicking OK adds the new Shape column to your table with the EsriJSON format geometry in plain text.
- Now you have a Shape column, you can remove the geometry column by right-clicking on the header and selecting
Update Column Data Types
You can go through each column and specify the correct data type (e.g. integer, decimal, text, date, etc.) but there’s a one-click trick that does it for you! Well, almost…
- Select all columns in the table, go to the Transform menu and select Detect Data Type to automatically determine the data type for each column.
Why does Power Query think your date columns are numeric data? Because that’s how the JSON delivers Esri datetime fields: milliseconds since Unix epoch time (1 January 1970 00:00:00). Just like with the geometry field, we’ll need to convert datetime fields by adding custom columns.
- Go to the Add Column menu and select Custom Column. Give it a unique New column name – I usually just add “_DATETIME” to the end of the existing column name – and enter the Custom column formula:
= #datetime( 1970, 1, 1, 0, 0, 0) + #duration( 0, 0, 0, [PERIODDATE] / 1000 )
This example dataset uses the field name PERIODDATE but you can replace that in the formula above with whatever datetime field name you are using in your data. Click OK when done.
- To avoid confusion, you can now remove the original numeric field so only the new datetime column remains. Right-click the column header and choose Remove.
Repeat the previous steps as needed for all datetime fields in your table.
Verify the Table is Working
You can now go to the Home menu in Power Query Editor and select Close & Apply. It will refresh the data query and provide the Data panel with a list of columns from the table. These columns can be added to the fields of any Power BI report visualizations, including ArcGIS Maps for Power BI.
Drag the Shape column name into the Location field of the ArcGIS Maps visualization on your report to see the points, lines, or polygons display on the map. You can set unique value symbology by dragging another column to the Color field. Set up additional symbology and labelling options in the visualization’s Layers menu.
Considerations
The steps in this blog are fundamental and should work for most instances, but there may be other hurdles in your way so here are some things to keep in mind:
- The service query will only return a maximum number of records specified by the MaxRecordCount property on the layer’s REST details page. If the total number of returned records from the query exceeds this count, you will only receive as many as the service allows. Use the Where clause to filter the query whenever possible to limit the records. For a more advanced approach for Power Query experts, make multiple requests to the service by taking advantage of the Result Offset and Result Record Count parameters and combine the results into one table in the Power Query Editor.
- Modern security authentication is complex. The steps outlined here are useful for public services requiring no authentication. Even a simple token-based authentication is going to require regular, manual refreshing with a new token each time it expires. While there are methods of making automatic token requests using your credentials, the complexity is a bit outside the scope of this blog. Get in touch with us if you need consulting services for complex data or authentication issues.
- Column names will be the source field names. If field aliases are configured in the source layer, they will not be shown. Layer subtypes and coded field domains are also not used, so only the coded values are returned by the REST service. These problems and more are resolved in the second part of this mini blog series.
With these considerations in mind, as well as the time it takes manually building your own queries, check out part two of this blog to get a report template with pre-configured query to help simplify the process.
Posted by Alex Rexroad, GIS Consultant, Exprodat
Data Sources and Links
- The example service URL used in this blog is NSTA Field Production, PPRS (WGS84) layer from ArcGIS Online. This layer contains information provided by the North Sea Transition Authority (NSTA) and/or other third parties. Access to the data at this URL is not guaranteed and all data are subject to change at discretion of NSTA. Hyperlinks are current as of date of publication.
- ArcGIS Maps for Power BI | Microsoft Power BI
- Data sources in Power BI Desktop
- PowerBI data and geocoding – data preparation – location types
- PowerBI – Add a data layer to a map
- Get started with ArcGIS for Power BI—ArcGIS for Microsoft 365 | Documentation
- Business Productivity Solutions Using Location | ArcGIS for Microsoft 365 (esri.com)
- Query (Map Service/Layer) | ArcGIS REST APIs | ArcGIS Developers
