Skip To Content

Prepare your data

Geocoding is the process of transforming a description of a location (such as a pair of coordinates, an address, or the name of a place) to a location on the earth's surface.

Esri provides resources that allow you to locate addresses, cities, landmarks, business names, and other places in more than 100 countries around the world. ArcGIS Maps for Power BI uses the ArcGIS World Geocoding Service to detect point locations (such as addresses, United States cities, and world cities) and the ArcGIS GeoEnrichment Service to detect boundary locations (such as ZIP Codes, states, provinces, and countries). If you signed in to your ArcGIS Online or ArcGIS Enterprise account, ArcGIS Maps for Power BI uses the default locators configured for your organization to find locations.

When you add data to a map from Power BI, choose the location type that best represents your information. Location information from your data is used to create a relationship between your business data and the specified location type. Although ArcGIS Maps for Power BI has functionality that provides a location on the map based on the available location data, there are a few ways you can prepare your data before you add it to the map to help ensure accurate results every time.

Location types

Follow these best practices to accurately map your data.

Use the appropriate location information in the Location field

The following types of information are allowed in the Location field:

  • Address information—Depending on the geographic region of your organization, address data can be composed of any of the following: address, neighborhood, city, subregion, region, state, province, postal code, United States ZIP Code, country, and so on. The more address elements your data contains, the more accurate your results will be.

    When you geocode addresses, you can add a maximum of 3,500 points to an ArcGIS Maps for Power BI visualization as a standard user or 10,000 points if you're signed in to your ArcGIS Online or ArcGIS Enterprise account.

    The Location field well accepts only a single value. Because of this, if your address information is contained in separate columns, it's important to combine the information into a single, comma-separated location column. You can then place the combined column data in the Location field well to add data to the map.

  • Standard administrative boundaries—When you use standard administrative boundaries in the Location field, ArcGIS Maps for Power BI first searches for specific settings in the column's Data Category metadata; if the data is categorized as State or Province, Postal Code, County, or Country/Region, ArcGIS Maps for Power BI uses a standard geography query to locate the items on the map.
    Note:

    In some cases, ZIP Codes may be associated with nonresidential P.O. boxes; these codes do not have boundaries associated with them, and the GeoEnrichment service does not maintain demographic data for this type of ZIP Code. If you're creating a map using nonresidential ZIP Codes, use the Points location type. Using the Boundaries location type will result in errors during geocoding.

    If no metadata is found, ArcGIS Maps for Power BI searches for supported keywords in the name of the data fields that correlate to standard administrative boundaries. The following table lists the standard administrative boundaries and related keywords. Keywords are not case sensitive.

    Location typeSupported keywordsGeometry

    Address

    city, cities, addr, address, street, town, capital

    points

    United States state

    state, states

    polygons

    ZIP Code

    zip, zips, zipcode, zipcodes, zip code, zip codes, postal code, postal codes

    polygons

    United States county

    county, counties

    polygons

    World city

    city, cities

    points

    World country

    country, countries

    polygons

    If a keyword is found, ArcGIS Maps for Power BI uses the ArcGIS GeoEnrichment service to add polygon locations to the map. For a complete list of supported countries, see GeoEnrichment coverage.

    Administrative boundaries are added to the map as polygons, which represent both the shape and the location of the place.

    If ArcGIS Maps for Power BI cannot find recognizable metadata or keywords, it sends the data in text form to the ArcGIS World Geocoding Service to find point locations. In many cases, this results in inaccurate findings because there is not enough information to determine the locations. Use the Location type pane to specify the parameters for the locations.

Default locators

When you add locations to a map, ArcGIS Maps for Power BI uses different locators to find locations and plot them on the map.

The following table describes how ArcGIS Maps for Power BI determines which locator to use:

AccountLocator

Standard account Standard (included with Microsoft Power BI)

  • Points—ArcGIS World Geocoder
  • Polygons—ArcGIS GeoEnrichment Service

Sign in to ArcGIS Online ArcGIS Online account

Default locator configured for your organization

