Handling Paginated REST API Results with Power Query

Querying a RESTful API, ingesting its response, and transforming the data through Power Query is a powerful capability. In many instances, the integration between Power Query and Power BI or Power Query and Excel further simplifies the process by eliminating the need for other tools and scripts to perform intermediate processing of a JSON response. However, working with REST APIs is not always straightforward as there can be complexities with larger data sets. To limit the processing and bandwidth burden on an API, a response may be paginated to limit the number of results returned for any given query. In other words, if a particular query is expected to return a million entries, then the API may paginate those million entries into blocks of 100 entries per page. To retrieve the full million entry result set would require ten thousand separate API requests to retrieve each 100-entry page (100 * 10,000 = 1,000,000).

The purpose of this post is to demonstrate Power Query code that can handle paginated JSON results from a REST API. Three solutions are presented to handle API request scenarios where the number of pages is known, calculated, or unknown.

REST API

The code presented in this solution uses a publicly available REST API from the U.S. Department of the Treasury named the Treasury Reporting Rates of Exchange. The intent of this API is to provide the U.S. government’s authoritative quarterly exchange rates for converting foreign currency units and U.S. dollar equivalents. I chose this particular API because the basic concept of an exchange rate is commonly understood albeit entirely unimportant for the purposes of this exercise. More importantly, this API exposes a significant volume data needed to demonstrate pagination. I recommend exploring the full U.S. Treasury Fiscal Data site as there are quite a few data sources which are well-documented, free-to-use without requiring a sign-up or API token, and they provide a great opportunity to learn how to interact with APIs as well as develop analytics using real-world data.

The API query used in this post is straightforward. The three data fields requested from the Rates of Exchange API are Country – Currency Description, Exchange Rate, and Record Date. As illustrated in the screenshot below, the JSON response contains some meta data including count, total-count, and total-pages. For this demonstration, these particular fields and their corresponding values are important to note for final verification that the Power Query code is working as expected. The count field corresponds to the number of entries returned, the total-count field corresponds to the total number of entries in the result set, and the total-pages field corresponds to the total number of paginated results, e.g., 100 maximum results across 162 total pages (page index 162 is a partial page with only 10 entries).

API JSON Results
API JSON Results

Custom Power Query Paging Function

According to the documentation for this API, there are two defined parameters with respect to response pagination. The page[size]= parameter sets the number of entries returned in the response. The default response for page[size]= is 100 entries per page. This example uses the default setting. The more important parameter for this demonstration is the page[number]= parameter as it will be used to iterate through each page number. The page[number]= parameter sets the pagination index which is used to return a particular page from the resulting data set. As an example, if the resulting data set has 42 total pages and the page[number]= parameter is set to 7, then the response will return the block of 100 entries associated with page 7.

Function Source Code

We will need to create a custom Power Query function to process a dynamic number of pages returned by an API. This function defines one parameter named page which represents the page number to retrieve and it is used in the API query request. This function will be used in each of the three solutions.

  1. From Power BI or Excel (or any other client application that supports Power Query), select Get data and then Blank query.
  2. The Power Query window opens to a new blank query.
  3. From the Power Query Home menu, select Advanced Editor.
  4. Paste the following code into the Advanced Editor replacing any pre-existing default code.
  5. Click Done to close the Advanced Editor window.
  6. In the Query Settings pane under Properties, rename the function to GetPage.
(page as text) =>
let
    Source = try Json.Document(Web.Contents("https://api.fiscaldata.treasury.gov/services/api/fiscal_service/v1/accounting/od/rates_of_exchange?page[number]=" & page & "&fields=country_currency_desc,exchange_rate,record_date")) otherwise [data=null, meta=null, links=null],
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Removed Columns" = Table.RemoveColumns(#"Converted to Table",{"meta", "links"}),
    #"Expanded data List" = Table.ExpandListColumn(#"Removed Columns", "data"),
    #"Expanded data Records" = Table.ExpandRecordColumn(#"Expanded data List", "data", {"country_currency_desc", "exchange_rate", "record_date"}, {"data.country_currency_desc", "data.exchange_rate", "data.record_date"})
in
    #"Expanded data Records"
Power Query – Advanced Editor GetPage Function
Power Query – Advanced Editor GetPage Function

Function Test

  1. To test the function, click GetPage on the Queries pane.
  2. The Enter Parameter screen is displayed with a text field corresponding to the page parameter.
Power Query – GetPage Function
Power Query – GetPage Function
  1. Enter a valid page number index. Invalid values will return a null table.
  2. Click the Invoke button.
Power Query – GetPage Function Test Value
Power Query – GetPage Function Test Value
  1. The Invoked Function returns a table containing the Country – Currency Description, Exchange Rate, and Record Date entries from the supplied page number index as expected.
Power Query – Invoked GetPage Function Test
Power Query – Invoked GetPage Function Test

