How can you build a Power Map with two different data sets?
Step 1: Get both tables into the Data Model. You can do this one of three ways:
1a) Power Query with Load To, Add this data to the data model
1b) Power Pivot - add this data to the data model
1c) Create a pivot table, choosing the box, add this data to the data model
When you launch Power Map, you can build the first map, then Add Layer and use the other data set.
To download this workbook: https://www.mrexcel.com/download-center/2018/06/power-map-from-two-data-sets.xlsx
Step 1: Get both tables into the Data Model. You can do this one of three ways:
1a) Power Query with Load To, Add this data to the data model
1b) Power Pivot - add this data to the data model
1c) Create a pivot table, choosing the box, add this data to the data model
When you launch Power Map, you can build the first map, then Add Layer and use the other data set.
To download this workbook: https://www.mrexcel.com/download-center/2018/06/power-map-from-two-data-sets.xlsx
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2218: Power Map From Two Data Sets.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
I usually do these Power Excel seminars and I always show a couple of Power Map examples.
But I actually had someone in the audience the other day in Melbourne, Florida, who's been trying to use Power Map and had a question for me that I wasn't really sure of: How do you get two different layers-- two different data sets-- on a map?
And here's the way I found to do it.
I'll be interested in the YouTube comments to see if someone has a better way.
So I have these two different data sets-- one by State and one by City-- and so I want to create a field map from this and a column chart from this.
First thing I want to do is, I'm going to make both of them into a table with Ctrl+T-- Table1, horrible name, let's call this States-- and then Ctrl+T and rename this City.
Now, what I have to do is I have to-- before I start Power Map-- I have to get both of these into the data model.
If I'm lucky enough to have the Power Pivot tab, well, then it'll be really easy.
Just click Add Data Model-- Add Data Model.
Well, let's say you don't have this tab.
Okay.
So here's my cheap and dirty way to do this: Insert, PivotTable, Add this data to the Data Model, and that just creates some sort of a PivotTable.
I don't care, I just need to get the data into the data model.
Then come over here, same thing: Insert, PivotTable, Add this data to the Data Model.
I'll actually put this one on the same worksheet that no one will ever see.
I'll hide this worksheet or something like that, click OK, and again, just some sort of data there like that-- beautiful.
Alright.
Now, now that I have both of those in the data model, when I go to Insert, Power Map or 3d Map-- success.
What I have now, is both categories, so I'll build the City first-- City and State like this-- and we'll say the color is going to be the category and then the score is going to be the height.
Alright.
So there's our first map, and then I'm going to say, Add a Layer, and for Layer 2 I'll use State. and this will be a filled area map, and our score will be the value-- score, and the score will be the value.
Alright, so we get the different shapes there.
Now, that really-- that is an ugly... that is the ugliest color I've ever seen.
So, let's see if we can find some way to change that color-- that's down here under Layer Options.
Instead of being based on a purple, I'll just go with some other color like that-- maybe something darker so I could see the changes.
Alright.
So Florida has the highest score, Louisiana has the lowest score.
Alright.
Oh, and I missed Alabama completely.
Alright.
So that's it.
Whole trick to this is to get both datasets into the data model.
Again, if you have the PowerPivot tab, that's easy-- just choose one data set, Add to Data Model, choose the other data set, Add to Data Model.
But if you don't, then in Excel 2013 or 16, Insert, PivotTable, Add this data to the Data Model, would work.
Or, heck, even Power Query-- Data, From Table, and then Close & Load, to add this data to the Data Model.
So lots of different ways to get both tables in there, but if you don't put both tables in the data model before you head to Power Map, it's just never going to work.
Yeah, my new book, MrExcel LIVe, The 50 Greatest Excel Tips of All Time, includes some Power Map examples-- check that out.
Click the "I" on the top right hand corner for information about that.
Wrap-up of today's episode: How can you build a power map with two different data sets?
You have to get both tables into the data model and you can do that one of three ways-- either with Power Query, Power Pivot, or just create a Pivot Table and choose that box that says Add this data to the Data Model.
Do that for both tables.
And when you launch Power Map you can build the first map, and then click Add Layer, and use the other data set.
To download the workbook from today's video, visit the URL in the YouTube description.
I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
I usually do these Power Excel seminars and I always show a couple of Power Map examples.
But I actually had someone in the audience the other day in Melbourne, Florida, who's been trying to use Power Map and had a question for me that I wasn't really sure of: How do you get two different layers-- two different data sets-- on a map?
And here's the way I found to do it.
I'll be interested in the YouTube comments to see if someone has a better way.
So I have these two different data sets-- one by State and one by City-- and so I want to create a field map from this and a column chart from this.
First thing I want to do is, I'm going to make both of them into a table with Ctrl+T-- Table1, horrible name, let's call this States-- and then Ctrl+T and rename this City.
Now, what I have to do is I have to-- before I start Power Map-- I have to get both of these into the data model.
If I'm lucky enough to have the Power Pivot tab, well, then it'll be really easy.
Just click Add Data Model-- Add Data Model.
Well, let's say you don't have this tab.
Okay.
So here's my cheap and dirty way to do this: Insert, PivotTable, Add this data to the Data Model, and that just creates some sort of a PivotTable.
I don't care, I just need to get the data into the data model.
Then come over here, same thing: Insert, PivotTable, Add this data to the Data Model.
I'll actually put this one on the same worksheet that no one will ever see.
I'll hide this worksheet or something like that, click OK, and again, just some sort of data there like that-- beautiful.
Alright.
Now, now that I have both of those in the data model, when I go to Insert, Power Map or 3d Map-- success.
What I have now, is both categories, so I'll build the City first-- City and State like this-- and we'll say the color is going to be the category and then the score is going to be the height.
Alright.
So there's our first map, and then I'm going to say, Add a Layer, and for Layer 2 I'll use State. and this will be a filled area map, and our score will be the value-- score, and the score will be the value.
Alright, so we get the different shapes there.
Now, that really-- that is an ugly... that is the ugliest color I've ever seen.
So, let's see if we can find some way to change that color-- that's down here under Layer Options.
Instead of being based on a purple, I'll just go with some other color like that-- maybe something darker so I could see the changes.
Alright.
So Florida has the highest score, Louisiana has the lowest score.
Alright.
Oh, and I missed Alabama completely.
Alright.
So that's it.
Whole trick to this is to get both datasets into the data model.
Again, if you have the PowerPivot tab, that's easy-- just choose one data set, Add to Data Model, choose the other data set, Add to Data Model.
But if you don't, then in Excel 2013 or 16, Insert, PivotTable, Add this data to the Data Model, would work.
Or, heck, even Power Query-- Data, From Table, and then Close & Load, to add this data to the Data Model.
So lots of different ways to get both tables in there, but if you don't put both tables in the data model before you head to Power Map, it's just never going to work.
Yeah, my new book, MrExcel LIVe, The 50 Greatest Excel Tips of All Time, includes some Power Map examples-- check that out.
Click the "I" on the top right hand corner for information about that.
Wrap-up of today's episode: How can you build a power map with two different data sets?
You have to get both tables into the data model and you can do that one of three ways-- either with Power Query, Power Pivot, or just create a Pivot Table and choose that box that says Add this data to the Data Model.
Do that for both tables.
And when you launch Power Map you can build the first map, and then click Add Layer, and use the other data set.
To download the workbook from today's video, visit the URL in the YouTube description.
I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel.