Sign in to ArcGIS Enterprise ArcGIS Enterprise account

Default locator that supports batch processing configured for your organization

Note:

If your ArcGIS Enterprise organization does not contain a locator that supports batch processing, ArcGIS Maps for Power BI uses the first available batch processor in ArcGIS Online.

Choose the boundary location search method

When you're mapping boundaries, such as states or other standard administrative boundaries, ArcGIS Maps for Power BI uses a closest-match search method to locate the areas. This is the default behavior and is more forgiving in terms of spelling or other errors in your data. Use Closest match if you believe your dataset may contain spelling errors (such as Claifornia instead of California), if there are multiple possible English spellings of your location names, or if you're not sure of the exact spelling used by Esri's data services. This option is not recommended for codes, as it may return incorrect geometries. The following are examples of data that work well with the Closest match search:

  • Country names
  • Brazil states
  • U.S. states
  • Any data that may contain spelling errors or special characters

If you're using codes or abbreviations to locate boundaries, or when you're confident that the spelling in your dataset is correct and matches that used in Esri's data services, use the Exact match search method. For example, use Exact match for the following data:

  • ZIP Codes
  • U.S. state abbreviations
  • ISO three-letter country codes (ISO 3166-1 Alpha-3)
  • Geometry names that have a single English spelling and that you're sure are accurately spelled in your dataset (California, for example)

Locate postal codes

When you use ZIP Codes or postal codes in the Location field, ArcGIS Maps for Power BI searches for specific settings in the column's Data Category metadata to identify the location type and accurately map it. When you're mapping locations by ZIP Code or postal code, it's important that you properly identify the data, depending on the format of the codes.

When postal codes are represented as codes or IDs, for example, 92374 or CF, categorize your data as Postal Code. Doing so allows ArcGIS Maps for Power BI to identify the data as codes so that it can accurately map the locations.

If a postal code is represented as a full name such as Cardiff, however, setting the metadata to Postal Code may generate inaccurate results. In these cases, set the metadata to Uncategorized and specify a column name that ArcGIS Maps for Power BI can correctly identify using recognized keywords, such as ZIP Code or Postal Code. Alternatively, use the Location type pane in ArcGIS Maps for Power BI to set the location type as a postal code category for the appropriate country for your data.

Use specific latitude and longitude values

Latitude and longitude values represent an x,y coordinate location on the map. You can map x,y coordinate data using the World Geodetic System 1984 (WGS84) coordinate system. In this system, latitude (y) values range from -90 to 90, and longitude (x) values range from -180 to 180.

Create a separate Location column in your data

Because the Location field can contain only a single value, it's best to create a new column in your dataset that combines all the address information (such as address, city, state, and ZIP Code) into a single, comma-separated values column that you can then use to add location-based information to your map.

Note:

Convert address and ZIP Code values to text format (Format: Text) before concatenating columns.

To add a new location column to your dataset, do the following:

  1. Using the Power BI Desktop Report Editor, open the dataset you want to edit.
  2. Do one of the following:
    • On the Modeling tab in the Calculations section, click New Column.
      Click New Column on the Modeling tab.
    • In the Fields pane, click More options next to the dataset you want to edit and choose New column.
      Click More options > New column in the Fields pane
  3. In the new column, create a custom DAX formula that combines fields from multiple address columns into a single column. For example, to combine Address, City, Province, and Postal Code columns, your formula should look similar to the following:
    Column = [Address] & ", " & [City] & ", " & [Province] & ", " & [Postal Code]
    Note:

    You can omit the commas by using only spaces between the field values. For example, Column = [Address] & " " & [City] & " " & [Province] & " " & [Postal Code].

  4. Give your new column a name that lets you easily identify it as your combined location data.
  5. Save the dataset. You can now use the new location column to map your data.

Verify geocoding errors

When you add a location field to the ArcGIS Maps for Power BI visualization Location field well, ArcGIS Maps for Power BI sends the information to one of its geocoding or geoenrichment servers to determine the correct placement of locations on the map. Sometimes, due to spelling errors or incomplete location information, the geocoding process fails, and the location cannot be positioned on the map. If some locations do not appear on the map, verify your original data to ensure that it is accurate, correct any errors, and import your dataset to create the map.