At this point we have a function that is able to retrieve a single indexed page within the result set. We can now use this function to iterate through each indexed page to dynamically retrieve and aggregate all of the paginated results.

Retrieving All Pages

This particular API conveniently returns the total number of pages as part of its JSON response which makes page retrieval fairly easy. Many other APIs are not as helpful. Some APIs may only return the total number of entries in the response. Other APIs may not return any counts. For those less accommodating APIs, I have provided alternate solutions to either calculate pages or retrieve pages dynamically without meta counts.

  1. From the Power Query Home ribbon, select New Source and then Blank Query.
  2. The Power Query window opens a new blank query.
  3. From the Power Query Home menu, select Advanced Editor.
  4. Paste one of the following code sequences into the Advanced Editor replacing any pre-existing default code.
  5. Click Done to close the Advanced Editor window.
  6. In the Query Settings pane under Properties, rename the function to Treasury Reporting Rates of Exchange.

Method 1: Number of Pages is Known Upfront

In the easiest scenario, the API response includes a count of the total number of pages. No additional calculation is needed.

let
    Source = try Json.Document(Web.Contents("https://api.fiscaldata.treasury.gov/services/api/fiscal_service/v1/accounting/od/rates_of_exchange?fields=country_currency_desc,exchange_rate,record_date")) otherwise [data=null, meta=null, links=null],
    #"Page List" = if Source = null or Source[meta] = null then {null} else {1..Source[meta][#"total-pages"]},
    #"Converted to Table" = Table.FromList(#"Page List", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "PAGES"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"PAGES", type text}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "GetPage", each if [PAGES] <> null then GetPage([PAGES]) else null),
    #"Expanded GetPage" = Table.ExpandTableColumn(#"Invoked Custom Function", "GetPage", {"data.country_currency_desc", "data.exchange_rate", "data.record_date"}, {"GetPage.data.country_currency_desc", "GetPage.data.exchange_rate", "GetPage.data.record_date"})
in
    #"Expanded GetPage"
Power Query – Retrieve Full Result Set When Number of Pages Is Known
Power Query – Retrieve Full Result Set When Number of Pages Is Known

Method 2: Number of Pages is Unknown; Number of Entries is Known

If the number of pages is unknown because the API doesn’t expose it in the meta data, then the page indexes can be calculated using other available values. If the API returns the total number of entries in the result set and the number of entries per response is known, then the number of pages in the paginated results can be calculated using simple division and rounding up. The bulk of this code is the same as above. The relevant change is found in step #”Page List”. In this step the dynamic list generation is changed to {1..Number.RoundUp(Source[meta][#"total-count"] / Source[meta][#"count"])} which calculates the number of pages by dividing the total-count (number of entries in the result set) by count (number of entries per page). The Number.RoundUp function is then used to account for partial pages by rounding the remainder up to the next integer. In other words, if the total number of entries in the result set is not evenly divided by the total entries per page, then the remainder will be on a partial page. As an example, if there are 205 total entries with 100 entries per page, then the number of pages should be 3 to account for the 5 entries above 200. This calculation would need to be adjusted if the API pagination started at zero or some other number.

let
    Source = try Json.Document(Web.Contents("https://api.fiscaldata.treasury.gov/services/api/fiscal_service/v1/accounting/od/rates_of_exchange?fields=country_currency_desc,exchange_rate,record_date")) otherwise [data=null, meta=null, links=null],
    #"Page List" = if Source = null or Source[meta] = null then {null} else {1..Number.RoundUp(Source[meta][#"total-count"] / Source[meta][#"count"])},
    #"Converted to Table" = Table.FromList(#"Page List", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "PAGES"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"PAGES", type text}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "GetPage", each if [PAGES] <> null then GetPage([PAGES]) else null),
    #"Expanded GetPage" = Table.ExpandTableColumn(#"Invoked Custom Function", "GetPage", {"data.country_currency_desc", "data.exchange_rate", "data.record_date"}, {"GetPage.data.country_currency_desc", "GetPage.data.exchange_rate", "GetPage.data.record_date"})
in
    #"Expanded GetPage"
Power Query – Retrieve Full Result Set When Number of Pages Is Unknown
Power Query – Retrieve Full Result Set When Number of Pages Is Unknown

Method 3: Both Number of Pages and Number of Entries are Unknown

In this scenario, which is the most complex of the three, both the total number of pages nor the total number of entries are unknown. We need to use entirely different logic since the API does not expose any meta data with respect to result counts. The function List.Generate is used to iterate through pages until a null page is retrieved, i.e., the page contains an entry with null values. This condition depends on the custom GetPage function returning null when a indexed page number does not return any results. The conditional should be adjusted to the particular API.

