DA-100 Microsoft Power BI – 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 these six different regions, greater Manchester, Merseyside, South Yorkshire, Thailand, Weir, West Midlands and West Yorkshire being shown in different visualizations. 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 one more than two, but there’s two principal ones, map and third 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 needed 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 it’s 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 through reports. So if I write 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 visualizations. 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 I’m 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 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 create 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 sales. 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 another example of maps. So I’m going to use some different data from our original spreadsheet. So we’re 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 it’s called 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 COSP. For now, we’re looking at how to visualize 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. Actually, just click on the map field rather, and drag it so that’s a total 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 showed me a map of the world. And I’m 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, and yet, well, there’s a fair bit near Afghanistan. I’m not sure any of them are actually in Afghanistan or this huge area there, but we got areas in India and 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’m 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, say, population into size. And there you can see, for instance, California, one of the biggest states by population. New York. Yeah. Half the size of Texas. 26 million. So it probably is the biggest state just on a visual field.

2. Formatting maps

Now let’s have a look at what we’ve got here in the formatting section. So we have got data colors, so we’ve got a default color. So you can change whichever color you want or you can have a look at all of the colors and say, I want to change one particular color. If I just expand this a little, you can also see category labels. So if you wanted to show what state it is, I think personally that’s just tad too small. I would prefer fewer labels, but to actually be able to read them. And again, you can adjust various things like the color 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 them all a bit smaller. It’s easier to show if a dot of the category labels on or all of them a bit bigger. So, as I say, one is the default.

Map controls. There are two Mac 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 a scroll and you can just scroll using the mouse left and right and up and down, up north and southeast west. Use a 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 roadmap 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.

So that’s California again, or a grayscale light or dark view. You would have to have particular use, especially for the dark. It could be quite illuminating to have a dark background with light foregrounds. But for me the rod seems standard. Heat Map this changes your 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 have 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 colors for 00:50 and 100%. So if you wanted it to go to really dark colors, then you can see a very contrasting version of where the darker colors are. Standard title at the top again, it’s not very big, so I would center and make it bigger and have a background color. I think I’ve been using yellow dark color, a foreground color 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 colors section. So when I put the heat map back on, it disappears. So I’ll return to data colors section. In the next video, we’re going to have a look at how we can help tidy up this particular graph of Afghanistan.

3. 8g. 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 it’s centered 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, Ara, Z, or are they American states? I mean, al could be Albania.

A r 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 name too in the modeling tab in the older versions or column tools in the later versions of Power Bi, 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 if I say this is a place, well, that’s no real different from what the computer has guessed.

 And you can see all over 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 nonus, nonwestern 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 where it’s completely relevant. Continent. The computer goes, well, 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 claused. If I open up the advanced system section, you can again change the data category.

So you’ve 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, okay, 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’ll create a new column. So I can also do this by going into modeling 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 of data name, and then I’m going to put quotation marks around it and click the commit sign, the tick mark, check mark. Right, that’s not actually done anything, but what’s individually, 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 new column here and that would do the same thing.

And you can see it’s being represented over here as well. So there’s no actual way to insert an extra column here. Even if I try to get some room, it doesn’t work. So but either way, I have now inserted a constant cold 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 this down further to next level in the hierarchy. And that 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, 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, but not quite into it.

And if I wanted, I could also categorize 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 got county. Let’s try to change that. This computer is intelligent enough to have worked out Afghanistan as 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 also 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, when I change it to country stroke region, it now changes to a global map of the world symbol. So it’s just an additional helper for me to be able to say, yes, this is geography. So data categorization. It refines the information that Power Bi desktop can use to provide the best visualizations. If you’ve 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 postal 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 gray 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 categorize the data so that the computer has a better idea of what it all is.

4. Filled Maps, Conditional Formatting, and color blindness

A couple of videos ago we made this maps us. But whilst these bubbles are good and we had a look at heat maps, wouldn’t it be good to actually have the states be filled in a single color? And this you can do. And that is called a filled map. So very easy. I’m just going to click on this map and I’m going to change the visualization for maps map to filled map. So now we can see everywhere is filled. Now you may notice that Florida isn’t filled. That’s because there is actually a deliberate typhole in the Florida state that we will use later and find out how to correct it. Obviously the easiest way to correct it would be go back to the data. But sometimes that’s not a problem. Now you might be a bit disappointed with this field map. Yes, it fills it in, but everything’s all the same color, no problem. What we’re going to do is we are going to color it based on the population.

So I’m going to drag a population down to the color which is not there. Got location legend? Could it be legend? Well that certainly color. Is it, let’s face it. But it colors it for a different color for each population and that’s hardly useful. I can’t really see that. California for instance, is a bigger color than Pennsylvania or Rhode Island or something like that. So no, that’s not what we need. But we need some way of adding in the colors. So it must be in formatting, right? So we’ve got data colors here, so that’s no problem. So we go in here and we have a default color and then we have show all. So we could change these colors individually.

But hang on, that means we have to do the analysis. That’s not going to actually be useful. How can we change the colors so that it reacts to something like population? Now in more recent versions we’ve got this FX button but it was hidden away in previous versions. Have a look at the right hand side of this data colors. I’m just going to move it up and here you can see three dots appear when I go over the default color. So it is very hidden, very out of the way. Heaven’s sakes. Field maps, this should be your primary color focus. You should be able to say you are by default going to do it based on some other field. But apparently that’s not the case.

We’re hiding it away with three dots which are only visible when you hover over default color. So click on that and we get conditional formatting and this new box appears. So we can format based on a color scale or rules or field value. Let’s have a look at color scale. Scale. This is the more usual one. So I want to do a color scale based on the population, let’s say. And it’s going to sum the population there are various options average min, max count, distinct count and median and standard deviation variance if there is none. So for instance Florida, florida is deliberately empty. What should you do? Well, you could say don’t format or give it a specific color.

So the lowest value is going to be name of color. So we’re going to say red. We could also say it’s going to be this particular number. That can be useful when you want to, for instance, say any state that’s got less than 10 million is going to be the same color, same for the maximum highest color. So now you can see that there is this differential. We can see that Hawaii is in red, alaska is in red. Basically everything’s in red apart from Texas, California and New York is greenish. Okay, what’s going on here? Why have we not got more colors? It’s because we have such a huge separation of the data. California 38 million, texas 26 million, new York and Florida, which isn’t there, 19 million and then everything else 12 million and less. So really having it by a population is not actually going to be of much use. What we really want is to have it by a population ranking, let’s say.

So if I get rid of that, not in the two tips in the FX, which is the default color, and change it from sum of population to sum of population ranking, now we get a much more useful color scheme. So California is in red, that’s because it’s ranked one, which now is the minimum rather than the maximum. So let’s change these colors around. So I’ll have green going to red. So California is green and then you can see the most populous states in green and the least populous states in red. Maybe not that much of a differential. I mean all of these states look roughly the same. We’ve got Minnesota and Wisconsin and Missouri all in a sort of light green or lime green or something.

So what we can do is have a diverging color set. So essentially instead of two colors, it gives us three. So we can have as the middle value a another color. And you can see the difference in the color scheme here. This goes from this sort of green to a green red to a red. This one has much more variety. So now when I look at it, for me the color scheme isn’t quite right, but it certainly is more informative. One potential problem of all of this is of course colorblindness. 8% of males and a much smaller percentage of females have particular colorblindness.

For example, red green colorblindness, which really if it’s red green colorblindness, that will make this very difficult to interpret because this color green, a fairly dark green would be roughly the same as a fairly dark red. It’s the amount of darkness. So make sure when you’re designing maps for instance, that you have got some idea of color blindness in mind and maybe have a look at simulating colorblindness. There are plenty of websites where you can simulate colorblindness. For example, Colororacle. org this is a filter which allows you to change what you’re seeing onto the screen for a particular variety of version of colorblindness.

So one possibility could be instead of making it red green, make it red blue. So if I change this to a blue, this will be a lot more visible to people with red green color blindness. Examples of other colors you could use here we have a website called ColorBrewer Two and you can see you can have various color schemes, either multi hue or single hue. And you’ll notice one of those that it doesn’t have, it doesn’t recommend is red green. So if I want to show only those which are colorblind safe, you can see that all of them that are shown here are colorblind safe until you can see red green colorblindness affects about 8% of men and 0. 4% of women.

So it’s important thing one in twelve men would not be able to see well with a red green chart. So have a look at various ranges that you can use and see which one of these might actually look good for the data that you are trying to present, as well as not showing problems for people with particular colored blindness. So aside from this conditional formatting, which is really hidden away with these, then there’s nothing additional in the formatting for field maps that wasn’t there in maps. However, you won’t be able to use the field maps with success everywhere. Now, if you go back to our original maps US, everything was in green.

So we can change this so that it uses conditional formatting again. So if I just put in the population ranking, you can see that is how you really use color in a map. But if I go into maps, Afghanistan for instance, and try to change that to a field map, the computer goes, I have no idea what you’re talking about. It doesn’t know that these are regions as well as little points on the map. Similarly, if I was to go into here our admins regions, yes, West Midlands is a region, Greater Manchester is a region, but the computer, I’m afraid, doesn’t know where those regions are atonino’s specific locations. So food maps can be quite useful. But unfortunately, powerbeats is fairly limited in what areas of the world it covers. However, fingers crossed it covers your area. Give it a go.