Have you ever looked at your business data and thought “Gosh, wouldn’t it be great if I could get this on a map without looking up addresses and geocoding every single entry?” I sure have! Luckily for you (and for me), ArcGIS for Power BI allows you to join layers so you can spatially enable your business data. Enhancing your data with location information allows for deeper analysis, understanding, and more elegant reporting.
You can use a layer join to attach new data to your original data so it can be analyzed in a single layer. A layer join takes two separate data tables and brings them together using a common attribute. This shared data attribute is used to determine which attributes to attach to which records. All you need to get started is a reference layer with location information, like an address or ZIP code, and Power BI data.
Here’s an example
I am exploring whether vaccination rates in public schools can help identify districts that are good targets for public health outreach on early childhood health. I have a dataset in Power BI with information on vaccinations and exemptions for each school in the city. But the vaccination table only lists each school’s name. No location—big problem. I can’t easily get this data onto a map.
However, I also have a layer in ArcGIS that includes both the names and coordinates of each school. Using ArcGIS for Power BI, I can join the data sets by their shared attributes (in this case, the school name) and associate my vaccination data with each school’s location.
Once the layer join is made, I will be able to use the map to make selections and see dynamic interactions with the other visuals on my Power BI report. The thought alone sets my heart aflutter with excitement! Let’s get to work.
Step 1: Choose a field to use for the join
My vaccination data is set up in Power BI and I have added a map to my report. To start, I choose a field to use for the layer join. Since both datasets include the school name, I click the ArcGIS for Power BI visual and drag the NAME field from my Power BI dataset into the Join layer field well.
Step 2: Add a reference layer
Next, I add the reference layer to my map. All Power BI users can add public ArcGIS content and a set of curated layers containing United States demographic data. If you’re signed in to ArcGIS, you can also add content from your ArcGIS organization (which is where my layer is) and from Living Atlas.
When choosing a reference layer for a layer join, make sure it contains attributes that match the ones in your Power BI dataset. In this case, if I add a layer that doesn’t contain the school names, my layer join won’t work.
Step 3: Create a layer join
Now that I’ve added a reference layer, I select the Layers list from the map tools. The Join layer option appears in the reference layer’s context menu—and this is where the magic happens.
Once I select Join layer, I choose the field in the reference layer that matches my Power BI data. The field name doesn’t have to match exactly, but the data attributes must be of the same type. For example, I can only join numbers to numbers, strings to strings, and so on.
Finally, I click Create join. And now, the final reveal. Drumroll, please…
Step 4: Enjoy your spatially enabled data!
Now that the information in my Power BI report is linked to the locations on the map, I can use the Join layer selection tools to highlight schools on the map and get targeted, specific information in my report’s other visuals.
Likewise, the map responds to my interactions with the other report visuals, filtering down to display only the selected locations.
If you have important data with partial or no location information, use ArcGIS for Power BI to join layers. Then showcase all your insights in a beautiful and interactive map-enabled report. You can perform layer joins with any Power BI dataset and ArcGIS reference layer that have data attributes in common. Think ZIP codes, cities, neighborhoods, counties, states, business names—the possibilities are truly endless!
Learn more about getting started with ArcGIS for Power BI and how you can integrate ArcGIS across your Microsoft 365 products.
Article Discussion: