The new Wolfram Alpha data types in Excel are not as clever as the old data types. This episode shows how to improve the conversion to data types.
Transcript of the video:
[ ♫ ]When you are down and out in spreadsheet hell, get your fix on Twitter at MrExcel.
Learn Excel from MrExcel podcast episode 2344.
Improving the conversion to Wolfram Alpha data types.
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Today, a few tips on how to improve converting to data types.
Let's get started.
Alright, so in regular Excel - Stocks and Geography have been there for a long time.
They are actually a very mature feature. This is kind of the second rev of those.
And here is something awesome that they've improved on the Excel team.
They've improved on this tremendously.
If I just type in Madison, I try and convert that to Geography.
They are going to come over and say, "Which one do you want? Do you want the one in Wisconsin, New York, Illinois, or Alabama?" But, they got smarter in version two of this.
if I surround madison by other cities in Wisconsin and I ask for Geography. Then they are going to figure it out perfectly.
They know that I mean the Madison in Wisconsin.
And here, Madison a tiny little city in Florida.
If i surround that with cities in Florida and ask for Geography.
They are going to automatically get Florida. How did the states appear?
I put that in, in advance with an =IFERROR so it it shows up automatically.
Ah, but here is the hassle. Wolfram Alpha.
This is brand new for Wolfram Alpha.
And they aren't smart enough to look at the surrounding cells.
So when I come out here and say that I want to put this as a Location.
It takes longer doesn't it?
Alright.
They only got two of them: West Palm Beach and Daytona Beach.
For Miami, they don't know if I mean Miami Florida, Miami Oklahoma, or Miami Arizona.
I mean, it's not that Miami is the largest one. It is surrounded by other cities in Florida.
So i choose. Select Orlando Florida or Oklahoma?
Florida. And then Madison, I want the one in Florida. They offer Madison Wisconsin.
Madison Alabama. Madison Mississippi.
I actually have to come up here and type Madison comma Florida in the Search box.
And then they get the Madison that I meant for them to get.
Now, does that mean that Wolfram Alpha is worse at this?
Well, they're new at this. But let's check what each feature knows.
So here is Madison with the map icon. The original Geography.
The one that's been around forever. Very nice: they give us an image of Madison.
Tell us it's in Florida.
That it is in Madison County. The area. It is in the United States. Latitude.
Then "Learn More". I don't understand why they are returning a link that doesn't work.
I don't get that. The longitude. Name.
Population and time zone.
Alright, so that's everything Bings knows about Madison Florida. Or even Madison Wisconsin.
[ typing ] Different image, right? That's all great.
But check out Wolfram Alpha. Oh my gosh. They know so much more.
First they don't have a picture from the city.
But they tell us it's in Wisconsin. The aggregate household income. The area.
Population. The country they are in.
The elevation and by the way that's in meters. How many females live there.
The number of households by the household income breakout.
A little map that you can put in.
Location - so they show Wisconsin and where in Wisconsin. The male population.
Median household income. The name. Famous people that are born there.
Look at that - Chris Farley.
And then population by age, population by educational attainment. All kinds of extra stuff that you get from Wolfram.
Also hey quick note here this is the old geography data type.
In this new version - the one that you can get on Microsoft 365 Home. They've also improved the Card.
Now I can click on Wisconsin to drill down.
right in the card.
That improvement is there.
Although it's not there in the Enterprise editions of Excel yet.
Alright so kudos to Wolfram Alpha. They have a lot more information. That seems to be a great thing.
Now the reason this whole thing came up was a couple of days ago I asked a lot of my friends "hey, tell me your five favorite musical acts.
and I had a little video where I predicted everyone's age. And it sort of worked.
There were a couple that were wrong. But it came out well.
But i had said I was surprised how hard it was to get this data in. It took me hours to get this spreadsheet to work.
Because here is what it looked like originally.
There were several items that it could not figure out. Right?
A lot of these came through as musical acts but Meat Loaf came through as a person and not the musical act.
And when i went here and i said that I wanted to change it.
So, Data Type, Change.
They would offer me the person and the food.
But not the music act. It is very frustrating.
It's like, how can they not have Meat Loaf and not have the Who and not have Queen.
I was like "what the heck is going on?" So I have shortened the list down and we are going to take a look at these problems one at a time.
First off, Oz du Soleil from Excel on Fire came up with a couple of music acts that aren't from the U.S.
Brazilian acts and it just simply doesn't know those. Gal Costa it knows as a person.
And Wolfram even knows that that person is a singer.
But when I go to notable achievements. It doesn't list any of the songs.
It just lists film appearances. So some of them it just simply doesn't know.
But John Prine. This is super annoying.
John Prine it says is a music work. Not not a person.
This was performed by a group called Low.
So I came here and said Data Type, Change. And when I search for John Prine.
They offer me John Prine the person or this song about John Prine.
And when I choose John Prine the person. I open the card.
They still don't give me a list of his songs.
So, it is really weird.
John Prine, Queen, The Who and and Jimmy Buffett. They don't seem to know.
How can it be that they don't know these?
So I came out and I typed Paradise by the Dashboard Light.
A Meat Loaf song. And they recognized that.
Then i said all right well tell me the artist who sang that.
And they gave me Meat Loaf the music act not Meat Loaf the person. And check this out.
I am actually able to take this. Ctrl+C. And then come down here to this cell.
I am going to Paste Special Values. Home, Paste as Values.
And it doesn't paste as values. It brings the Data Type.
But now it brings them as a musical act! I am like this is insane that i have to do this for each person.
So. Bohemian Rhapsody. I's able to get Queen. Copy Ctrl+C.
Come down here and Paste Special as Values.
Click OK. Jmmy Buffett and the Coral Reefer Band.
I was eventually able to get them but it is frustrating to go through all of those hassles. Here is the faster way.
If I would have added using CONCAT or TEXTJOIN, and would have asked for "Meat Loaf music act". Or "The Who music act".
Or "Jimmy Buffett music act", "Queen music act" and then Data Type, Automatic.
Just seems like it takes a long time.
And then, for all of those I get the correct item. I get the musical act.
So the entity type is "music act" in all the cases.
Alright, so that would have helped with those four.
But these five, either they are too obscure. Sorry Mike Girvin. Sorry Oz.
Sorry my sister Barb.
You like them as one of your top five but for whatever reason, Wolfram Alpha doesn't consider them worthy.
John Prine might be a problem with singer-songwriters.
I tried Arlo Guthrie as a substitute. Same kind of thing.
Also, here's another one: Bruce Springsteen was an example in my first video.
I remember the first time I did this I clicked and it said Bruce Springsteen a person. Nope.
and then the E Street Band or just Bruce Springsteen.
So first I am going to choose Bruce Springsteen and the E Street Band.
And then over here I am going to choose Bruce Springsteen music act.
And the big difference> When we ask for =A2.works.
and then here I will just copy that over. Then ask for the COUNTA.
There were 531 songs by Bruce Springsteen and the E Street Band.
But if I just do Bruce Springsteen, then I get a thousand nineteen.
Alright, so that's one where I guess because I chose wrong in the Data Selector then I get very different answers.
So the point of all this?
I guess be careful the Stocks and Geography are smart enough to look at the surrounding cells.
Hopefully at some point Wolfram Alpha will be smart enough to look at the surrounding cells.
That might lead to much better results.
And then, adding what you're looking for - the entity type - seems to improve the performance a lot. So try that.
Then just be careful depending on whether you're getting the artist or the artist and their band. You might have songs that are in one or not the other.
It was light outside when we started this.
Let's call it a day.
Please, down below the video, click Like, Subscribe, Ring the Bell.
Feel free to post any questions or comments down in the comments below.
I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
[ ♫♫ ]Tips and tricks get your spreadsheet fix at MrExcel.com.[ ♫♫ ]
Learn Excel from MrExcel podcast episode 2344.
Improving the conversion to Wolfram Alpha data types.
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Today, a few tips on how to improve converting to data types.
Let's get started.
Alright, so in regular Excel - Stocks and Geography have been there for a long time.
They are actually a very mature feature. This is kind of the second rev of those.
And here is something awesome that they've improved on the Excel team.
They've improved on this tremendously.
If I just type in Madison, I try and convert that to Geography.
They are going to come over and say, "Which one do you want? Do you want the one in Wisconsin, New York, Illinois, or Alabama?" But, they got smarter in version two of this.
if I surround madison by other cities in Wisconsin and I ask for Geography. Then they are going to figure it out perfectly.
They know that I mean the Madison in Wisconsin.
And here, Madison a tiny little city in Florida.
If i surround that with cities in Florida and ask for Geography.
They are going to automatically get Florida. How did the states appear?
I put that in, in advance with an =IFERROR so it it shows up automatically.
Ah, but here is the hassle. Wolfram Alpha.
This is brand new for Wolfram Alpha.
And they aren't smart enough to look at the surrounding cells.
So when I come out here and say that I want to put this as a Location.
It takes longer doesn't it?
Alright.
They only got two of them: West Palm Beach and Daytona Beach.
For Miami, they don't know if I mean Miami Florida, Miami Oklahoma, or Miami Arizona.
I mean, it's not that Miami is the largest one. It is surrounded by other cities in Florida.
So i choose. Select Orlando Florida or Oklahoma?
Florida. And then Madison, I want the one in Florida. They offer Madison Wisconsin.
Madison Alabama. Madison Mississippi.
I actually have to come up here and type Madison comma Florida in the Search box.
And then they get the Madison that I meant for them to get.
Now, does that mean that Wolfram Alpha is worse at this?
Well, they're new at this. But let's check what each feature knows.
So here is Madison with the map icon. The original Geography.
The one that's been around forever. Very nice: they give us an image of Madison.
Tell us it's in Florida.
That it is in Madison County. The area. It is in the United States. Latitude.
Then "Learn More". I don't understand why they are returning a link that doesn't work.
I don't get that. The longitude. Name.
Population and time zone.
Alright, so that's everything Bings knows about Madison Florida. Or even Madison Wisconsin.
[ typing ] Different image, right? That's all great.
But check out Wolfram Alpha. Oh my gosh. They know so much more.
First they don't have a picture from the city.
But they tell us it's in Wisconsin. The aggregate household income. The area.
Population. The country they are in.
The elevation and by the way that's in meters. How many females live there.
The number of households by the household income breakout.
A little map that you can put in.
Location - so they show Wisconsin and where in Wisconsin. The male population.
Median household income. The name. Famous people that are born there.
Look at that - Chris Farley.
And then population by age, population by educational attainment. All kinds of extra stuff that you get from Wolfram.
Also hey quick note here this is the old geography data type.
In this new version - the one that you can get on Microsoft 365 Home. They've also improved the Card.
Now I can click on Wisconsin to drill down.
right in the card.
That improvement is there.
Although it's not there in the Enterprise editions of Excel yet.
Alright so kudos to Wolfram Alpha. They have a lot more information. That seems to be a great thing.
Now the reason this whole thing came up was a couple of days ago I asked a lot of my friends "hey, tell me your five favorite musical acts.
and I had a little video where I predicted everyone's age. And it sort of worked.
There were a couple that were wrong. But it came out well.
But i had said I was surprised how hard it was to get this data in. It took me hours to get this spreadsheet to work.
Because here is what it looked like originally.
There were several items that it could not figure out. Right?
A lot of these came through as musical acts but Meat Loaf came through as a person and not the musical act.
And when i went here and i said that I wanted to change it.
So, Data Type, Change.
They would offer me the person and the food.
But not the music act. It is very frustrating.
It's like, how can they not have Meat Loaf and not have the Who and not have Queen.
I was like "what the heck is going on?" So I have shortened the list down and we are going to take a look at these problems one at a time.
First off, Oz du Soleil from Excel on Fire came up with a couple of music acts that aren't from the U.S.
Brazilian acts and it just simply doesn't know those. Gal Costa it knows as a person.
And Wolfram even knows that that person is a singer.
But when I go to notable achievements. It doesn't list any of the songs.
It just lists film appearances. So some of them it just simply doesn't know.
But John Prine. This is super annoying.
John Prine it says is a music work. Not not a person.
This was performed by a group called Low.
So I came here and said Data Type, Change. And when I search for John Prine.
They offer me John Prine the person or this song about John Prine.
And when I choose John Prine the person. I open the card.
They still don't give me a list of his songs.
So, it is really weird.
John Prine, Queen, The Who and and Jimmy Buffett. They don't seem to know.
How can it be that they don't know these?
So I came out and I typed Paradise by the Dashboard Light.
A Meat Loaf song. And they recognized that.
Then i said all right well tell me the artist who sang that.
And they gave me Meat Loaf the music act not Meat Loaf the person. And check this out.
I am actually able to take this. Ctrl+C. And then come down here to this cell.
I am going to Paste Special Values. Home, Paste as Values.
And it doesn't paste as values. It brings the Data Type.
But now it brings them as a musical act! I am like this is insane that i have to do this for each person.
So. Bohemian Rhapsody. I's able to get Queen. Copy Ctrl+C.
Come down here and Paste Special as Values.
Click OK. Jmmy Buffett and the Coral Reefer Band.
I was eventually able to get them but it is frustrating to go through all of those hassles. Here is the faster way.
If I would have added using CONCAT or TEXTJOIN, and would have asked for "Meat Loaf music act". Or "The Who music act".
Or "Jimmy Buffett music act", "Queen music act" and then Data Type, Automatic.
Just seems like it takes a long time.
And then, for all of those I get the correct item. I get the musical act.
So the entity type is "music act" in all the cases.
Alright, so that would have helped with those four.
But these five, either they are too obscure. Sorry Mike Girvin. Sorry Oz.
Sorry my sister Barb.
You like them as one of your top five but for whatever reason, Wolfram Alpha doesn't consider them worthy.
John Prine might be a problem with singer-songwriters.
I tried Arlo Guthrie as a substitute. Same kind of thing.
Also, here's another one: Bruce Springsteen was an example in my first video.
I remember the first time I did this I clicked and it said Bruce Springsteen a person. Nope.
and then the E Street Band or just Bruce Springsteen.
So first I am going to choose Bruce Springsteen and the E Street Band.
And then over here I am going to choose Bruce Springsteen music act.
And the big difference> When we ask for =A2.works.
and then here I will just copy that over. Then ask for the COUNTA.
There were 531 songs by Bruce Springsteen and the E Street Band.
But if I just do Bruce Springsteen, then I get a thousand nineteen.
Alright, so that's one where I guess because I chose wrong in the Data Selector then I get very different answers.
So the point of all this?
I guess be careful the Stocks and Geography are smart enough to look at the surrounding cells.
Hopefully at some point Wolfram Alpha will be smart enough to look at the surrounding cells.
That might lead to much better results.
And then, adding what you're looking for - the entity type - seems to improve the performance a lot. So try that.
Then just be careful depending on whether you're getting the artist or the artist and their band. You might have songs that are in one or not the other.
It was light outside when we started this.
Let's call it a day.
Please, down below the video, click Like, Subscribe, Ring the Bell.
Feel free to post any questions or comments down in the comments below.
I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
[ ♫♫ ]Tips and tricks get your spreadsheet fix at MrExcel.com.[ ♫♫ ]
