Save content
Have you found this content useful? Use the button above to save it to your profile.
Map
iStock_Maxger_AW

On the map: How to map address data in Power BI

by
8th Nov 2017
Save content
Have you found this content useful? Use the button above to save it to your profile.

If you think that your customer addresses in your accounting software are too poor to show on a map, you can think again. If they're good enough for the postman to deliver an invoice or a parcel, then they are good enough for maps in Power BI.

In this article I will share my experiences in working with Sage 50 address data in Power BI and show you some simple things you can do to start looking at your accounting data on a map.

Why put your accounting data on a map?

Here are three reasons why you may want to see your accounting data on a map:

  • To drive engagement. Many people like to look at maps. Pinning sales information to a location on a map, somehow makes this data more real than a just cold table of sales figures. These sales happened. Here!

  • To see your business from a new perspective. This might be easier to explain through a simple example. Let’s say that your overall sales numbers are up and you are happy. You put the numbers onto a map and you can see in a second that although your overall numbers are up, in the southeast your figures are down. This might be indication of new competitive activity in the southeast that is otherwise masked simply because you are not looking at your data from a geographical perspective.

  • To mashup your accounting data with other data sets. Business happens at a time and a place. I discussed in a previous article using a ‘Dates’ table in Power BI to (among other things) tie together data linked by time. You can also use mapping to to tie together data that is linked by location. This can be your own data such as the origin of your website traffic, or it can be third party datasets such as the UK census, regional business statistics, weather and so on. Whatever might be relevant to your business. This kind of mashup is not currently supported by Power BI, but there are other tools that you can use for this kind of thing and I anticipate that Power BI will support this in the future.

Common problems with Sage 50 address data

Which addresses to use?

Sage 50 stores address information all over the place. A quick review of my Sage 50 dataset in Power BI reveals 11 different places in the following tables: Customers, Sales Invoices, Sales Orders, Suppliers and Purchase Orders. For each customer you can define multiple addresses.

On a sales invoice or order you can use one of the customer addresses already set up, or enter a new one. So in theory, if you have 100,000 sales transactions you could have 100,000 addresses. So the first thing you need to decide is what addresses are you working with?

The data feed that I use for my model does provide me with all of the addresses for every sales transaction and I can use these if I want to. It also does a scan of all of the addresses at the customer level (e.g. “registered address”, “delivery address”, “billing address”) to produce a “best guess” default address for each customer in a new ‘Customer Addresses’ table. It is this table that I will use in the rest of this article, but equally it could be the ‘Customers’ table, or your ‘’Sales Invoices/Orders’ table. It depends on what is is you are trying to analyse and show.

The Countries table

Sage 50 also has a ‘Countries’ table that contains the full country name (the ‘Customer’ table just stores the Country Code to identify the country) and a flag to identify if that country is in the European Union.

You will need to create a relationship between this table and the table with the rest of your customer address information, not just for aesthetic reasons (e.g. to display the full country name for an address) but also because Sage 50 uses two-character country codes (e.g “GB” for the United Kingdom) and Bing Maps (the mapping engine behind Power BI) needs either the full country name or an ISO 3-letter country code (e.g. “GBR” for the United Kingdom).

Once I have created the link between [Country Code] in my ‘Countries’ table and my ‘Customer Addresses’ table I create a new calculated column [Country] in ‘Customer Addresses’.

Country = RELATED(Countries[Country])

Technically I don’t have to do this but for ease of use within my model I think it is nice to have all of the address information together in one table.

Poorly structured addresses

This is probably the most obvious problem that I have come across when working with Sage 50 address data. In Sage 50 an address has six fields:

  • [Address 1],

  • [Address 2],

  • [Address 3],

  • [Address 4],

  • [Address 5],

  • [Country Code].

It is extremely common for there to be major inconsistencies over how each of these fields are used. Although in the Sage 50 screen where you define a new address, you are encouraged to follow a certain structure, from what I have seen this is often not followed.

