If you worked through the process that we outlined in part one of this blog, you know there are quite a few steps involved in transforming a REST endpoint URL into a Power BI data table. The more complex the data source, the more steps will be involved. When you get to field aliases and domain or subtype descriptions, the process gets even more complicated. So, this second part of the blog provides a nice shortcut for you: a pre-configured Power BI report.
Download the zipped Power BI report (.pbix) to use as a template complete with the Power Query formulas you need to connect. Add your own visualizations to the report or just copy and paste the query formula group into your own reports.
While the template and described steps in this blog are intended to be used specifically with ArcGIS REST services, the query and parameters can be modified to use any REST API endpoint, though it may be easier to use the manual steps outlined in part one.
Edit the Query Parameters
When you open the blank report, go directly to the Data pane, right-click the ArcGIS REST Query Process data source and select Edit query.
The Power Query Editor window opens, but there is no need to edit the query formulas. With the downloadable query template, we’ve made it as easy as possible. Simply go to the Home tab menu, click the Manage Parameters drop-down button, then select Edit Parameters.
Fill out the form in the Edit Parameters window. Each parameter has a tooltip icon to help, but here’s a quick explanation of each:
- Service URL – enter the URL of the feature service or map service you want to use. Remember, it must be an individual layer in the service and the URL must end with an integer representing the layer ID. The example used previously was https://data.nstauthority.co.uk/arcgis/rest/services/Public_WGS84/UKCS_PPRS_Fields_WGS84/MapServer/0 but the template uses https://services.arcgis.com/P3ePLMYs2RVChkJx/arcgis/rest/services/World_Countries_(Generalized)/FeatureServer/0 as a placeholder. As mentioned in part one of this blog, there must be a Query hyperlink in the Supported Operations section of the URL page for it to be used as a Power BI data source.
- Token – if you are trying to access a secured service, you may need to request a token to authenticate your queries. The token can be copied and pasted into this parameter. Be aware that tokens do expire, and a new token may need to be generated and pasted to keep your connection active. If you are using a public service with no login required to access it, you can leave the token blank.
- Where – required for any ArcGIS query, the where clause is a SQL expression used to filter the records returned from the service. If you need only a subset of the total data available, it is best to filter here to dramatically improve performance. Only the records that meet the criteria will be downloaded to Power BI. If you’re totally lost on how to format the where clause, look at SQL reference for query expressions used in ArcGIS for some examples. By default, the expression 1=1 will return all records from the service, though it should be avoided when querying large datasets.
- Out Fields – a comma-separated list of the field names you want to display in your table. Like the where clause, this reduces the amount of data returned by the server and improves performance. You can find a table of valid fields by clicking on the Fields from Service URL query table. Filter the table to show just the fields you want included. The filtered list is automatically reflected in the drop-down options for the Out Fields parameter. You can also select the asterisk (*) to return all fields.
- Return Geometry – set to TRUE if you want to add the layer to your report’s ArcGIS Maps for Power BI visualization. If you only need the attribute table, set this to FALSE to greatly improve performance.
- Datum Transformation – if you have Return Geometry set to TRUE, you should add a datum transformation ID in this parameter if the service uses a spatial reference other than WGS84. If the service is in a WGS84 geographic or projected coordinate system, leave this field blank. You can find valid transformation IDs to use at ArcGIS Coordinate systems, map projections, and transformations or the EPSG Geodetic Parameter Dataset. For example, if the service is in European Datum 1950, there is a long list of transformations for retrieving WGS84 coordinates, but ED50 to WGS 84 (18) is most commonly used for UK offshore oil and gas exploration and production, so we could use the code “1311” as the datum transformation.
- Use Field Aliases – as demonstrated in the example NSTA layer, field names are often paired with an alias that provides a more readable format. Because of limitations to field names, it may be easier to use the configured alias. Set this parameter to TRUE to use aliases instead of the raw field names. The original field name will be appended in brackets to the alias. If set to FALSE, only the field names will be used to name columns.
- Add Domain/Subtype Descriptions – some attribute tables use domains and subtypes, which use coded values to represent longer strings of text. This can make it a nightmare trying to read/decipher the meaning of codes. Setting this parameter to TRUE will add a column in your Power BI table for each field with a domain or subtype set. The added column will be the text description of the coded value, making it much easier to interpret. This can significantly increase the size of your data table and add some processing time, so set to FALSE if it’s not needed.
Using Additional Parameters
If you need to customize the query with additional URL parameters, open the advanced editor for the ArcGIS REST Query Process table.
Add the required parameters in the Query list by removing the “//” from each line you want to enable and include the appropriate value in the quotation marks. The image below includes the maxAllowableOffset and geometryPrecision parameters as an example.
If you have other custom parameters not shown in the list, simply add them yourself using the same format as the example parameters.
Once your parameters are all set, click OK and then select the ArcGIS REST Query Process table to preview the data, formatted to comply with the set parameters.
You can apply additional transformations such as sorting, filtering, adding or removing columns, calculating statistics or converting values as needed.
When you’re done, go to the Home tab and select Close & Apply to return to your report.
You can now add the fields from your ArcGIS service query into any of the report visualizations as a data source, including ArcGIS Maps for Power BI.
Summary
Hopefully the provided Power BI report query has helped you to shortcut getting data from a REST service into your own report without the complexity of part one’s manual steps.
If you have any trouble with the query template we’ve provided, let us know so we can help troubleshoot and post updates if needed.
If you’re still struggling with getting your geospatial data into Power BI, or integrating ArcGIS with another system, please get in touch.
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
- 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
