PL-300 – Section 7: Part 1 Level 6: Mapping

  1. Maps

Hello and welcome to level six. And in level six, we’ll be looking at maps. And we’ll be looking at performance indicators. So, in this particular section, we’re going to have a look at maps.

So, we’ve been looking at how we can have the six different regions; Greater Manchester, Merseyside, South Yorkshire, Turin Ware, West Midlands and West Yorkshire, being shown in different visualisations. But these of course, are geographic locations. How can we put them onto a map?

Well, there are two different types of map. There’s more than two, but there’s two principal ones, Map and Filed map. And this video is all about Map. So, if I put in Map, maximize it down to that, and I’m going to put some information on these wells. So, we’ve got a location well, so this is your geographic data, but it need it to just be the only geographic data because you’ve also got latitude and longitude. Additionally, we’ve got Legend, Size and Tooltips. So, I’m going to put Region Name into Location and instantly you can see that the computer has identified, that is part of England, and it’s put them in the right sort of place.

Now, West Yorkshire, obviously, is not just a specific location. It’s an entire state, it’s an entire county. But it’s put it into a fairly good location.

Now, these interact with other things that we’ve set up before, like for instance, drill-free reports. So, if I right and click and drill through, you can see, for instance, this is another way of being able to select areas to have a look at and in more detail with some of your other visualisations. So, I’m going to rename this as Maps of Admin Areas.

Now, all of these circles are the same size. We haven’t put any values into them. So, what we’re going to do is, put in the sum of the sales value. So, this is the total sales value for all time and into the size section. And there you can see the size has now changed. So, instantly, we can see that the Greater Manchester one, biggest, and then West Yorkshire and then the West Midlands.

Now, you are relying on the computer to be able to identify these locations and it doesn’t always work, at least not the first time. If you want to be more specific, you can have fields, which are latitude and longitude. So, these will uniquely identify the place on the planet Earth. We’ve also got Legend; these will change these circles into pie charts. So, if I put in the year of date, for instance, then you can see what I’m talking about.

Now, again, with pie charts, we’ve got lots of different values here, where we’ve got 22 different values. I wouldn’t recommend using pie charts if you’ve got more than six different values. So maybe instead of putting the year, we’ll put the quarter and then we can see which particular quarter might be the biggest for sale. So, here, for instance, in Greater Manchester, in the third quarter, it looks like that is the biggest for sales. In the first quarter, the smallest for sales. And then you can visually compare and contrast the size, as well as where they are.

Now, let’s put in a nova example of maps. So, I’m going to use some different data from our original spreadsheet. So, we’ve basically only use the one source of data so far. So, I’m going to use this. This shows the number of people in each particular area of Afghanistan. This was just downloaded from Wikipedia or something similar.

So, let’s get the data for this. So Get Data, Excel, Source data, and is accord Afghanistan. And I’m just going to load this data. So, it’s adding it into the model. I’m not going to be connecting these two together. We’ll be looking at connecting different sources of data together in a later part of this course. For now, we’re looking at how to visualise data. So, if we go to the Afghanistan data, create a new page, and we’re going to call this Maps Afghanistan.

Okay, so let’s drag it in the map field. I should just click on the map field rather and drag it so that’s a comfortable size. And I’m going to put the number of people into size. And it has no idea where I’m talking about, so it’s just shown me a map of the world. And we’re going to put all of these particular provinces into location.

Okay, you can see that we’ve got a problem. This is meant to be data of Afghanistan, but there’s a fair bit near Afghanistan. I’m not sure any of them are actually in Afghanistan. Oh, this is a huge area there. But we’ve got areas in India and in Iran and Oman, and Syria, and Russia and even one over here in Ireland. So having name, by itself may not be the best thing. So, I’m going to create one more map, we’re going to see how we can improve on the situation in the next video.