let
    Source = List.Generate(() => [PAGE = 1, RESULTS = GetPage("1")], each Table.RowCount(Table.SelectRows(_[RESULTS], each not List.Contains(Record.ToList(_), null))) > 0, each [PAGE = _[PAGE] + 1, RESULTS = GetPage(Number.ToText([PAGE] + 1))], each _[[PAGE], [RESULTS]]),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"PAGE", "RESULTS"}, {"PAGES", "GetPage"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column",{{"PAGES", type text}}),
    #"Expanded GetPage" = Table.ExpandTableColumn(#"Changed Type", "GetPage", {"data.country_currency_desc", "data.exchange_rate", "data.record_date"}, {"GetPage.data.country_currency_desc", "GetPage.data.exchange_rate", "GetPage.data.record_date"})
in
    #"Expanded GetPage"
Power Query – Retrieve Full Result Set When Both Number of Pages and Number of Entries are Unknown
Power Query – Retrieve Full Result Set When Both Number of Pages and Number of Entries are Unknown

Code Explanation

Method 1 and Method 2

Step 1 – Source

The first step requests the API as described above. The purpose of this step is to retrieve the count and total-count from the meta record. Inspecting those values confirms the expected result found in the earlier screenshot of the direct API request. It’s worth noting that the API request is wrapped in a try...otherwise statement. If the API request fails, then null values are returned by default for the data, meta, and links records. This helps prevent the overall Power Query query and any downstream Power Query queries from failing if the API request is unsuccessful for any number of reasons, e.g., network issues, server issues, etc.

Power Query – Methods 1 & 2 – Query Step 1 – Source
Power Query – Methods 1 & 2 – Query Step 1 – Source

Step 2 – Page List

Using the value of total-count, the next step generates a list of page indexes starting from 1 and ending with the total number of pages. This particular API always begins with a page index numbered 1, but other APIs may start with a different value. This step also includes a basic error check for null values returned from a failed API request.

Power Query – Methods 1 & 2 – Query Step 2 – Page List
Power Query – Methods 1 & 2 – Query Step 2 – Page List

Step 3 – Converted to Table

The dynamic list generated in the previous step is converted to a table containing a single column of page indexes.

Power Query – Methods 1 & 2 – Query Step 3 – Converted to Table
Power Query – Methods 1 & 2 – Query Step 3 – Converted to Table

Step 4 – Renamed Columns

The single column in the table of page indexes is renamed to PAGES.

Power Query – Methods 1 & 2 – Query Step 4 – Renamed Columns
Power Query – Methods 1 & 2 – Query Step 4 – Renamed Columns

Step 5 – Changed Type

The PAGES column is changed to a text data type.

Power Query – Methods 1 & 2 – Query Step 5 – Changed Type
Power Query – Methods 1 & 2 – Query Step 5 – Changed Type

Step 6 – Invoked Custom Function

With a list of valid page indexes, we can now call the custom GetPage function to retrieve the entries associated with a particular page from the API. From the Add Column ribbon, select Invoke Custom Function to call GetPage using the values found in the PAGES column as the argument. For each value in the PAGES column, the GetPage function is invoked and the API is requested. The new GetPage column contains a table of entries from each indexed page API request.

Power Query – Methods 1 & 2 – Query Step 6 – Invoked Custom Function
Power Query – Methods 1 & 2 – Query Step 6 – Invoked Custom Function

Step 7 – Expanded GetPage

Expanding the tables found in the GetPage column created in the prior step displays all of the expected entries.

Power Query – Methods 1 & 2 – Query Step 7 – Expanded GetPage
Power Query – Methods 1 & 2 – Query Step 7 – Expanded GetPage

Results

After returning the results from Power Query to Power BI, a simple table visualization is used to confirm that the number of pages and the number of entries match the total counts from the original API request and the code is working as expected.

Power BI – Methods 1 & 2 – Result Counts
Power BI – Methods 1 & 2 – Result Counts

Method 3

Step 1 – Source

Unlike the other methods, the total number of pages and the total number of entries are unknown. This code statement assumes the API page indexes begin at number 1. The List.Generate function uses a conditional to check for any null values in returned records. Given the way the GetPage function is coded, there should not be any null values unless the page returns an empty result set.

Power Query – Method 3 – Query Step 1 – Source
Power Query – Method 3 – Query Step 1 – Source

Step 2 – Converted to Table

The interim list of records is converted to a table of records in one column named Column1.

Power Query – Method 3 – Query Step 2 – Converted to Table
Power Query – Method 3 – Query Step 2 – Converted to Table

Step 3 – Expanded Column

The records are expanded into a column for page indexes named PAGES and a column containing tables of results per page named GetPage.

Power Query – Method 3 – Query Step 3 – Expanded Column
Power Query – Method 3 – Query Step 3 – Expanded Column

Step 4 – Changed Type

The PAGES column is changed to the text data type.

Power Query – Method 3 – Query Step 4 – Changed Type
Power Query – Method 3 – Query Step 4 – Changed Type

Step 5 – Expanded GetPage

Expanding the tables found in the GetPage column created in the prior step displays all of the expected entries.

Power Query – Method 3 – Query Step 5 – Expanded GetPage
Power Query – Method 3 – Query Step 5 – Expanded GetPage

Results

After returning the results from Power Query to Power BI, a simple table visualization is used to confirm that the number of pages and the number of entries match the total counts from the original API request and the code is working as expected.

Power BI – Method 3 – Result Counts
Power BI – Method 3 – Result Counts

Mapping Cities in Power BI

As I wrote in the post Mapping Continents and Countries in Power BI, there are complexities with mapping geographic data using the Map and Filled Map visualizations in Power BI. The complexity only increases as you move into further levels of granularity. Unlike continent-level and country-level data, city-level data contains many locations that share the same name. In order for Power BI to render a map visualization as expected, additional supporting data points must be provided to the Bing mapping service so that the correct location is identified on the map.

There Are How Many Cities Named Newark?

To demonstrate city-level mapping complexity in Power BI, the city of Newark is used in the following examples. Why Newark? Well, if you’re familiar with New Jersey or at least its international airport, then you may be aware that Newark is one of the oldest cities in the United States and it is the most populous city in the state. While it is certainly an important city, I didn’t pick it for that reason. Newark is complicated from a mapping perspective because there are many cities named Newark. I was only aware of one other in Delaware, but there are actually at least 16 other cities and towns named Newark across the United States and at least 2 in the United Kingdom.

The following table represents the data used in these examples. This isn’t a complete inventory of all cities named Newark, but it does serve as a sufficient subset for demonstration purposes.

COUNTRYSTATE / PROVINCECITYLATITUDELONGITUDE
United KingdomNottinghamshireNewark53.076111-0.809167
United KingdomPeterboroughNewark52.590833-0.209722
United StatesArkansasNewark35.701667-91.441389
United StatesCaliforniaNewark37.533333-122.033333
United StatesDelawareNewark39.683611-75.749722
United StatesIllinoisNewark41.536389-88.580833
United StatesIndianaNewark39.128889-86.807222
United StatesMarylandNewark38.265278-75.271667
United StatesMissouriNewark39.993333-91.973333
United StatesNebraskaNewark40.641111-98.963056
United StatesNew JerseyNewark40.72422-74.172574
United StatesNew YorkNewark43.046667-77.095278
United StatesOhioNewark40.063056-82.416667
United StatesSouth DakotaNewark45.929444-97.791389
United StatesTexasNewark33.013611-97.488333
United StatesVermontNewark44.702222-71.942778
United StatesWest VirginiaNewark39.118889-81.3975
United StatesWisconsinNewark42.534722-89.179722
Power BI – Location Data Table
Power BI – Location Data Table

Data Setup

After connecting Power BI to the underlying data source, the columns are configured as follows.

Country

  1. From the Fields pane, select COUNTRY.
  2. Set Data type to Text.
  3. Set Format to Text.
  4. Set Summarization to Don’t summarize.
  5. Set Data category to Country.
Power BI – Column Tools Settings (Country Data)
Power BI – Column Tools Settings (Country Data)

State

  1. From the Fields pane, select STATE.
  2. Set Data type to Text.
  3. Set Format to Text.
  4. Set Summarization to Don’t summarize.
  5. Set Data category to State or Province.
Power BI – Column Tools Settings (State Data)
Power BI – Column Tools Settings (State Data)

City

  1. From the Fields pane, select CITY.
  2. Set Data type to Text.
  3. Set Format to Text.
  4. Set Summarization to Don’t summarize.
  5. Set Data category to City.
Power BI – Column Tools Settings (City Data)
Power BI – Column Tools Settings (City Data)

Latitude

  1. From the Fields pane, select LATITUDE.
  2. Set Data type to Decimal number.
  3. Set Format to General.
  4. Set Summarization to Don’t summarize.
  5. Set Data category to Latitude.
Power BI – Column Tools Settings (Latitude Data)
Power BI – Column Tools Settings (Latitude Data)

Longitude

  1. From the Fields pane, select LONGITUDE.
  2. Set Data type to Decimal number.
  3. Set Format to General.
  4. Set Summarization to Don’t summarize.
  5. Set Data category to Longitude.
Power BI – Column Tools Settings (Longitude Data)
Power BI – Column Tools Settings (Longitude Data)

Method 1: Mapping Using City Name Only

Spoiler Alert – Rendered incorrectly

To demonstrate the complexity of city-level mapping in Power BI, a Map visualization is added to a report and configured as follows. In the Location configuration for the map, only the CITY field is used. In the Bubble size configuration for the map, Count of CITY is used.

Power BI – Map Settings Based On City Name Only
Power BI – Map Settings Based On City Name Only

The map is rendered with all 18 cities named Newark mapped to Newark, New Jersey. This is obviously incorrect when all of the other supporting data points from our source data are considered. Since city name is the only information provided to the visualization, the mapping service assumes all entries are referring to the largest or most well-known city named Newark.

Power BI – City Name Only Mapping
Power BI – City Name Only Mapping

Method 2: Mapping Using City Name, Latitude, and Longitude

Spoiler Alert – Rendered correctly

The map is rendered correctly by adding the latitude and longitude fields to the map configuration in addition to the city name. In the Latitude configuration for the map, the LATITUDE field is added. In the Longitude configuration for the map, the LONGITUDE field is added. The CITY field is added to the Tooltips configuration otherwise the tooltip only displays the latitude and longitude coordinates.

Power BI – Map Settings Based On City, Latitude, and Longitude
Power BI – Map Settings Based On City, Latitude, and Longitude

The map is rendered at the city level with the bubbles in the expected locations.

Power BI – Map Based On City, Latitude, and Longitude
Power BI – Map Based On City, Latitude, and Longitude

Method 3: Mapping Using State/Province and City Name

Spoiler Alert – Rendered incorrectly at the State/Province level; rendered correctly at the city level

If we change the map configuration to consider the state/province data in addition to the city name, then we find an improvement in the rendered map. However, the map is still incorrect. In the Location configuration for the map, the STATE field is added above the CITY field. The latitude and longitude fields from the prior example are removed.

Power BI – Map Settings Based On State and City
Power BI – Map Settings Based On State and City

At first glance, the map appears to be correct. Upon further inspection, notice that only one location is identified in the United Kingdom instead of the two locations found in the data table. There is also a bubble incorrectly placed in Canada. This discrepancy is due to Newark, Peterborough, United Kingdom. By only supplying the values Peterborough and Newark, the mapping service assumes the value Peterborough is referring to a location in Ontario, Canada instead of the United Kingdom. So, at the state/province level, the map is incorrect.

Power BI – Map Based On State and City (State Level)
Power BI – Map Based On State and City (State Level)

Let’s expand to the city-level data by clicking the Expand all down one level in the hierarchy button. Do not use the Go to the next level in the hierarchy as that will ignore the state/province information entirely and render the map as if only city name was provided.

Power BI – Expand All Down One Level In The Hierarchy
Power BI – Expand All Down One Level In The Hierarchy

The expanded map at the city level is rendered correctly. The mapping service considers the state/province data as it evaluates the city names. The Newark, Peterborough data point is correctly rendered in the United Kingdom. All other data points are mapped correctly.

Power BI – Map Based On State and City (City Level)
Power BI – Map Based On State and City (City Level)

Method 4: Mapping Using Country, State/Province, and City Name

Spoiler Alert – Rendered correctly at all levels (Country, State/Province, and City)

Let’s change map configuration to additionally consider the associated country with the state/province data and the city name. In the Location configuration for the map, the COUNTRY field is added above the STATE field.

Power BI – Map Settings Based On Country, State, and City
Power BI – Map Settings Based On Country, State, and City

At the country level, the map is rendered correctly with 16 locations in the United States and 2 locations in the United Kingdom. No locations are identified in Canada.

Power BI – Map Based On Country, State, and City (Country Level)
Power BI – Map Based On Country, State, and City (Country Level)

Let’s expand to the state/province-level data by clicking the Expand all down one level in the hierarchy button. Do not use the Go to the next level in the hierarchy as that will ignore the country information entirely.

Power BI – Expand All Down One Level In The Hierarchy
Power BI – Expand All Down One Level In The Hierarchy

At the state/province level, the map is rendered correctly. Unlike the prior example where country data was not used, both United Kingdom locations are mapped to the United Kingdom. The issue found with Newark, Peterborough does not occur when country information is provided in the hierarchy.

Power BI – Map Based On Country, State, and City (State Level)
Power BI – Map Based On Country, State, and City (State Level)

Expanding to the lowest level (city level), the map continues to be rendered as expected. No issues are identified with the mapped locations.

Power BI – Map Based On Country, State, and City (City Level)
Power BI – Map Based On Country, State, and City (City Level)

Method 5: Mapping Using a Country, State/Province, and City Hierarchy

Spoiler Alert – Rendered correctly at all levels (Country, State/Province, and City)

For completeness, I’ll also demonstrate mapping using a defined hierarchy of the Country, State, and City data columns.

Use the following steps to add the hierarchy to the model.

  1. In the Fields pane, click the ellipsis (three dots) next to the COUNTRY field.
  2. Select Create hierarchy.
  3. A new hierarchy field named COUNTRY Hierarchy is added to the model.
  4. In the Fields pane, click the ellipsis (three dots) next to the STATE field.
  5. Hover over Add to hierarchy and select COUNTRY Hierarchy.
  6. In the Fields pane, click the ellipsis (three dots) next to the CITY field.
  7. Hover over Add to hierarchy and select COUNTRY Hierarchy.
  8. In the Fields pane, click the ellipsis (three dots) next to the COUNTRY Hierarchy field.
  9. Select Rename.
  10. Change the name to GEOGRAPHY Hierarchy.

In the Location configuration, remove any existing fields and add the GEOGRAPHY Hierarchy field instead.

Power BI – Map Settings Based On Hierarchy
Power BI – Map Settings Based On Hierarchy

At the country level, the map is rendered correctly with 16 locations in the United States and 2 locations in the United Kingdom. No locations are identified in Canada.

Power BI – Map Based On Hierarchy (Country Level)
Power BI – Map Based On Hierarchy (Country Level)

Let’s expand to the state/province-level data by clicking the Expand all down one level in the hierarchy button. Do not use the Go to the next level in the hierarchy as that will ignore the country information entirely.

Power BI – Expand All Down One Level In The Hierarchy
Power BI – Expand All Down One Level In The Hierarchy

At the state/province level, the map is rendered correctly. Both United Kingdom locations are mapped to the United Kingdom and the issue found with Newark, Peterborough does not occur when country information is provided in the hierarchy.

Power BI – Map Based On Hierarchy (State Level)
Power BI – Map Based On Hierarchy (State Level)

At the country level, the map is rendered correctly with 16 locations in the United States and 2 locations in the United Kingdom. No locations are identified in Canada.

Power BI – Map Based On Hierarchy (City Level)
Power BI – Map Based On Hierarchy (City Level)

Further Reading

For additional information on Power BI mapping, please consider reading the related post discussing Mapping Continents and Countries in Power BI.

Mapping Continents and Countries in Power BI

Maps – they’re everywhere. We have instant access to detailed electronic maps directly from our phones, tablets, computers, and cars. For some, old and usually outdated maps are used as artwork to hang on walls. For others, the humongous folded paper maps found in a car door side pocket are still the go-to map when electronic maps are not available (just remember to update it periodically as roads may have changed during the past 50 years). From a technology standpoint, the coronavirus pandemic demonstrated the critically important need for digital mapping services. Maps relayed information about COVID-19 case levels, hospitalizations, deaths, and vaccine availability in a familiar and easy-to-use visualization. Vast amounts of data from all over the world were consolidated and made available at aggregate global and granular local levels through maps.

Given the ubiquity of maps, I incorrectly assumed that including a map visualization in Power BI would be a simple exercise. Well, adding the visual itself is very easy, but having the data rendered correctly is not as straightforward. This isn’t entirely the fault of Power BI (there are some Power BI specific issues). I was quickly reminded that the world is a complicated place. Continents, regions, countries, and cities may be referred to by different names. Complex geopolitical factors play into the recognition of a country as a sovereign entity or as a territory of another entity. There are disputed lands that may or may not be recognized as sovereign entities or they may be considered parts of multiple different countries depending on political perspective. Even assigning a particular country to a continent is not simple since some countries span multiple continents while other countries bordering multiple continents may prefer to be recognized as part of one continent over another regardless of geological assignment.

In short, mapping is a challenging exercise no matter what service is used. As I mentioned earlier, these challenges are not specific to Power BI. As a Microsoft product, Power BI uses Bing for its mapping services. This adds another layer of complexity since Bing may recognize locations differently than Google Maps or any other service in terms of location naming, borders, and political recognition. From a continent and country naming perspective, the examples below were tested with Power BI.

Enabling Map Visuals in Power BI

Power BI map visualizations are not enabled by default due to security reasons. Data used to render maps in Power BI are transmitted externally since Power BI uses Bing as its mapping service. If the report or dashboard contains sensitive information that should not be transmitted externally, you may want to consider an alternate solution.

To use the Map or Filled Map visualizations, you may need to enable a security option if the error “Map and filled map visuals are disabled” is displayed (screenshot below).

  1. From the Power BI application menu, click File.
  2. Click Options and settings.
  3. Click Options. The Options window is displayed.
  4. Within the Global section, click Security.
  5. In the list of Security options, find the Map and Filled Map visuals section and enable the Use Map and Filled Map visuals option.
  6. Click the OK button to close the Options window.
Power BI – Map Security Error
Power BI – Map Security Error

Continents

The seven continents recognized by Power BI are listed below. Please be aware that the terms Australia and Oceania are not interchangeable with respect to recognized continent names. As an example, the term Australia when used as a continent name in Power BI does not fill New Zealand on a Filled Map visualization. However, the term Oceania, when set to the Data category of Continent, does fill New Zealand. My understanding is that Oceania is considered a region instead of a continent even though the terms are used interchangeably in some parts of the world.

  • Africa
  • Antarctica
  • Asia
  • Australia
  • Europe
  • North America
  • South America

Columns containing continent names should be set to the Continent data category. Click on the field containing continent names. On the Column tools menu for that column, set the Data category to Continent.

Power BI – Data Category Continents
Power BI – Data Category Continents

Map Visualization Using Continent Names

Using the listed continent names, a Map visualization places the bubbles at a central point in the expected locations.

Power BI – Continents
Power BI – Continents

Filled Map Visualization Using Continent Names

Using a Filled Map visualization instead of a Map visualization produces some unexpected results. All of the continents are filled as expected, however, Asia appears to be filled more than once since it has a darker fill color in the following screenshot. Hovering over Asia displays the label Europe. It seems Asia is filled based on both the Asia and Europe continent names.

Power BI – Continents (Filled)
Power BI – Continents (Filled)

Another peculiarity with the Filled Map visualization is that some island nations and territories are not filled when using continent names. As an example, Cuba, Haiti, and Dominican Republic are filled as expected but Jamaica is not filled.

Power BI – Unfilled Islands
Power BI – Unfilled Islands

Countries

Mapping country data is where the process starts to become really interesting and problematic. I searched for a reference list of valid country names for Power BI and could not find a single, consistent reference. Without delving into geopolitics, there are many partially recognized countries, disputed lands, and territories. Countries found on one reference list may not be found on other lists if the country/sovereign is not recognized by the organization maintaining the list. Also, simply finding a country listed on a country reference list does not guarantee that Power BI recognizes the country as part of its mapping service with Bing. Let’s not forget that naming is not consistent either as countries tend to have various official and unofficial names that may or may not be recognized by Power BI or Bing. Is it the United States of America or United States or U.S. or U.S.A. or America?

In no way do any of the following country name lists represent an official or accurate or complete country name reference. They only serve to provide a list of tested names (at the time this was written) that are generally recognized by the Power BI mapping service when the Data category is set to Country. From the reference lists I found, these country names were commonly found as recognized countries. If you think a country name is missing, then feel free to add it to your report and make sure it is recognized by Power BI. If you feel a country belongs to Asia instead of Europe, then please make that adjustment in your report and test.

I also included a list of additional names that the Power BI mapping service recognizes when the Data category is set to Country. Again, this is not an accurate or complete list so please use it with caution, e.g., Puerto Rico is recognized when categorized as a Country in Power BI but it is regarded as a territory of the United States. Generally, these entries tend to be islands, partially recognized states, disputed lands, territories of other countries, etc.

Power BI – Countries
Power BI – Countries

Columns containing country names should be set to the Country data category. Click on the field containing country names. On the Column tools menu for that column, set the Data category to Country.

Power BI – Data Category Country
Power BI – Data Category Country

Africa

There are 54 countries associated with Africa as listed below. The Map visualization is rendered as expected, however, the Filled Map visualization appears to have unfilled areas in Sudan and South Sudan along with a few surrounding islands and territories.

  • Algeria
  • Angola
  • Benin
  • Botswana
  • Burkina Faso
  • Burundi
  • Cameroon
  • Cape Verde
  • Central African Republic
  • Chad
  • Comoros
  • Côte d’Ivoire
  • Democratic Republic of the Congo
  • Djibouti
  • Egypt
  • Equatorial Guinea
  • Eritrea
  • Eswatini
  • Ethiopia
  • Gabon
  • Ghana
  • Guinea
  • Guinea-Bissau
  • Kenya
  • Lesotho
  • Liberia
  • Libya
  • Madagascar
  • Malawi
  • Mali
  • Mauritania
  • Mauritius
  • Morocco
  • Mozambique
  • Namibia
  • Niger
  • Nigeria
  • Republic of the Congo
  • Rwanda
  • São Tomé and Príncipe
  • Senegal
  • Seychelles
  • Sierra Leone
  • Somalia
  • South Africa
  • South Sudan
  • Sudan
  • Tanzania
  • The Gambia
  • Togo
  • Tunisia
  • Uganda
  • Zambia
  • Zimbabwe
Power BI – Africa
Power BI – Africa
Power BI – Africa (Filled)
Power BI – Africa (Filled)

Asia

There are 47 countries associated with Asia as listed below. The Map visualization is rendered as expected, however, the Filled Map visualization appears to have unfilled areas along the border of China with India, Kyrgyzstan, and Kazakhstan as well as unfilled surrounding islands and territories. There are also unfilled areas with respect to Israel and Palestine.

  • Afghanistan
  • Armenia
  • Azerbaijan
  • Bahrain
  • Bangladesh
  • Bhutan
  • Brunei
  • Cambodia
  • China
  • Cyprus
  • Georgia
  • India
  • Indonesia
  • Iran
  • Iraq
  • Israel
  • Japan
  • Jordan
  • Kazakhstan
  • Kuwait
  • Kyrgyzstan
  • Laos
  • Lebanon
  • Malaysia
  • Maldives
  • Mongolia
  • Myanmar
  • Nepal
  • North Korea
  • Oman
  • Pakistan
  • Philippines
  • Qatar
  • Saudi Arabia
  • Singapore
  • South Korea
  • Sri Lanka
  • Syria
  • Tajikistan
  • Thailand
  • Timor-Leste
  • Turkey
  • Turkmenistan
  • United Arab Emirates
  • Uzbekistan
  • Vietnam
  • Yemen
Power BI – Asia
Power BI – Asia
Power BI – Asia (Filled)
Power BI – Asia (Filled)

Australia

There are 14 countries associated with Australia as listed below. Both the Map and Filled Map visualizations appear to be rendered as expected.

  • Australia
  • Federated States of Micronesia
  • Fiji
  • Kiribati
  • Marshall Islands
  • Nauru
  • New Zealand
  • Palau
  • Papua New Guinea
  • Samoa
  • Solomon Islands
  • Tonga
  • Tuvalu
  • Vanuatu
Power BI – Australia
Power BI – Australia
Power BI – Australia (Filled)
Power BI – Australia (Filled)

Europe

There are 44 countries associated with Europe as listed below. The Map visualization is rendered as expected, however, the Filled Map visualization appears to double-count Italy as represented by a darker fill color. I assume this is due to Vatican City existing within Italian borders.

  • Albania
  • Andorra
  • Austria
  • Belarus
  • Belgium
  • Bosnia and Herzegovina
  • Bulgaria
  • Croatia
  • Czechia
  • Denmark
  • Estonia
  • Finland
  • France
  • Germany
  • Greece
  • Hungary
  • Iceland
  • Ireland
  • Italy
  • Latvia
  • Liechtenstein
  • Lithuania
  • Luxembourg
  • Malta
  • Moldova
  • Monaco
  • Montenegro
  • Netherlands
  • North Macedonia
  • Norway
  • Poland
  • Portugal
  • Romania
  • Russia
  • San Marino
  • Serbia
  • Slovakia
  • Slovenia
  • Spain
  • Sweden
  • Switzerland
  • Ukraine
  • United Kingdom
  • Vatican City
Power BI – Europe
Power BI – Europe
Power BI – Europe (Filled)
Power BI – Europe (Filled)

North America

There are 22 countries associated with North America as listed below. Both the Map and Filled Map visualizations appear to be rendered as expected.

  • Antigua and Barbuda
  • Bahamas
  • Barbados
  • Belize
  • Canada
  • Costa Rica
  • Cuba
  • Dominica
  • Dominican Republic
  • El Salvador
  • Grenada
  • Guatemala
  • Haiti
  • Honduras
  • Jamaica
  • Mexico
  • Nicaragua
  • Panama
  • Saint Kitts and Nevis
  • Saint Lucia
  • Saint Vincent and the Grenadines
  • United States
Power BI – North America
Power BI – North America
Power BI – North America (Filled)
Power BI – North America (Filled)

South America

There are 13 countries associated with South America as listed below. The Map visualization is rendered as expected. The Filled Map visualization has a strange blank square area within Brazil but otherwise appears as expected.

  • Argentina
  • Bolivia
  • Brazil
  • Chile
  • Colombia
  • Ecuador
  • Guyana
  • Paraguay
  • Peru
  • Suriname
  • Trinidad and Tobago
  • Uruguay
  • Venezuela
Power BI – South America
Power BI – South America
Power BI – South America (Filled)
Power BI – South America (Filled)

Other Countries, Territories, and Lands

Listed below are 51 other possible countries, territories, islands, and lands which may or may not be sovereign countries, however, Power BI and Bing recognize the names and renders them on map visualizations when the Data category is set to Country.

  • American Samoa
  • Anguilla
  • Antarctica
  • Aruba
  • Bermuda
  • Bonaire
  • Bouvet Island
  • British Indian Ocean Territory
  • British Virgin Islands
  • Cayman Islands
  • Christmas Island
  • Cocos (Keeling) Islands
  • Cook Islands
  • Curaçao
  • Falkland Islands
  • Faroe Islands
  • French Guiana
  • French Polynesia
  • French Southern and Antarctic Lands
  • Gibraltar
  • Greenland
  • Guadeloupe
  • Guam
  • Guernsey
  • Heard Island and McDonald Islands
  • Hong Kong
  • Isle of Man
  • Jersey
  • Kosovo
  • Macao
  • Martinique
  • Mayotte
  • Montserrat
  • New Caledonia
  • Niue
  • Norfolk Island
  • Northern Mariana Islands
  • Pitcairn Islands
  • Puerto Rico
  • Réunion
  • Saint Barthelemy
  • Saint Helena, Ascension and Tristan Da Cunha
  • Saint Martin
  • Saint Pierre and Miquelon
  • Sint Maarten
  • South Georgia and South Sandwich Islands
  • Svalbard
  • Taiwan
  • Tokelau
  • Turks and Caicos
  • Wallis and Futuna
Power BI – Other Countries and Territories
Power BI – Other Countries and Territories

Further Reading

For additional information on Power BI mapping, please consider reading the related post discussing Mapping Cities in Power BI.