What I have seen generally is that the [Country Code] data is good, but the other fields are all over the place. The most common problem is a completely inconsistent use of the same address fields for the same purpose. For example [Address 4] might represent “City” in one line, “County” in another, “Post Code” in another and may be blank in another.

Inconsistencies like this do limit what you can do with maps, but they are no longer show-stoppers.

Perfectly-structured address data is no longer so important

Not so long ago it would have been really important for you to have well-structured and consistent address data if you wanted to show it on a map. This is not really the case anymore.

Think about the human interaction with Google or Bing Maps. If you want to look up an address on Google or Bing Maps you don’t get it into a nice clean yet fixed structure first. No. You just start typing, with omissions and spelling mistakes. To find my office in Bing on a map of Dublin (Ireland), all I need to type is “4 har” and Bing does the rest.

I don’t need to specify that “4 Harbourmaster Place” is [Address 1], “Dublin 1” is [Address 3] (or is it?) for example. I can just throw what I know about the address to Bing Maps or Google Maps and the rest is done for me. You can do the same with address data in Power BI and this solves the problem of your inconsistent use of address fields.

Just throw a single address string at Bing

Now what you can do is to create a single [Address] field that you can just throw at the Bing Maps engine for geo-coding and displaying on a map visualisation.

The simplest way to do this is simply to concatenate the address fields together like this:

Address =
[Address 1]&
", "&[Address 2]&
", "&[Address 3]&
", "&[Address 4]&
", "&[Address 5]&
", "&[Country]

This will produce a single field with all the address information in it that Power BI can now throw to Bing Maps and it will work fine (see below about categorising this field as an “address”).

This will display on a map ok:

However,the text of the address can look a bit ugly with the rather awkward leading commas and double commas that are there because of blank address fields in the dataset.