And this is going to be a map of the US. So again, if I get more data, and we have a look at one called States, and these are the 50 states of the United States, including Hawaii and Alaska, and it shows the population. So, I’m going to load that data in. So, it’s essentially the same sort of information. It does have country. I’m not going to use country at the moment. I’m just going to do exactly what we’ve done for Afghanistan. I’m going to add a map.

I am going to put the state in location. And you can see that the computer has correctly identified all of these states as being in the United States. Whereas it couldn’t identify all of these particular areas as being in Afghanistan. And so I’m now going to add in said Population into Size. And there you can see, for instance, California, one of the biggest states by population, New York, near half the size of Texas, 26 million, so it probably is the biggest state, just on a visual field.

  1. Formatting Maps

Now, let’s have a look at what we’ve got here in the formatting section.

So, we have got data colours. So, we’ve got a default colour, so you can change whichever colour you want or you can have a look at all of the colours. And say I want to change one particular colour, I’ll just expand this so you can also see category labels so if you wanted to show what state it is, I think, personally, that’s just a tad too small. I would prefer as few of labels but to actually be able to read them.

And, again, you can adjust various things like the colour and the font. The bubbles, this shows the size of your bubbles relative to a standard. So, one is the standard, but you can make the all a bit smaller. It’s easier to show if I’d dotted the category labels on all of them a bit bigger. So, that’s to say one is the default. Map controls. There are two map controls in here, Auto Zoom which is on by default. So, that gets you to the right zooming depending on what data you are looking at but I also like to have the zoom buttons on. They’re up here and they just give a visual indication on how to zoom in and out.

So yes, you can use the scroll and you can just scroll using the mouse left and right, and up and down, north and south, east and west. Use the scroll wheel to go in and out but I prefer actually to have a visual indication of how to zoom in and out as well. Map styles, there are five different map styles. The world map style, this probably makes more sense if I zoom into a particular area, so like California.

So, here we can see the main highways, so I can change that to an aerial view. There’s California again, or a grey scale light or dark view. Those you would have to have particular use especially for the dark. It could be quite illuminating to have a dark background where you’ve a light foreground. But for me the road seems standard. Heat map, this changes you’re bubbles into, well you can see, smaller things which are darker for the higher values.

Now, I need to blow this up to actually be able to see it. So, I’ll change the radius, not that high perhaps. But if you have them overlapping as in this case you can see not just which is the biggest state, California, but what is the biggest area. So, New York, New Jersey, Massachusetts. That sort of area together has a greater number of population than California by itself. So California is the biggest state, in terms of population but it’s not necessarily the biggest area.

So again, you can change the colours for zero, 50 and 100%. So, if you wanted it to go to really dark colours and you can see a very contrasting version of where the darker colours are. Standard title at the top, again it’s not very big so I would centre and make it bigger and have a background colour. I think I’ve been using yellow, a foreground colour of black. And all the rest are just your standard formatting controls.

Now, there is one control that I have deliberately not shown you and it’s no longer here. And this particular control is much more useful I think when we get onto the third map section so I’m reserving it to a later video. But you may notice now we’ve got a heat map on that we no longer have the data colour section. So, that when I put the heat map back on it disappears. So, I’ll return to this data colours section. In the next video we’re going to have a look at how we can help tidy up this particular graph of Afghanistan.

  1. Adding Data Categories

Now, a couple of videos ago, we created this map of Afghanistan, but unfortunately, it’s not very mappy of Afghanistan. It doesn’t really look much like, is it? It’s centred around a fair area of Afghanistan, but there’s bits all over the place, and part of the problem is that the computer doesn’t know what all of these names refer to.

Now, you could have another example. Suppose you had a data source which had the American states by two letters: AL, AR, A-Zed, or are they American states, I mean AL could be Albania; AR, Argentina; CA, Canada. So, it’s helpful if you were to tell the computer what a particular field has.

Now, if I go into our Afghanistan in Name Two, in the Modelling tab, there is this Data Category section in the Properties, and it is here that we can tell the computer, well, this happens to be A. So, if I say this is a place, well, that’s no really different from what the computer has guessed, and you can see all other the place of these places. If I said there were states or provinces, then that narrows it down significantly. So, you can see, the vast majority being in Afghanistan, but still a few outside. So, with, especially, non-US, non-Western-Europe things, it may be that you have to actually just try a few of these data categories and see what works best.

Obviously, there will be some way it’s completely irrelevant to continent. The computer goes, oh, this doesn’t really look like a continent, and just try and focus on what might work best. So, it looks like the State or Province works best for this particular data set in Afghanistan.

Now, there are a couple of other places that you can set this. So, we’re currently in the report on the left-hand side. So, if I go to the Data tab, then we have the raw data. But again, here, I can click on a particular column or a particular column over here on the right-hand side, and I can say what this is going to be. So, let’s make it a city, or I could go into the Model tab, and again, I can find the particular item, and if I go down to the Advanced section, so typically, you might see this as the default with the Advanced closed. If I open up the Advanced system section, you can, again, change the data category. So, you got a couple of other options here: Web URL, so web address, Image URL, or Barcode, but the vast majority of these are to do with maps. So, my question to me is, OK, how can I actually get this to work better? Well, the computer doesn’t know that these relate to Afghanistan because I haven’t told it. And so, it’s quite properly trying to get places all over the world. So, what we really need is an Afghanistan field.

Now, I can either go back to my original data and add such a field, add such a column to the original data; or I can add one myself. So, if I click on dot-dot-dot next to the field name, I will create a new column. So, I can also do this by going into Modelling and New Column. And this new column, you can see it says, Column equals. Well, let’s call this Country, and it’s going to equal Afghanistan.

Now, I can’t really spell Afghanistan well, so what I’m going to do, because it’s the same as the source data, I’m just going to make it as a source data name, and then I’m going to put quotation marks around it and click the Commit sign, the tick mark/check mark. Right, and that’s not actually done anything visually, but what that’s done in the data is it’s just created a new column, which just says Afghanistan all the way down.

Now, as I say, I could click on a new column here, and that would do the same thing, and you can see, it’s being represented over here as well, though there’s no actual way to insert an extra column here. Even if I tried to get some room, it doesn’t work. So, but either way, I have now inserted a constant called Country. So, what I’m going to do with this constant is I’m going to drag that into the location as well, and the result, you will not be too surprised to see, or you might be, is that there is one single dot on Afghanistan, and the reason for that is that this is now a drill down report. So, I can now drill down this further to the next level in the hierarchy, and gets me back to where I am, where I was before. So, what I need to do is not drill down to the next level, but I need to expand one level down. So, once I do that, then the computer goes, ah, this is a place where the country is Afghanistan. And now you can see, everything has been hopefully correctly identified, going right into the border with Tajikistan, not quite into it.

And if I wanted, I could also categorise this as the country. So the computer knows it’s a country, or it’s worked it out, except I haven’t put Country, I’ve put County. Let’s try to change that, and this computer’s intelligent enough to have worked out Afghanistan is a country. But with maps, it’s actually quite useful to actually say this is a particular thing, as opposed to uncategorized. And you’ll notice the icon next to it changes as well. So here, we have just got an icon which says it’s a new column, and instead, let’s change it to Country/Region, it now changes to a globe, the map of the world symbol. So, it’s just an additional helper for me to have it say, yes, this is geography.

So, data categorization, it refines information that Power BI Desktop can use to provide the best visualisations. If you got certain type of data, then you’ll notice some of these are blanked out, so Number of People, which is a number, I can’t put that as an address or city, I can put it in as a portal code. In America, for instance, postal codes are five or nine digits long. Or I could put it in as a barcode. So, it will grey out anything that’s not relevant. So, when you are inserting data, when you’re loading data for the first time, which is geographic, then I strongly recommend that you categorise the data so that the computer has a better idea of what it all is.