It is like the Christmas where you got almost every thing you asked for. The Excel team has given us almost everything I asked for with the new data types. You can now create your own data types in Excel, without help from the I.T. department.
I've been out for two months, and while I was away from the computer, I read a book about narrative storytelling in radio. This is a longer video, with a lost of "this happened, then this happened, then this happened". I am not sure this will become the new normal. But I hope you can put up with it for today.
Table of Contents
(0:00) Welcome & history of Data Types
(1:09) What would you like them to add?
(1:48) Exchange rates done in March 2019
(2:16) Wolfram Alpha data types raised the bar
(2:41) Getting Weather History in Excel
(3:10) What I envisioned versus what they did
(5:03) If I had asked for a sports car...
(5:51) Improved development cycle for Microsoft 365
(7:06) Welcome to the Podcast - Starting Over
(7:23) The struggle between I.T. and the Other Departments
(8:06) Those who can do VLOOKUP versus those who can't
(8:27) The term "User" is a pejorative when spoken by the I.T. department
(9:01) Plan to create a too-wide data set in Power Query
(9:34) Finishing the last merge to create a wide data set
(10:12) Defining a Data Type in Power Query
(12:38) Using data types in the grid - displaying a card
(13:01) Filtering by a field not in the grid
(13:32) Adding data type fields to the grid and calculating revenue
(14:47) When you get almost everything you ask for
I've been out for two months, and while I was away from the computer, I read a book about narrative storytelling in radio. This is a longer video, with a lost of "this happened, then this happened, then this happened". I am not sure this will become the new normal. But I hope you can put up with it for today.
Table of Contents
(0:00) Welcome & history of Data Types
(1:09) What would you like them to add?
(1:48) Exchange rates done in March 2019
(2:16) Wolfram Alpha data types raised the bar
(2:41) Getting Weather History in Excel
(3:10) What I envisioned versus what they did
(5:03) If I had asked for a sports car...
(5:51) Improved development cycle for Microsoft 365
(7:06) Welcome to the Podcast - Starting Over
(7:23) The struggle between I.T. and the Other Departments
(8:06) Those who can do VLOOKUP versus those who can't
(8:27) The term "User" is a pejorative when spoken by the I.T. department
(9:01) Plan to create a too-wide data set in Power Query
(9:34) Finishing the last merge to create a wide data set
(10:12) Defining a Data Type in Power Query
(12:38) Using data types in the grid - displaying a card
(13:01) Filtering by a field not in the grid
(13:32) Adding data type fields to the grid and calculating revenue
(14:47) When you get almost everything you ask for
Transcript of the video:
Learn Excel from MrExcel podcast episode 2378. Define your own linked data types version one.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
There's a great story about how we got here today. It was back in August 2018. Episode 2227.
When I talked about the new stocks and geography data types.
So you start with cells that contain cities. Here on the Data tab mark them as geography.
You get a little map icon there.
You can click on the map icon to see information about that city.
But even better is you can add new data to the grid using =N2.Population or just use this Insert Data icon and get all that information. And there were even cool things.
If your data was stored as a Table you could come in here and choose a field that isn't in the grid. For example, State and Province.
And then filter out the Oklahoma cities. It was a beautiful thing.
I remember I was on the road doing live Excel power seminars.
I would always demo this.
I would ask the audience, “Hey, what do you think they should do next?” And almost universally in every single seminar, someone said Exchange Rates and someone else said Weather.
But then after a few seminars I got the usual answers of Exchange Rates and Weather and then someone said, “No, no I think you should let us define our own”.
I laughed. I thought, oh well that's never going to happen.
But I came home from the seminar. I wrote to the Excel team.
I said I know this sounds crazy, but they wanted to define their own.
And the Excel team said, “Yes, we think they should be able to define their own too”.
So, imagine that the data types team was split out into three sub-teams.
One went to work on Exchange Rates. One went to work on Weather.
And one went to work on define your own. The exchange rates team - it was quick.
March 23, 2019 episode 2274. Hey they have exchange rates.
They were clever.
They changed the stock data source provider who powers this to another company that offers exchange rates.
So, bam! Success.
They were able to very quickly get us exchange rates and that was beautiful that was in March of 2019.
And then August of 2020 they introduced the Wolfram Alpha data types.
This raised the bar on data types tremendously. The data card could now be resized.
You could drill down in the data card. They support a hierarchy.
You can even return a photo to the grid. They can return an array.
So here I have Houston. Rather than say Geography, choose Location.
Then in the card I can scroll all the way down to the bottom. Open Weather.
Click on the icon here. The card changes. It can be weather history.
I could use daily or weekly. Let's just put in daily.
Get the mean temperature all the way down. (That is in Celsius).
All right. Beautiful. So the second one is done. Now, the third one. Define your own.
When I started talking about this feature and how cool this feature would be.
I said you'd probably have the I.T. Department define a product catalog using XML.
I can select a range of cells that would contain an SKU and then up here you'd be able to define this as OurCompany ItemList.
And then I could do =A2.Category. =A2.Description. =A2.ListPrice.
You may even get an image in the grid. Hover and see the card.
Filter and sort using fields not displayed. Well, hey great news.
It's now here and they've done it.
They didn't do it the way that I would have done it.
In fact, we don't have to rely on the I.T. department.
Anyone using Power Query can define their own data type. So, hey that's awesome.
The thing I am a little bit disappointed in is I can't select a range of cells then that contain an SKU and convert to OurCompany Items. Which to me, that was sort of important.
But I can do formulas like =A2.Category, =A2.Description.
I can’t get an image in the grid. That's okay.
Because when this team started on Define your Own, the second team hadn't given us a way to get the image in the grid.
That's a relatively new feature. So I get it.
Give these people time to catch up to what these people did. We can hover and see the card.
Filter and sort using fields not displayed. Now that I've seen it.
Now that I've seen it.
I realize using Power Query I have to bring the data types into a worksheet in the grid. Don't make me do that. Just let me make it be a Connection Only.
And yes I really need to select a range of cells that contain the SKU and convert.
That, to me, is really important. Use XLOOKUP to retrieve data from the data type.
Go ahead and hook up that the feature where we can get an image of the product in the grid.
I am probably never going to use it but there'll be people who want to use that.
As I think about a metaphor.
If I had asked the Excel team for a sports car.
I would specify that I wanted a 12-cylinder engine. Zero to 120 miles an hour in 4.1 seconds.
A keyless starter so I won't have to put the key in.
Twelve-speaker sound system. Mahogany dashboard. 18 airbags. Anti-theft system.
And then what they gave me is even better. It's 16 cylinder engine.
But when they got to the second one they said yeah there's not a keyless starting system. In fact, there's not even a starter.
We're just releasing this right now because it is a kick-*** speaker system.
Sit in the car and listen to the radio. And and the mahogany dashboard is not done yet.
But it has even better airbags and anti-theft system than than you wanted.
And you'll enjoy that experience.
Let's think about the way that Microsoft used to release Excel. It would come out every three years or so.
Basically it was a three-year development cycle.
So in the first year they'd be thinking about ideas. Second year they'd actually build those.
Third year they would test them. If the test failed.
If it wasn't working you would have to wait another three years for the thing to come out.
It's much better now with Microsoft 365 because there's a release every Tuesday.
As soon as the feature has some useful parts they can release it let people try it.
Let people come up with more ideas that will shape the future of the feature.
It's a better way to go.
They keep working on the feature and if they release a feature that doesn't have everything?
On another Tuesday, not next Tuesday, but some other Tuesday, those new features will come out.
So the old Bill?
This this video would be “What? You've got a sports car and there's no way to actually start it?” But today, I get it. It took a whole bunch of under-the-hood work to get to this point.
They have every intention of putting in a starter and letting us drive this car.
And there are useful features in this V1 that we did not have before.
There are use cases here that some specific users will absolutely love. Alright so let's start over.
Learn Excel from MrExcel podcast episode 2378.
Create amazing expandable fields so your data isn't too wide all the time.
Hey, welcome back to MrExcel netcast. I am Bill Jelen. A great new feature from the Data Types team.
You know, a long time ago. Back in 1988 – 1989. I actually worked in the I.T.
department for a couple of years. Then I left I.T. And I went and worked in Accounting and Finance.
It was interesting to see both sides of that struggle between I.T. and the other departments.
So today, I have a fictitious I.T.
department who's giving me 110,000 rows of this: Store ID. Date. Product ID, Cust ID and Quantity. You know I hate the Product ID.
I understand why the I.T.
department stores it as a Product ID rather than give the description that I actually need. And the customer name that I actually need.
And the store name that I actually need. This is a nice tight way to to provide the data.
I.T. offers me three different workbooks with lookup tables for product, customer, and store.
You've heard me say before there's two kinds of people in the world: Those that can do VLOOKUP/XLOOKUP/INDEX-MATCH.
(Pick your poison, I don't care which) and Those who can't.
So if you give that 110,000 rows to someone who can do these lookups. Yes they are fine. They can run their own reports.
But there's a lot of people who can't. Alright so the I.T. department.
I love when the I.T. department uses the word “User”.
It's never a positive word. “Users” really should just be “those darn Users”.
The I.T. people: I can imagine there's a meeting in I.T.
and they say, “Those users sometimes they need data from all the lookup tables and they can't do VLOOKUP/XLOOKUP/INDEX-MATCH”.
So someone says well let's build a super wide view with every field from every lookup table that way they won't have to bother us to add more fields in the future. And that's how we head down this path.
Now, here's the plan. We are going to do all this using Power Query.
From the Sales workbook, I am going to create a connection only.
And then Stores, Products and Customers create connection only.
And then Merge. And merge and merge. And end up with a data set that is this wide.
When I started this video my plan was to do one of those freaky time lapse things where everything happens at 10x the normal speed.
But I decided I am going to turn off the video and I am going to go do this.
There are other videos I have that would show you how to do these things and that's not the point for today.
I will be right back. Alright, timewarp.
There we have Sales, Customers, Stores, Products. The first merge. The second merge.
And then we're taking that last merge and we're merging with Customers.
Choose CustID and CustID. Left Join. Click OK.
Out here for customers I am going to bring in all the fields except for the CustID because we already have that one.
And don't use original column name as a prefix. Click ok and there.
We have now an insanely wide data set.
If I would return this back to the grid it's just going to be terrible.
It's going to go be way too wide. People are going to hate it. And this is where this great new feature comes in.
What we're going to do is we're going to collapse these fields down.
I have a lot of information here about stores.
I am going to select store id and control-click on selling square feet, mall developer, store name, the era when was the store first built, and what region are they in. I think that's all of my information for stores.
Out on the Transform tab this great new feature called Create a Data Type.
Create a Data Type. So I choose those fields. Go to Create Data Type.
We will take a look at Advanced. The data type name is going to be called Store.
And what do I want to display?
I don't really want the Store ID. I actually want the Store Name.
Give me something useful. Give me a way to get the Store ID if I need it.
But give me Store Name.
Store ID, Selling SF, all of those fields are there because I pre-selected them. They are going to be there.
And what's going to happen is those fields are going to get collapsed down into a single column with a great new icon there. That says that we have a data type. Called Store.
Alright now I am going to do the same thing for Product and Customer.
So I choose the the Product ID, and then Description, Price, Author, Publisher, and Format.
Create a Data Type. This is going to be called Products.
I am going to display the Description instead of the Product ID.
In Advanced we can see that all the fields I selected are already there.
And now we have store and products.
Finally Customer through Zip Code. Those fields are all related. As well as Customer ID. Create Data Type.
This will be called Customer and the display column will be called Customer.
I wonder if that's going to be a problem? Click OK.
So now we have Date, Customer Dot One.
Alright so let's come in here and we'll call it Customers. Click OK.
Alright so we have Date, Quantity, Customers, Store, and Product. We can reorder these.
Then when I click Home, Close and Load, This will now bring all 110,000 rows right to our grid.
Alright there we are. It all fits on one screen. How cool is that?
I have Date. I have Quantity. I have the customer information.
I have what store they purchased from. And what product they purchased.
And I can still come in here, go to the icon, Show Card, and get the Item ID, the Price, the Author, the Publisher and the Format. If I would want to, since this is a table.
I can come in here and look for Format and filter to only the things that are DVDs.
Click OK.
And very quickly it filters using a field that doesn't have to be displayed in the grid.
If I would want to calculate Revenue, then I need the product List Price.
So we'll add a Price.
And then I can calculate Revenue of equal price times quantity.
So I can still do calculations in the data if I need to. Look at Customer. I can get that information.
Can I get a picture of the customer? No.
I really would love for a way to have this defined as a connection only and be able to apply it to other data sets but that's just not there yet.
And that's okay because some Tuesday, it will be there.
This is a great first step. A lot of under-the-hood here to make this work.
The other things that I love is that I can use filter using =FILTER and the data that comes over is going to have the data types. That means I can continue to extract information.
XLOOKUP isn't working as well as I want it to work. But it is working.
I will hopefully show that in the next video.
The last 37 days have taught me that when you get everything that you ask for.
Or almost everything you ask for, you really can't complain.
They gave us Exchange Rates quickly. They gave us Weather.
And now they've really started a long way towards Define Your Own.
And and I know they'll get the rest of the way there. I am really encouraged by this.
It's not quite as easy as I wanted it to be. But it is a tremendous step.
It is a sign that the Excel team is listening to their customers and reacting. So there you have it.
An amazing expandable fields using the new Custom Data Types through Power Query.
I love it and and I can't wait for it to get better.
Hey, if you like these videos, please down below click Like, Subscribe, and Ring That 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.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
There's a great story about how we got here today. It was back in August 2018. Episode 2227.
When I talked about the new stocks and geography data types.
So you start with cells that contain cities. Here on the Data tab mark them as geography.
You get a little map icon there.
You can click on the map icon to see information about that city.
But even better is you can add new data to the grid using =N2.Population or just use this Insert Data icon and get all that information. And there were even cool things.
If your data was stored as a Table you could come in here and choose a field that isn't in the grid. For example, State and Province.
And then filter out the Oklahoma cities. It was a beautiful thing.
I remember I was on the road doing live Excel power seminars.
I would always demo this.
I would ask the audience, “Hey, what do you think they should do next?” And almost universally in every single seminar, someone said Exchange Rates and someone else said Weather.
But then after a few seminars I got the usual answers of Exchange Rates and Weather and then someone said, “No, no I think you should let us define our own”.
I laughed. I thought, oh well that's never going to happen.
But I came home from the seminar. I wrote to the Excel team.
I said I know this sounds crazy, but they wanted to define their own.
And the Excel team said, “Yes, we think they should be able to define their own too”.
So, imagine that the data types team was split out into three sub-teams.
One went to work on Exchange Rates. One went to work on Weather.
And one went to work on define your own. The exchange rates team - it was quick.
March 23, 2019 episode 2274. Hey they have exchange rates.
They were clever.
They changed the stock data source provider who powers this to another company that offers exchange rates.
So, bam! Success.
They were able to very quickly get us exchange rates and that was beautiful that was in March of 2019.
And then August of 2020 they introduced the Wolfram Alpha data types.
This raised the bar on data types tremendously. The data card could now be resized.
You could drill down in the data card. They support a hierarchy.
You can even return a photo to the grid. They can return an array.
So here I have Houston. Rather than say Geography, choose Location.
Then in the card I can scroll all the way down to the bottom. Open Weather.
Click on the icon here. The card changes. It can be weather history.
I could use daily or weekly. Let's just put in daily.
Get the mean temperature all the way down. (That is in Celsius).
All right. Beautiful. So the second one is done. Now, the third one. Define your own.
When I started talking about this feature and how cool this feature would be.
I said you'd probably have the I.T. Department define a product catalog using XML.
I can select a range of cells that would contain an SKU and then up here you'd be able to define this as OurCompany ItemList.
And then I could do =A2.Category. =A2.Description. =A2.ListPrice.
You may even get an image in the grid. Hover and see the card.
Filter and sort using fields not displayed. Well, hey great news.
It's now here and they've done it.
They didn't do it the way that I would have done it.
In fact, we don't have to rely on the I.T. department.
Anyone using Power Query can define their own data type. So, hey that's awesome.
The thing I am a little bit disappointed in is I can't select a range of cells then that contain an SKU and convert to OurCompany Items. Which to me, that was sort of important.
But I can do formulas like =A2.Category, =A2.Description.
I can’t get an image in the grid. That's okay.
Because when this team started on Define your Own, the second team hadn't given us a way to get the image in the grid.
That's a relatively new feature. So I get it.
Give these people time to catch up to what these people did. We can hover and see the card.
Filter and sort using fields not displayed. Now that I've seen it.
Now that I've seen it.
I realize using Power Query I have to bring the data types into a worksheet in the grid. Don't make me do that. Just let me make it be a Connection Only.
And yes I really need to select a range of cells that contain the SKU and convert.
That, to me, is really important. Use XLOOKUP to retrieve data from the data type.
Go ahead and hook up that the feature where we can get an image of the product in the grid.
I am probably never going to use it but there'll be people who want to use that.
As I think about a metaphor.
If I had asked the Excel team for a sports car.
I would specify that I wanted a 12-cylinder engine. Zero to 120 miles an hour in 4.1 seconds.
A keyless starter so I won't have to put the key in.
Twelve-speaker sound system. Mahogany dashboard. 18 airbags. Anti-theft system.
And then what they gave me is even better. It's 16 cylinder engine.
But when they got to the second one they said yeah there's not a keyless starting system. In fact, there's not even a starter.
We're just releasing this right now because it is a kick-*** speaker system.
Sit in the car and listen to the radio. And and the mahogany dashboard is not done yet.
But it has even better airbags and anti-theft system than than you wanted.
And you'll enjoy that experience.
Let's think about the way that Microsoft used to release Excel. It would come out every three years or so.
Basically it was a three-year development cycle.
So in the first year they'd be thinking about ideas. Second year they'd actually build those.
Third year they would test them. If the test failed.
If it wasn't working you would have to wait another three years for the thing to come out.
It's much better now with Microsoft 365 because there's a release every Tuesday.
As soon as the feature has some useful parts they can release it let people try it.
Let people come up with more ideas that will shape the future of the feature.
It's a better way to go.
They keep working on the feature and if they release a feature that doesn't have everything?
On another Tuesday, not next Tuesday, but some other Tuesday, those new features will come out.
So the old Bill?
This this video would be “What? You've got a sports car and there's no way to actually start it?” But today, I get it. It took a whole bunch of under-the-hood work to get to this point.
They have every intention of putting in a starter and letting us drive this car.
And there are useful features in this V1 that we did not have before.
There are use cases here that some specific users will absolutely love. Alright so let's start over.
Learn Excel from MrExcel podcast episode 2378.
Create amazing expandable fields so your data isn't too wide all the time.
Hey, welcome back to MrExcel netcast. I am Bill Jelen. A great new feature from the Data Types team.
You know, a long time ago. Back in 1988 – 1989. I actually worked in the I.T.
department for a couple of years. Then I left I.T. And I went and worked in Accounting and Finance.
It was interesting to see both sides of that struggle between I.T. and the other departments.
So today, I have a fictitious I.T.
department who's giving me 110,000 rows of this: Store ID. Date. Product ID, Cust ID and Quantity. You know I hate the Product ID.
I understand why the I.T.
department stores it as a Product ID rather than give the description that I actually need. And the customer name that I actually need.
And the store name that I actually need. This is a nice tight way to to provide the data.
I.T. offers me three different workbooks with lookup tables for product, customer, and store.
You've heard me say before there's two kinds of people in the world: Those that can do VLOOKUP/XLOOKUP/INDEX-MATCH.
(Pick your poison, I don't care which) and Those who can't.
So if you give that 110,000 rows to someone who can do these lookups. Yes they are fine. They can run their own reports.
But there's a lot of people who can't. Alright so the I.T. department.
I love when the I.T. department uses the word “User”.
It's never a positive word. “Users” really should just be “those darn Users”.
The I.T. people: I can imagine there's a meeting in I.T.
and they say, “Those users sometimes they need data from all the lookup tables and they can't do VLOOKUP/XLOOKUP/INDEX-MATCH”.
So someone says well let's build a super wide view with every field from every lookup table that way they won't have to bother us to add more fields in the future. And that's how we head down this path.
Now, here's the plan. We are going to do all this using Power Query.
From the Sales workbook, I am going to create a connection only.
And then Stores, Products and Customers create connection only.
And then Merge. And merge and merge. And end up with a data set that is this wide.
When I started this video my plan was to do one of those freaky time lapse things where everything happens at 10x the normal speed.
But I decided I am going to turn off the video and I am going to go do this.
There are other videos I have that would show you how to do these things and that's not the point for today.
I will be right back. Alright, timewarp.
There we have Sales, Customers, Stores, Products. The first merge. The second merge.
And then we're taking that last merge and we're merging with Customers.
Choose CustID and CustID. Left Join. Click OK.
Out here for customers I am going to bring in all the fields except for the CustID because we already have that one.
And don't use original column name as a prefix. Click ok and there.
We have now an insanely wide data set.
If I would return this back to the grid it's just going to be terrible.
It's going to go be way too wide. People are going to hate it. And this is where this great new feature comes in.
What we're going to do is we're going to collapse these fields down.
I have a lot of information here about stores.
I am going to select store id and control-click on selling square feet, mall developer, store name, the era when was the store first built, and what region are they in. I think that's all of my information for stores.
Out on the Transform tab this great new feature called Create a Data Type.
Create a Data Type. So I choose those fields. Go to Create Data Type.
We will take a look at Advanced. The data type name is going to be called Store.
And what do I want to display?
I don't really want the Store ID. I actually want the Store Name.
Give me something useful. Give me a way to get the Store ID if I need it.
But give me Store Name.
Store ID, Selling SF, all of those fields are there because I pre-selected them. They are going to be there.
And what's going to happen is those fields are going to get collapsed down into a single column with a great new icon there. That says that we have a data type. Called Store.
Alright now I am going to do the same thing for Product and Customer.
So I choose the the Product ID, and then Description, Price, Author, Publisher, and Format.
Create a Data Type. This is going to be called Products.
I am going to display the Description instead of the Product ID.
In Advanced we can see that all the fields I selected are already there.
And now we have store and products.
Finally Customer through Zip Code. Those fields are all related. As well as Customer ID. Create Data Type.
This will be called Customer and the display column will be called Customer.
I wonder if that's going to be a problem? Click OK.
So now we have Date, Customer Dot One.
Alright so let's come in here and we'll call it Customers. Click OK.
Alright so we have Date, Quantity, Customers, Store, and Product. We can reorder these.
Then when I click Home, Close and Load, This will now bring all 110,000 rows right to our grid.
Alright there we are. It all fits on one screen. How cool is that?
I have Date. I have Quantity. I have the customer information.
I have what store they purchased from. And what product they purchased.
And I can still come in here, go to the icon, Show Card, and get the Item ID, the Price, the Author, the Publisher and the Format. If I would want to, since this is a table.
I can come in here and look for Format and filter to only the things that are DVDs.
Click OK.
And very quickly it filters using a field that doesn't have to be displayed in the grid.
If I would want to calculate Revenue, then I need the product List Price.
So we'll add a Price.
And then I can calculate Revenue of equal price times quantity.
So I can still do calculations in the data if I need to. Look at Customer. I can get that information.
Can I get a picture of the customer? No.
I really would love for a way to have this defined as a connection only and be able to apply it to other data sets but that's just not there yet.
And that's okay because some Tuesday, it will be there.
This is a great first step. A lot of under-the-hood here to make this work.
The other things that I love is that I can use filter using =FILTER and the data that comes over is going to have the data types. That means I can continue to extract information.
XLOOKUP isn't working as well as I want it to work. But it is working.
I will hopefully show that in the next video.
The last 37 days have taught me that when you get everything that you ask for.
Or almost everything you ask for, you really can't complain.
They gave us Exchange Rates quickly. They gave us Weather.
And now they've really started a long way towards Define Your Own.
And and I know they'll get the rest of the way there. I am really encouraged by this.
It's not quite as easy as I wanted it to be. But it is a tremendous step.
It is a sign that the Excel team is listening to their customers and reacting. So there you have it.
An amazing expandable fields using the new Custom Data Types through Power Query.
I love it and and I can't wait for it to get better.
Hey, if you like these videos, please down below click Like, Subscribe, and Ring That 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.