Data types, formats, and categories

The subsections below contain additional information regarding ArcGIS Maps for Power BI and data.

Categorize your location fields

To help ensure that ArcGIS Maps for Power BI accurately geocodes your data, specify the data category for each field. Using the Power BI Desktop Report Editor, choose the table you want to modify. On the ribbon, click the Modeling tab. Select a column in the table and choose the appropriate category from the Data Category drop-down menu. For example, a U.S. state column should be categorized as State or Province. For a world country column, choose Country/Region. For ZIP Codes, choose the Postal Code category. If you've created a combined location column, choose the Address category.

Desktop data categories

For columns that contain location data, set the Default Summarization option to Do Not Summarize.

Use text values

Format columns in your dataset that will be used for location (ZIP Codes, for example) as textual values, not numerical values. If your data contains a number that includes a leading zero, as is common with ZIP Codes, some software packages interpret these fields as numerical values and strip out the leading zero, changing the original value. Formatting such columns as text ensures that your data remains accurate.

Desktop data types

Use time formats

ArcGIS Maps for Power BI supports several date and time formats as listed in the table below. If you use a format that is not listed, ArcGIS Maps for Power BI tries to match the format to an equivalent one; for example, 30‑Dec‑1997 will display as 30 Dec 1997. If there is no equivalent format, ArcGIS Maps for Power BI uses the default MM/DD/YYYY HH:MM: AM/PM (12/30/1997 6:00 PM).

For best results, use the Power BI modeling tools to set your date and time formats before you add data to a map.

ArcGIS Maps for Power BI supports the following date-time formats:

Date-time formatExample

MM/DD/YYYY

12/30/1997

Month DD, YYYY

December 30, 1997

Day Month (short) YYYY

Tuesday, Dec 30, 1997

Day, Month DD, YYYY

Tuesday, December 30, 1997

MM/DD/YYYY HH:MM AM/PM

12/30/1997 6:00 PM

MM/DD/YYYY HH:MM (24hr)

12/30/1997 18:00

MM/DD/YYYY HH:MM:SS AM/PM

12/30/1997 5:50:50 PM

MM/DD/YYYY HH:MM:SS (24hr)

12/30/1997 17:59:59

Month YYYY

December 1997

Month (short) YYYY

Dec 1997

YYYY

1997

HH:MM:SS AM/PM

5:59:59

HH:MM:SS (24hr)

17:59:59

HH:MM AM/PM

5:50 PM

HH:MM (24hr)

17:59

Number of locations supported

When creating a map, it's easy to get carried away and try to add a large amount of data to the map. It's important to keep in mind that plotting too many individual locations can lead to viewer confusion and frustration, and the resulting map may not provide a clear picture of your business data. In addition to creating a map that's difficult to interpret, geocoding a large number of rows of data may negatively impact the performance of ArcGIS Maps for Power BI.

LocationStandard account Standard (included with Power BI)Sign in to ArcGIS Online ArcGIS organizational account

Address information in Location field well

3,500 locations/map

10,000 locations/month

10,000 locations/map

1,000,000 locations/month

Latitude and Longitude values in corresponding field wells

• Chrome, Safari, Firefox, Microsoft Edge

30,000

30,000

• Microsoft Internet Explorer 11

10,000

10,000

Standard Administrative Boundaries in Location field well*

• Chrome, Safari, Firefox, Microsoft Edge

15,000 polygons/map

15,000 polygons/map

• Microsoft Internet Explorer 11

5,000 polygons/map

5,000 polygons/map

*Due to hardware and browser limitations, performance may vary when you render polygons such as ZIP Codes, counties, or block groups, for example. To mitigate problems, ArcGIS Maps for Power BI restricts the maximum number of locations supported.

An ArcGIS Maps for Power BI visualization can contain a maximum of 1,024 data columns.

For details about address and location fields, see the Location types section above.