With some extra text manipulation though you can get rid of these. There may be a more elegant way of doing this (please comment if you have a better suggestion), but what I did was the following six steps to create my nice clean [Address] field:

  1. To test if Address 1&2 are blank:

    AND Addr1 & Addr2 is Blank = AND(ISBLANK([Address 1]),ISBLANK([Address 2]))
     

  2. To test if Addresses 1,2 & 3 are blank:

    AND Addr1 & Addr2 and Addr3 is Blank = AND([AND Addr1 & Addr2 is Blank],ISBLANK([Address 3]))
     

  3. To test if Addresses 1,2,3 & 4 are blank:

    AND Addr1 & Addr2 & Addr3 & Addr4 is Blank = AND([AND Addr1 & Addr2 and Addr3 is Blank],ISBLANK([Address 4]))
     

  4. To test if Addresses 1,2,3,4 & 5 are blank:

    AND Addr1 & Addr2 & Addr3 & Addr4 & Addr5 is Blank = AND([AND Addr1 & Addr2 & Addr3 & Addr4 is Blank],ISBLANK([Address 5]))
     

  5. Create [Interim Address] (which may still have a leading comma if Address 1 is blank:

    Interim Address =

    IF([AND Addr1 & Addr2 & Addr3 & Addr4 & Addr5 is Blank],LOOKUPVALUE(Countries[Country],Countries[Country Code],[Country Code]),
    IF([Address 1] = BLANK(),BLANK(),[Address 1])
    &IF([Address 2] = BLANK(),BLANK(),", "&[Address 2])
    &IF([Address 3] = BLANK(),BLANK(),", "&[Address 3])
    &IF([Address 4] = BLANK(),BLANK(),", "&[Address 4])
    &IF([Address 5] = BLANK(),BLANK(),", "&[Address 5])
    &IF([Country Code] = BLANK(),BLANK(),", "&[Country]))
     

  6. Then finally to test if I have a leading “, “ that I need to get rid of in the case where [Address 1] is blank:

    Address =
    IF(LEFT([Interim Address],1)=",",
    REPLACE([Interim Address],1,2,BLANK()),[Interim Address])

Now this is a bit convoluted, but it works and it produces a nice text address field with each line separated with a comma and a space that you can display in your visualisations.

Define your Address field(s) as as location information in Power BI

The next thing you need to do is to tell Power BI that your address field(s) hold location information. To do this, select the address field and in the modeling tab chose “Data Category”

 

Most of your Sage 50 data will be “Uncategorized”, but it is here where you can tell Power BI that the data relates to an address.

Power BI gives you the following options as categories of location information:

  • Address

  • City

  • Continent

  • Country/Region

  • County

  • Latitude

  • Longitude

  • Place

  • Postal Code

  • State or Province

Now this is where my poorly-structured Sage 50 address data lets me down. My only options are to categorise my new field [Address] as “Address” and [Country] as “Country/Region”.

If you have structured and populated your Sage 50 address data well and as per the address data entry screen, what I would do is to rename the address fields and add categories to them as follows:
 

Field

New name

Power BI Category

Address 1

Street 1

 

Address 2

Street 2

 

Address 3

Town

City

Address 4

County

County or State/Province

Address 5

Postcode

Postal Code

This would give me more options for analysing my data by location. I could for example use filled maps showing sales by country with a drill-down to county.

But still, what I have with the new [Address] field is still really powerful and I don’t have to do anything (or very little) to clean up my Sage 50 address data. I can now plot every customer address on a map and produce some engaging visualisations.

Map visualisation options

Now that I have my Sage 50 address data in a form that Bing Maps can work with, I can start producing some engaging visualisations. Power BI supports three types of map visualisation: “Map”, “Filled Map” and “ArcGIS Maps for Power BI”.

Map

This is great for showing points on a map. These points can be sized and coloured based on the data they are representing which is means that you can look at two dimensions of your data at the same time - for example the size of the point could be “Sales” and the colour could represent “Margin %”.

The Map visualisation is therefore great at analysing small numbers of important customers, but not so good at at viewing hundreds or thousands of small customers. In such a case you just end up with too many points on a map.

Picture2.png

Filled Map

A Filled Map is good for analysis by geographic region - for example by sales by county. This is a great option if you are dealing with too many points to display using the “<ap” visualisation. You can only display one value though through the shade of the filled areas. You can’t for example display “Sales” and “Margin” on the same map. You also have to bear in mind that shading is important on a filled map because the size of the shaded area relates to the size of the geographic area. Without shading, a small sales volume in a large county like North Yorkshire may look more important than a high sales volume in Greater Manchester. To use the filled map option effectively, you will need good quality, consistently-structured address data (for example you always use [Address 4] for “County”. You will then be able to apply the category “County” or “State or Province” to the field and add this as a drill-down to your visualisation.

Picture3.png

ArcGIS Maps for Power BI

ArcGIS Maps for Power BI is a visualisation option provided by the geospatial specialist company ESRI. This visualisation is quite heavy on memory but gives you a number of options that you may find better than the standard Map and Filled Map options from Power BI. This visualisation has many interesting features and I have highlighted just two below that I think could be particularly useful when analysing Sage 50 accounting data.

Heat map

Picture1.png

A Heat Map is a nice alternative to the Filled Map where you don’t have very well structured address data.

Picture1.png
 

Clustering map

A Clustering Map produces a nice uncluttered counts of data points by geographical area. It is a good clean way to display activity volume by area.

Useful links

Tags:

Replies (1)

Please login or register to join the discussion.

avatar
By farrside21
18th Dec 2018 16:05

One alternative might be to export the raw address data from Sage and load it into Aperture Data Studio. This will compare your address data to that on the Post Office Address File and make necessary corrections and completions. Associated with this batch verification are geocodes that can be appended to every address line (creating 2 columns of extra data). Export to Power BI. Power BI should recognise these automatically as coordinates and plot your map.

Even better is that you will have a corrected set of addresses to load back into your accounts system.

Thanks (0)