The Bing map engine behind the map visualisation in Power BI is very intuitive allowing users to provide textual data such as City or Country or Postcode to map metrics, instead of just latitude and longitude as most other applications do. However one thing which is not immediately obvious is how to get around the issue of duplicate City/Town names.
In this blog I will explain how to map your metrics when your data source contains duplicate cities/towns.
To start with we have a simple data set with quarterly sales for 6 different cities based in 5 different states which is being loaded from a CSV into Power BI. Straight away you can see that we only have 2 distinct city names.
As soon as we try to map the sales data by city, we get an obvious problem all of the Bristol sales are being assigned to Bristol, England, while the Georgetown sales are appearing in Guyana.
Adding state to the Location field does nothing to help the problem as Power BI only reads a single input in the Location field.
So the solution is to create a new column containing both City and State data. To do this you need to complete the following steps:
1. Click “Edit Queries”
2. Select the data source in question.
3. Select the two or more columns which contain the data we want to merge eg: City and State
-If additional geographical data is available such as Country then this can be included in the merged column.
4. Navigate to the “Add Columns” menu and select “Merge Columns”
5. Choose the separator value and name the new column
For simplicity I have just called this “Merged” and separated the values using only a space.
Once the new column has been created it can be dropped into the Location field of the map visualization.
As you can see from the screenshot below I now have 6 data points, showing all three variations of Bristol, and all three variations of Georgetown.
One final tip, is to ensure you have set the Data Category value for the column in question. In this case I have set the Data Category to City to help Bing identify the type of data I believe I am providing it.
The only problem with this, is if you set the Data Category value incorrectly no data will be displayed as shown in this final screenshot where I have changed the Data Category to “Continent”
Pareto Charts in Power BI and the DAX behind them
The Pareto principle, commonly referred to as the 80/20 rule, is a concept of prioritisation.
Apr
Databricks: Cluster Configuration
Databricks, a cloud-based platform for data engineering, offers several tools that can be used to
Apr
AI Assistance in Microsoft Fabric
The exponential growth of Large Language Models (LLMs) couples with Microsoft’s close partnership with OpenAI
Apr
10 reasons why it’s worth the effort to understand the value of your data
“If leaders really want to create a data driven culture, the journey starts with them!
Apr
Content Safety in Azure AI Studio
Azure AI Content Safety is a solution designed to identify harmful content, whether generated by
Apr
Model Benchmarks in Azure AI Studio
In the constantly changing field of artificial intelligence (AI) and machine learning (ML), choosing the
Apr
Celebrating International Women’s Day: from Classroom to Code
As we celebrate International Women’s Day, I want to share my journey of breaking stereotypes
Mar
Pretty Power BI – Adding Pagination to Bar Charts
Good User Experience (UX) design is crucial in enabling stakeholders to maximise the insights that
Feb