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”
How Artificial Intelligence and Data Add Value to Businesses
Knowledge is power. And the data that you collect in the course of your business
May
Databricks Vs Synapse Spark Pools – What, When and Where?
Databricks or Synapse seems to be the question on everyone’s lips, whether its people asking
1 Comment
May
Power BI to Power AI – Part 2
This post is the second part of a blog series on the AI features of
Apr
Geospatial Sample architecture overview
The first blog ‘Part 1 – Introduction to Geospatial data’ gave an overview into geospatial
Apr
Data Lakehouses for Dummies
When we are thinking about data platforms, there are many different services and architectures that
Apr
Enable Smart Facility Management with Azure Digital Twins
Before I started writing this blog, I went to Google and searched for the keywords
Apr
Migrating On-Prem SSIS workload to Azure
Goal of this blog There can be scenario where organization wants to migrate there existing
Mar
Send B2B data with Azure Logic Apps and Enterprise Integration Pack
After creating an integration account that has partners and agreements, we are ready to create
Mar