The May 2011 MrExcel Challenge of the Month invites you to mash up some data from the Azure Data Market. This episode of the MrExcel Podcast demos getting data from ADM.
...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
For more Excel 2010 knowledge, check out "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions.
...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
For more Excel 2010 knowledge, check out "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions.
Transcript of the video:
Learn Excel from MrExcel podcast, episode 1403.
Azure Data Market.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Okay! This month, May, 2011, the challenge of the month out of MrExcel.com.
Left navigation bar, go to challenge of the month is to build a mash-up of Data.
Some of your data, some data from the azure data market and run them through power pivot.
Great chance to win one of two Xbox and Konect systems.
Alright! So, figured we'd try and get some data from the azure data market.
Now, there's two ways to get it.
One is directly in Power pivot, that's over here Power pivot.
Power pivot window and then from Azure Data Market.
But, unfortunately you know, some of the data that you're getting may not be that clean and so I want to build the data right here in Excel first, so I can take a look and make sure that it matches my data.
Alright! So, I downloaded the free add-in for Excel 2010 import data from Data market.
Looking for your account key, sign in with a Windows Live, sign it.
Very nice, they just get you, right to it, every single time.
So, we'll copy that [ control C ], go back paste it into Excel, remember my account.
All right! Now, let's browse for data sets.
So, the data I have today Mary, and I are actually thinking about relocating.
We're looking to move down the South Florida, get out of the snow.
So, back here in Excel.
I have a list of possible cities in Excel.
We're looking for that south East Coast Southern, Florida.
And so I, you know, there's a couple data sets out here.
They're interesting to me if you've ever been on zillow to look for houses.
Zillow has four data feeds out here that can get you all of the properties in one zip code, but let's take a look at some crime data.
So, I'll just search for crime up there.
We go this data from the government that means it's free.
That's a great thing, we'll take a look at this and you have opportunities to explore the data set.
You can see it here that subscription is free, unlimited transactions per month, beautiful.
It will show you what kinds of data you can get.
Alright! So, let's just state, put in FL for Florida, run query, City crime, there we go, all right!
So, we're getting a row ID, to help me in city, year.
I guess, I only want data from 2007.
Population, violent crime.
All right! So, all kinds of statistics that we can download right into Excel.
Now, normally you would subscribe and once you subscribe, you can go to export or can export to Powerpivot.
Although, again what I'm recommending is that we take into Excel.
First the, first time that you do this, so we can choose a xls download.
Do I open or save?
Say open.
So, we now have data that came in from the Azure Data market.
I'm just going to choose all this data, [ CTRL C ] to copy, switch over to my data set and we'll paste it out here on the right hand side.
[ ctrl V ] This gives me a chance to actually look at the data.
You know, one thing I learned in Power pivot, if one side is all caps and the other side is proper case that does not create a match, like a vlookup would.
So, you know here I can just kind of see how many my cities are actually over there in the data, so equal MATCH.
This city comma 4 comma zero So, that one's there, is good.
You know what, there's a bunch that aren't and it's funny it's the data down in the Keys.
So, this is sorted from South to North.
That's generally not there as we get for the North, the data gets kind of better.
All right! So, as I start to look at this data, I want to match things up.
You know we have to go figure out.
Why some of these cities, I have over here aren't exactly matching, the cities over there and again this is all based on your getting data from different systems.
So, you want to do a little bit of data cleansing before you start to match those things up.
However, Azure Data Market very, very cool.
Again, if you're looking for houses, zillow.
There's you know, the number of internet users by country.
All kinds of things out there, some are free some are not.
Certainly for the challenge, we're looking for to use something that's free.
So, quick introduction to using data from the Azure Data Market.
Check it out.
Hey! I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Azure Data Market.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Okay! This month, May, 2011, the challenge of the month out of MrExcel.com.
Left navigation bar, go to challenge of the month is to build a mash-up of Data.
Some of your data, some data from the azure data market and run them through power pivot.
Great chance to win one of two Xbox and Konect systems.
Alright! So, figured we'd try and get some data from the azure data market.
Now, there's two ways to get it.
One is directly in Power pivot, that's over here Power pivot.
Power pivot window and then from Azure Data Market.
But, unfortunately you know, some of the data that you're getting may not be that clean and so I want to build the data right here in Excel first, so I can take a look and make sure that it matches my data.
Alright! So, I downloaded the free add-in for Excel 2010 import data from Data market.
Looking for your account key, sign in with a Windows Live, sign it.
Very nice, they just get you, right to it, every single time.
So, we'll copy that [ control C ], go back paste it into Excel, remember my account.
All right! Now, let's browse for data sets.
So, the data I have today Mary, and I are actually thinking about relocating.
We're looking to move down the South Florida, get out of the snow.
So, back here in Excel.
I have a list of possible cities in Excel.
We're looking for that south East Coast Southern, Florida.
And so I, you know, there's a couple data sets out here.
They're interesting to me if you've ever been on zillow to look for houses.
Zillow has four data feeds out here that can get you all of the properties in one zip code, but let's take a look at some crime data.
So, I'll just search for crime up there.
We go this data from the government that means it's free.
That's a great thing, we'll take a look at this and you have opportunities to explore the data set.
You can see it here that subscription is free, unlimited transactions per month, beautiful.
It will show you what kinds of data you can get.
Alright! So, let's just state, put in FL for Florida, run query, City crime, there we go, all right!
So, we're getting a row ID, to help me in city, year.
I guess, I only want data from 2007.
Population, violent crime.
All right! So, all kinds of statistics that we can download right into Excel.
Now, normally you would subscribe and once you subscribe, you can go to export or can export to Powerpivot.
Although, again what I'm recommending is that we take into Excel.
First the, first time that you do this, so we can choose a xls download.
Do I open or save?
Say open.
So, we now have data that came in from the Azure Data market.
I'm just going to choose all this data, [ CTRL C ] to copy, switch over to my data set and we'll paste it out here on the right hand side.
[ ctrl V ] This gives me a chance to actually look at the data.
You know, one thing I learned in Power pivot, if one side is all caps and the other side is proper case that does not create a match, like a vlookup would.
So, you know here I can just kind of see how many my cities are actually over there in the data, so equal MATCH.
This city comma 4 comma zero So, that one's there, is good.
You know what, there's a bunch that aren't and it's funny it's the data down in the Keys.
So, this is sorted from South to North.
That's generally not there as we get for the North, the data gets kind of better.
All right! So, as I start to look at this data, I want to match things up.
You know we have to go figure out.
Why some of these cities, I have over here aren't exactly matching, the cities over there and again this is all based on your getting data from different systems.
So, you want to do a little bit of data cleansing before you start to match those things up.
However, Azure Data Market very, very cool.
Again, if you're looking for houses, zillow.
There's you know, the number of internet users by country.
All kinds of things out there, some are free some are not.
Certainly for the challenge, we're looking for to use something that's free.
So, quick introduction to using data from the Azure Data Market.
Check it out.
Hey! I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
