The data types gallery with Geography, Stocks, Currency, and Wolfram is mildly interesting. But I think the real goal is to get your own company data in the Data Type. I want it to be a very rich experience, just like Wolfram Data Types:
1) Type plain text and convert to a data type
2) From the card for the data type, return an array of entities (such as all the people in Bob Marley and the Wailers music act.
3) Have those fields returned from the Data Card be Data Types as well, so you can drill down and get more information about the sub-entity
4) The data type should be able to return an image from an Image URL
5) The data type should be able to return a clickable hyperlink.
6) It would be nice if the little icon for the data type was customizable.
Bill's theory is that once you can get to your own company data in data types, no one will ever download data from Wikipedia again.
Table of Contents
(0:00) Introduction
(1:29) My vision for the future of data types
(2:16) Building a data type in Power BI
(5:27) Mark the tables as Featured
(6:54) Test the custom data type in Excel
1) Type plain text and convert to a data type
2) From the card for the data type, return an array of entities (such as all the people in Bob Marley and the Wailers music act.
3) Have those fields returned from the Data Card be Data Types as well, so you can drill down and get more information about the sub-entity
4) The data type should be able to return an image from an Image URL
5) The data type should be able to return a clickable hyperlink.
6) It would be nice if the little icon for the data type was customizable.
Bill's theory is that once you can get to your own company data in data types, no one will ever download data from Wikipedia again.
Table of Contents
(0:00) Introduction
(1:29) My vision for the future of data types
(2:16) Building a data type in Power BI
(5:27) Mark the tables as Featured
(6:54) Test the custom data type in Excel
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2450.
Creating Excel custom data types from Power BI.
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
I've been fascinated with these Data Types that they introduced several years ago.
Well, they're actually kind of on version two.
So I can choose a cell here and say that this is music.
It gets converted to a linked data type and then using the card I can come in.
And let's say find members.
I can Add all of the Members to the grid.
So not just a single entity, but an array of entities.
And they're not just coming in as text.
Each one is coming in as a linked data type.
And I can get, let's say, information from that linked data type.
And even because this in B1 is an array, if I add this spill operator (#). It will retrieve all of the images except for Carlton Barrett, who for some reason they don't have the image.
Alright now, this is this is interesting.
These are party tricks here, right?
What we really need, what we really need is our own company data in these data types.
When we achieve that and when it is as rich as this.
Where we can store an entire array.
Return an array of entities.
From the entities, get data.
My vision is that once we can get our own data in data types, all of this other stuff will just go away.
These are all party tricks, right?
Getting data from Wikipedia?
You know that's like, remember when Power Query was brand new?
Everyone who did a Power Query demo would go out and pull something from Wikipedia.
No one does that today.
Getting your own company data that will be really valuable.
And once they achieve this, I don't think we will ever go back to Geography.
Maybe - maybe currency exchange rates and maybe stocks.
But pulling Wikipedia data in?
Who trusts Wikipedia data?
It was a great two- or three-year proof of concept of what is possible.
As soon as they give us our own data?
Yeah, we will never go back.
So I already have one video on this on how to use a linked data type from Power Query.
In this video I'm going to show you how to do this with Power BI.
Tomorrow, though, I'm going to show you the future.
Let's get started.
Alright, so here I have a workbook.
This is called book catalog.
This is our own company data.
These are all the books that we publish.
Book title, author (and you'll notice sometimes there's two of them).
That's going to cause problems.
ISBN, Published date.
I broke out just the first author I could try and create a linked data type.
I have the URL and further out I have the image URL.
There's a second sheet here.
This is by the way let's see called and this is a table called Books.
I have a second sheet with a table called Book Aauthors.
So Name and URL.
And I've done File, Save As and save this to my one drive.
So if I go into File, Info and copy the path.
I really want to see that.
How can I see that?
So let's go to Notepad.
When I paste that path, that's the complete path along with this question mark Web=1.
And we're going to have to throw away.
That ?web=1 when we go into power BI.
So everything before that is what we want to use, right?
So that's the data in Excel.
We want to get this data to be a new data type in the Data Types gallery.
We're going to fire up Power BI desktop.
And I'll create a new report.
Close this and go to Get Data, From Web.
I know it seems like it's an Excel workbook.
But it's an Excel workbook stored on the web.
And we paste in that URL.
Alright, make sure to get rid of that question mark.
Choose OK.
Alright, now it didn't ask me and authenticate, and that's probably because I'm already signed in.
I think the first time that I did this I had to put in my Microsoft user ID and password.
And so we want are the two tables, Books and BookAuthors.
I'll simply load those.
Alright, we're going to come take a look at the data so we're on the left hand side.
Alright, and a couple of bits of clean up here.
So we're in the authors table.
I click on URL and the data category is a web URL – good.
And then over here I'll click on books.
The URL here again, I'll say that this is a web URL.
And the image URL.
Alright, good and then here.
I wonder if we can create a relationship?
Will the data types understand the relationship?
So from first author right here, that's the one that has a single name to the name.
And they figured out that this is a one to many relationship.
And then I'm going to click on book authors alright and this is the important part.
This is how you get this table to be in the data types entity.
Over here on the Property.
Pane, I'm going to say is a Featured table and choose yes.
This is a list of book authors.
The important row label is Name.
That's what will be displayed by default.
And the key column is also name.
Click save.
Alright, there we go - success.
And then, Books.
Let's click on that one and we want to make that be a Featured table.
So this is a list of our products.
If you open this, I want the book title.
And the key column, again, should be book title.
Alright cool.
Click save and success.
Right now we need to publish this to a new workspace.
I hadn't used Power BI in awhile and I had just published it to the workspace that I've been using years ago and that didn't work.
So I had to create a brand new workspace.
Publish let's see will save our changes.
New one that I created is called MrExcel – we will select.
Success?
Alright, that's great.
I'm back in Excel and go to the data to Data tab.
Open this up.
And wow, check it out is already there.
I didn't even have to restart Excel.
So I have a book title over in A2.
I choose books.
And it recognized that there's two of them.
Alright, so we want this one, Select.
All right, that's freaking awesome, right?
And then I can get the cover image beautiful.
Just make it taller and we'll see it nice that works great.
Let me get the author.
Ohhhhhhh.
Where is the little icon?
I want to drill down and get the URL for Mike.
How come that's not working?
Let's see down here I have the URL.
Let's add that.
That's not the image URL, that's the actual URL.
And that does not come in as a link.
I'll try and add “.Link”.
That sometimes works with regular URLs.
It doesn't know it.
And then getting the author to be an entity.
It feels like I'm going to have to come up here and then choose Books Author.
You can’t convert a formula to an entity.
It looks like I am shut down here.
Alright look, this is better than the power query data types, but it's still not there.
It's not the goal of being able to have a data type, an entity, that returns another entity that returns a field.
We're not there yet.
Closer.
Make sure to check out tomorrow, where I think we'll see the future.
Hey, I've created some new courses for the Retrieve platform.
This is a beautiful platform because it's searchable video.
You're watching a video and you just type “pivot table” and it brings you to the exact point Some amazing artificial intelligence.
As soon as I saw it, I'm like, Oh yeah, this this is the answer!
My store launches on December 20th, 2021.
Check that out.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Well hey, I want to thank you for stopping by.
I will see you next time for another netcast from MrExcel.
Creating Excel custom data types from Power BI.
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
I've been fascinated with these Data Types that they introduced several years ago.
Well, they're actually kind of on version two.
So I can choose a cell here and say that this is music.
It gets converted to a linked data type and then using the card I can come in.
And let's say find members.
I can Add all of the Members to the grid.
So not just a single entity, but an array of entities.
And they're not just coming in as text.
Each one is coming in as a linked data type.
And I can get, let's say, information from that linked data type.
And even because this in B1 is an array, if I add this spill operator (#). It will retrieve all of the images except for Carlton Barrett, who for some reason they don't have the image.
Alright now, this is this is interesting.
These are party tricks here, right?
What we really need, what we really need is our own company data in these data types.
When we achieve that and when it is as rich as this.
Where we can store an entire array.
Return an array of entities.
From the entities, get data.
My vision is that once we can get our own data in data types, all of this other stuff will just go away.
These are all party tricks, right?
Getting data from Wikipedia?
You know that's like, remember when Power Query was brand new?
Everyone who did a Power Query demo would go out and pull something from Wikipedia.
No one does that today.
Getting your own company data that will be really valuable.
And once they achieve this, I don't think we will ever go back to Geography.
Maybe - maybe currency exchange rates and maybe stocks.
But pulling Wikipedia data in?
Who trusts Wikipedia data?
It was a great two- or three-year proof of concept of what is possible.
As soon as they give us our own data?
Yeah, we will never go back.
So I already have one video on this on how to use a linked data type from Power Query.
In this video I'm going to show you how to do this with Power BI.
Tomorrow, though, I'm going to show you the future.
Let's get started.
Alright, so here I have a workbook.
This is called book catalog.
This is our own company data.
These are all the books that we publish.
Book title, author (and you'll notice sometimes there's two of them).
That's going to cause problems.
ISBN, Published date.
I broke out just the first author I could try and create a linked data type.
I have the URL and further out I have the image URL.
There's a second sheet here.
This is by the way let's see called and this is a table called Books.
I have a second sheet with a table called Book Aauthors.
So Name and URL.
And I've done File, Save As and save this to my one drive.
So if I go into File, Info and copy the path.
I really want to see that.
How can I see that?
So let's go to Notepad.
When I paste that path, that's the complete path along with this question mark Web=1.
And we're going to have to throw away.
That ?web=1 when we go into power BI.
So everything before that is what we want to use, right?
So that's the data in Excel.
We want to get this data to be a new data type in the Data Types gallery.
We're going to fire up Power BI desktop.
And I'll create a new report.
Close this and go to Get Data, From Web.
I know it seems like it's an Excel workbook.
But it's an Excel workbook stored on the web.
And we paste in that URL.
Alright, make sure to get rid of that question mark.
Choose OK.
Alright, now it didn't ask me and authenticate, and that's probably because I'm already signed in.
I think the first time that I did this I had to put in my Microsoft user ID and password.
And so we want are the two tables, Books and BookAuthors.
I'll simply load those.
Alright, we're going to come take a look at the data so we're on the left hand side.
Alright, and a couple of bits of clean up here.
So we're in the authors table.
I click on URL and the data category is a web URL – good.
And then over here I'll click on books.
The URL here again, I'll say that this is a web URL.
And the image URL.
Alright, good and then here.
I wonder if we can create a relationship?
Will the data types understand the relationship?
So from first author right here, that's the one that has a single name to the name.
And they figured out that this is a one to many relationship.
And then I'm going to click on book authors alright and this is the important part.
This is how you get this table to be in the data types entity.
Over here on the Property.
Pane, I'm going to say is a Featured table and choose yes.
This is a list of book authors.
The important row label is Name.
That's what will be displayed by default.
And the key column is also name.
Click save.
Alright, there we go - success.
And then, Books.
Let's click on that one and we want to make that be a Featured table.
So this is a list of our products.
If you open this, I want the book title.
And the key column, again, should be book title.
Alright cool.
Click save and success.
Right now we need to publish this to a new workspace.
I hadn't used Power BI in awhile and I had just published it to the workspace that I've been using years ago and that didn't work.
So I had to create a brand new workspace.
Publish let's see will save our changes.
New one that I created is called MrExcel – we will select.
Success?
Alright, that's great.
I'm back in Excel and go to the data to Data tab.
Open this up.
And wow, check it out is already there.
I didn't even have to restart Excel.
So I have a book title over in A2.
I choose books.
And it recognized that there's two of them.
Alright, so we want this one, Select.
All right, that's freaking awesome, right?
And then I can get the cover image beautiful.
Just make it taller and we'll see it nice that works great.
Let me get the author.
Ohhhhhhh.
Where is the little icon?
I want to drill down and get the URL for Mike.
How come that's not working?
Let's see down here I have the URL.
Let's add that.
That's not the image URL, that's the actual URL.
And that does not come in as a link.
I'll try and add “.Link”.
That sometimes works with regular URLs.
It doesn't know it.
And then getting the author to be an entity.
It feels like I'm going to have to come up here and then choose Books Author.
You can’t convert a formula to an entity.
It looks like I am shut down here.
Alright look, this is better than the power query data types, but it's still not there.
It's not the goal of being able to have a data type, an entity, that returns another entity that returns a field.
We're not there yet.
Closer.
Make sure to check out tomorrow, where I think we'll see the future.
Hey, I've created some new courses for the Retrieve platform.
This is a beautiful platform because it's searchable video.
You're watching a video and you just type “pivot table” and it brings you to the exact point Some amazing artificial intelligence.
As soon as I saw it, I'm like, Oh yeah, this this is the answer!
My store launches on December 20th, 2021.
Check that out.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Well hey, I want to thank you for stopping by.
I will see you next time for another netcast from MrExcel.
