@@MyOnlineTrainingHub I saw a video from Microsoft about this about six months ago and sort of gave up on seeing it as each month went by and it did not come out. And then: boom! You posted today, and I went and looked and there is is. I am one of the 50% lucky, like you : )
Thank you Mynda for taking the time to demonstrate an application to the new feature! Another huge win for the MS team and I can only imagine it getting better in the months to come. I'll have my head buried in this for weeks after general release. Goodbye family time LOL!
Awesome feature! This will be especially useful in our manufacturing business where parts (unique part IDs) are central to queries/calculations/dashboards & part attributes (e.g., description, unit of measure, commodity) are frequent derivative queries. For example, a work-in-process status report that lists open work orders, part numbers, quantities, dates, completion status, etc. A derivative query would be “what part is that?” or “what commodity?” or “unit cost?” To enable quick answers to such questions, we tend to construct wide tables (i.e., many columns) which make sizing (zoom) % navigation (scroll) more difficult. Custom Data Types will enable development of narrow tables consisting of only those columns needed for the report’s primary purpose (thereby making those reports less overwhelming, more readable, & more useful). If the report user needs to find other non-displayed part attributes, then simply click on the CDT icon to bring up the data card. Voilà, the attribute info is there! I hope this makes its way to GA soon, but I will try to temper my expectations after the long roll-out of LET. 🙂 As always, Mynda, a great tutorial. Your TdF example was a great illustration. Thanks!
Good vid, Mynda. It's just terrific! I am one of the lucky ones for once, and I've been experimenting with this technology for a week or so. Mind-blowing.
Excellent video Mynda. Great demonstration of the feature as well as a great context in which to show it and how to integrate it into an application. Many layers of learning here. Well done and thanks again!
I follow your videos for quite some time, and I'm glad to take this opportunity to express my deep appreciation to your excellent training method and exceptional way of teaching.
Waow ! I usually use Excel for 5 years from now... And i never heard about this fantastique formula sort(filter()) You simply excel. I also think that you are one of the best in Excel advanced courses. I love you youtub channel.
Mind blowing! I am super excited and can't wait to have this feature available generally! And as usual Mynda, you have delivered the knowledge in your best style, making it practical, precise and perfect! 😊👍
I had seen a couple of mentions for Custom Data Types but couldn't see how / why it may be of benefit. Thank you for the dynamic report and... throwing in the new formulas, xlookup, Array fill and valuetotext. Very helpful!
HI Mynda. Excellent once again !!!! . Your knowledge of Excel is mind-blowing and you are an excellent teacher. Many thanks for all your tutorial videos, which are of great help to us all. Regards Martin (South Africa)
Brilliant video once again! Thank you, Mynda. Just found out that I have that feature already and will start trying now - so exciting! Regards from Germany!
In 5:17 you said that powerpivot can't access the underlying rich data of data type, only displayed data can be accessed. What if we instead load the data type to data model, would it be possible for powerpivot to access those data of that data type?
Power Pivot can only use rich data that has already been extracted to the grid. You can’t load a single column and then drill down into it from power pivot. Hope that clarifies things.
@@MyOnlineTrainingHub Sure will - maybe a bit of pocket money providing a seminar! I started with Visicalc and transitioned through Lotus 123, Quattro Pro and the Excel. I really think that in the last 3-4 years, there have been such major advances in Excel that they compare to the creation of Visicalc in the 70's. If only I had been starting my career in say, 2005. Young people today ... luxury (an acknowledgement to the 4 Yorkshiremen).
Thanks, Santchev! If you don't have VALUETOTEXT then it sounds like you don't have Microsoft 365 and you also won't have the Power Query custom data types.
Hey Mynda thank you for your feedback. I have Office 365 for business and also have the custom data type in Power query. I am office insider on current Channel Level, but still don’t have these new formulars. Weird...
Ah, ok. That is weird. VALUETOTEXT is designed to work with the data types, so not sure why you don't have it yet. It may be a flighting issue where functionality is not released all at once to all users on the same channel. Hopefully you'll get it soon.
Hi, Maam. I follow your video related with power query. Those are very helpful..If you make some more video on M language in power query that would be more helpful.
Great to hear :-) If you want to get up to speed with Power Query quickly, please consider my course: www.myonlinetraininghub.com/excel-power-query-course
this is wonder, just in time for what I am trying to build. Can you kindly demonstration if I have a list of 10 Teams, how can I show them all in the table and all the teams to show ? Thank you
Glad it was helpful! I'm not sure what you're asking. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
That is so great! Can't wait to get this feature. I was talking with Guy Hunkin about this idea last year in Slovenia. Is it also possible to adress the data if there is only a connection? Do I have to load a reference column into a new table first? Thank you :-) and greetings from Germany, Andreas.
@@MyOnlineTrainingHub Yes, Not only you have shown how to create your own data type but also how to build a dynamic document with the data. That's really interesting approach
This great - thank you! Question please: What if you want to make the Team Name as primary drop-down? I tried it, but the team name shows over and over ? Is there a way to fix that? THANKS!
Thank you! If you look at my example file, you'll see on the Team Data sheet the team name is the data type level and there is a drop down in cell D4 where you can select it and get the underlying data. If that's not what you mean, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Do you know when this feature will become generally available? Should it be enabled by the organization's IT department? In my organization I see only 2 Data Types (Stocks and Geography) and there is no "Create Data Type" button in Power Query (I've already checked my Office 365 for updates and got the message that I'm using the most up to date version). Thanks!
Does the defined custom data type show up under Data Types in the Data ribbon tab? That is, are they reusable, or do they only live within the table output from Power Query?
hi Mynda, great video, thank you very much. I have a question. How do I activate the option of data type? I fit all the requirements of the system and the Data Type icon does not appear. Any Idea?
Hi Edurado, Power Query Custom Data Types are currently in preview on the Beta channel for Microsoft 365 Windows users, however only 50% of Beta channel users will have received this new feature. I just happened to be in the lucky 50%! When the feature is generally available it may be restricted to a specific licence, but I don’t have details on that yet.
Awesome stuff, thanks Mynda. Once challenge on my Power Query is that cannot find the "Create Data Type on clicking the Transform Tab". Any idea how to find on Office 365?
Thank you! This feature is still in beta, so you need to be an Office Insider and in the lucky 50% of users on the beta channel. insider.office.com/en-us/
I know this is old but I finally have a reason to use it but noticed the card does not handle carriage returns in columns that were included that have either #(lf) or #(cr). Is is even possible?
I'm not aware of a way to make the cards display the carriage returns, sorry 🤔Might be better to instead, split the data into rows where there are carriage returns.
Hi Mynda, I am trying to build an excel spreadsheet for stock valuation and would like to link the financial statement figure of a stock from internet(e.g. google finance or yahoo finance) to excel spreadsheet which can update automatically. Do you have any videos I can refer to? Thanks in advance! Appreciate with the quality of your video.
That is awesome. It was literally my first question when they introduced the stock data types! When will we be able to do it with our own data? Fantastic announcement. One question, do you have to output the data types from power query, or can you leave as a connection only and reference the connection rather than the query table? Thanks for this great video!
No luck. I am on the beta insider programme and updated the excel installation but no Create Data type button showing. On the insiders office page it says You have to be in an organization with office 365?
Bummer. It's still not been made clear what they mean by 'be in an organisation' and how that translates to specific licenses e.g. E3 or E5 etc. as I suspect it may evolve.
Awesome Mynda, thanks for sharing! Do you happen to know if there are limits for how much data can be 'stored/associated' with each data type cell - ie how many columns and how much per cell - or is it just as per normal excel and therefore can be a huge data table?
@@MyOnlineTrainingHub Thanks Mynda, looking forward to gen release and being able to play with it. Your example is a good insight on how much easier and nicer you can display and format a dynamic display of data compared to say using a user form or heaps of lookup formulas.
WOW. Thanks for the awesome video. No wonder you were so excited to share. In cell E3, was it necessary to use XLOOKUP instead of just doing > ? Or were you just showing different ways of interacting with the data, as you did with using VALUETOTEXT() instead of C3.Name?
Cheers, David! Correct...I was just trying to demonstrate different approaches and cover where people might get caught out by the differences between a data type and regular text strings.
@@MyOnlineTrainingHub Yes, that is a really key distinction. Essentially, one is a string and the other is key:value object, like JSON, a python dict, perl hash, etc. Thanks again. The new Data Types functionality -- including the Wolfram Alpha connections -- is a transformational feature set.
It's only available to a random 50% of Microsoft 365 users on the Beta channel. If you don't see it then you either don't have Microsoft 365, or aren't on the Beta channel, or aren't in the lucky 50%.
Hi Madam, Is there any option available in pivot the custom data types? I have the data like ID| Porg Name| PG Details Columns 11|PG1|PG1 Details 11|PG2|PG2 Details 11|PG3|PG3 Details If i create a custom data type based on ID my ID is repeating but I want one row for one id and I want to PG1, PG2, PG3. Custom data type details as value in that column
This feature is only available in Excel for Microsoft 365 and only 50% of Beta channel users currently have it. If you want to get it when it's generally available you'll need to upgrade to Microsoft 365.
Mynda, do you know how to perform reverse operation: Import Excel table with Custom Data Type column to PowerQuery? 🙂 P.S. By default, if you copy/paste you output table to the new Excel file and import it to power query, column with Custom Data Type will contain only Errors (DataFormat.Error: Invalid cell value '#VALUE!'.) 🤔
In.my excel automatically does not show formula in.power query ....like text. It should automatically comes rest formula but I have to type manually why
You can either update Excel and if that doesn't work, then you need to get the latest version. I recommend Microsoft 365 so you always have the latest version.
Nah, CUBEMEMBER naming conventions (nomenclature) aren't related to Power Query Data Types. I guess conceptually the idea of extracting/summarizing data from a cube vs Power Query Data Types is similar, but the big difference is the data types store the transactional data in a cell, whereas the cube functions allow you to summarise a data model.
Superb presentation Mynda. Sadly all it did was introduce #UKNOWN! and #FIELD! errors to the workbook. I guess I am not one of the lucky 50%! It does seem strange seeing advanced referencing mechanisms existing side-by-side with crude direct cell referencing, though.
Thanks, Peter! I'm not usually one of the lucky 50%, so when I discovered I had Data Types I couldn't pass up the opportunity to share it. I'm sure you could add some sophistication to my example ;-)
One of the coolest new features to allow us to have records in cells. So Rad!
Rad indeed, Mike!
@@MyOnlineTrainingHub I saw a video from Microsoft about this about six months ago and sort of gave up on seeing it as each month went by and it did not come out. And then: boom! You posted today, and I went and looked and there is is. I am one of the 50% lucky, like you : )
I want to be 1 of the lucky 50%, too! But, I get to work virtue by being patient instead. 🙂
Thank you Mynda for taking the time to demonstrate an application to the new feature! Another huge win for the MS team and I can only imagine it getting better in the months to come. I'll have my head buried in this for weeks after general release. Goodbye family time LOL!
My pleasure, Tim :-) Have fun with Data Types!
Awesome feature! This will be especially useful in our manufacturing business where parts (unique part IDs) are central to queries/calculations/dashboards & part attributes (e.g., description, unit of measure, commodity) are frequent derivative queries. For example, a work-in-process status report that lists open work orders, part numbers, quantities, dates, completion status, etc. A derivative query would be “what part is that?” or “what commodity?” or “unit cost?” To enable quick answers to such questions, we tend to construct wide tables (i.e., many columns) which make sizing (zoom) % navigation (scroll) more difficult. Custom Data Types will enable development of narrow tables consisting of only those columns needed for the report’s primary purpose (thereby making those reports less overwhelming, more readable, & more useful). If the report user needs to find other non-displayed part attributes, then simply click on the CDT icon to bring up the data card. Voilà, the attribute info is there! I hope this makes its way to GA soon, but I will try to temper my expectations after the long roll-out of LET. 🙂 As always, Mynda, a great tutorial. Your TdF example was a great illustration. Thanks!
Thank you, Jim! It's great to hear you'll find this new feature useful.
Good vid, Mynda. It's just terrific! I am one of the lucky ones for once, and I've been experimenting with this technology for a week or so. Mind-blowing.
Woo hoo for you! It sure is a cool feature :-)
I lack the words out of admiration.... " I am delighted " Thank's again ! As you can see, you do not need to cycle and you belong to the TOP 50 ;-)
Thanks so much, Jue!
Mynda making or spreadsheet life every time easier! Love it!!!!
:-) glad to hear, Felipe!
Excellent video Mynda. Great demonstration of the feature as well as a great context in which to show it and how to integrate it into an application. Many layers of learning here. Well done and thanks again!
Glad it was helpful, Oliver!
Absolutely game changing stuff. This replaces about 80% of the formulas and stuff I use!
Wow! Awesome to hear.
I follow your videos for quite some time, and I'm glad to take this opportunity to express my deep appreciation to your excellent training method and exceptional way of teaching.
So nice of you, Shlomo!
Waow ! I usually use Excel for 5 years from now... And i never heard about this fantastique formula sort(filter())
You simply excel. I also think that you are one of the best in Excel advanced courses. I love you youtub channel.
Thanks so much, Jules! You can learn more about the new dynamic array functions like SORT etc. here: www.myonlinetraininghub.com/excel-dynamic-arrays
Interesting, but we will have to wait for the open version. Thanks anyway for the explanation Mynda!!
I'm sure you'll love it, Ivan!
Mind blowing! I am super excited and can't wait to have this feature available generally! And as usual Mynda, you have delivered the knowledge in your best style, making it practical, precise and perfect! 😊👍
Great to hear you're excited about this feature too, Vijay!
I had seen a couple of mentions for Custom Data Types but couldn't see how / why it may be of benefit. Thank you for the dynamic report and... throwing in the new formulas, xlookup, Array fill and valuetotext. Very helpful!
Great to hear it was helpful, John!
HI Mynda. Excellent once again !!!! . Your knowledge of Excel is mind-blowing and you are an excellent teacher. Many thanks for all your tutorial videos, which are of great help to us all. Regards Martin (South Africa)
So pleased to hear that, Martin!
Hi Mynda. Great stuff! Thanks for the preview. Can't wait for it to become widely available. Appreciate all your insights :)) Thumbs up!!
Pleased to hear you're looking forward to it, Wayne!
Hi Mynda!Wow Custom Data Types Looks Mega Exciting Looking Forward To Trying It Out When It Becomes Available...Thank You For The Great Demo/Update :)
They sure are exciting, Darryl! Glad you're looking forward to using them.
Brilliant video once again! Thank you, Mynda. Just found out that I have that feature already and will start trying now - so exciting! Regards from Germany!
Very exciting! Enjoy the new custom Data Types :-)
Brillant again "The woman in green", thank you Mynda, your Pampa´s fan.
:-) thanks so much, Martin!
Simply you are sophisticated
Thanks so much, Numan!
In 5:17 you said that powerpivot can't access the underlying rich data of data type, only displayed data can be accessed. What if we instead load the data type to data model, would it be possible for powerpivot to access those data of that data type?
Power Pivot can only use rich data that has already been extracted to the grid. You can’t load a single column and then drill down into it from power pivot. Hope that clarifies things.
Wow. Awesome, very useful. Thank you Mynda!👍
Glad you liked it, Luciano!
This has HUGE potential in my work. Shame I retired last week.
:-) Share it with your ex-colleagues. Enjoy your retirement, Tony!
@@MyOnlineTrainingHub Sure will - maybe a bit of pocket money providing a seminar! I started with Visicalc and transitioned through Lotus 123, Quattro Pro and the Excel. I really think that in the last 3-4 years, there have been such major advances in Excel that they compare to the creation of Visicalc in the 70's. If only I had been starting my career in say, 2005. Young people today ... luxury (an acknowledgement to the 4 Yorkshiremen).
:-) indeed!
What a great new feature. Can't wait to start using it. Thanks for sharing!
My pleasure, Ila!
Excellent video Mynda! Very cool
Cheers, Chris!
Hi Mynda, thank you for that effort this is brillant. otherwise the "VALUETOTEXT" function is not aivailable for me. how to manage that please?
Thanks, Santchev! If you don't have VALUETOTEXT then it sounds like you don't have Microsoft 365 and you also won't have the Power Query custom data types.
Hey Mynda thank you for your feedback. I have Office 365 for business and also have the custom data type in Power query. I am office insider on current Channel Level, but still don’t have these new formulars. Weird...
Ah, ok. That is weird. VALUETOTEXT is designed to work with the data types, so not sure why you don't have it yet. It may be a flighting issue where functionality is not released all at once to all users on the same channel. Hopefully you'll get it soon.
Indeed i’ll probably get it soon. Thank a lot Mynda for sharing these news features and tips.
Great video - this could be very useful for me in the future. Thank you Mynda!
Great to hear, Fred!
wow...very useful. Thank you Mynda!👍
You're most welcome, Mahesh!
Once again brilliant stuff!! Thank you.
Glad you enjoyed it 🙏
Hi, Maam. I follow your video related with power query. Those are very helpful..If you make some more video on M language in power query that would be more helpful.
Great to hear :-) If you want to get up to speed with Power Query quickly, please consider my course: www.myonlinetraininghub.com/excel-power-query-course
This was really amazing, wasn't aware of this feature until now and now can't wait to try it.
So pleased you're excited about this new feature too :-)
Thank you so much for your all efforts,appreciated
You are very welcome, Omar!
Love the way you creating data... 🥰🥰
Thank you! 😊
this is wonder, just in time for what I am trying to build.
Can you kindly demonstration if I have a list of 10 Teams, how can I show them all in the table and all the teams to show ? Thank you
Glad it was helpful! I'm not sure what you're asking. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Hi Mynda, thanks for the video! One quick question, does it mean we should not have any dot in the header of our Data Table?
Correct, avoid dots in the table headers if you intend to reference those fields with the dot operator in formulas.
MyOnlineTrainingHub Thanks again Mynda! 😊 Looking forward to this feature.
That is so great! Can't wait to get this feature. I was talking with Guy Hunkin about this idea last year in Slovenia. Is it also possible to adress the data if there is only a connection? Do I have to load a reference column into a new table first? Thank you :-) and greetings from Germany, Andreas.
Hi Andreas, you have to load the data to the worksheet to reference it in formulas.
Great video and great new feature.
Cheers, Heiko! Have fun with custom data types :-)
Tour de France riders 🚲 What a great idea 😉
:-) glad you liked it.
@@MyOnlineTrainingHub Yes, Not only you have shown how to create your own data type but also how to build a dynamic document with the data. That's really interesting approach
This great - thank you! Question please: What if you want to make the Team Name as primary drop-down? I tried it, but the team name shows over and over ? Is there a way to fix that? THANKS!
Thank you! If you look at my example file, you'll see on the Team Data sheet the team name is the data type level and there is a drop down in cell D4 where you can select it and get the underlying data. If that's not what you mean, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
You are amazing ..... love to watch your videos.
Thank you so much 😀
Do you know when this feature will become generally available? Should it be enabled by the organization's IT department? In my organization I see only 2 Data Types (Stocks and Geography) and there is no "Create Data Type" button in Power Query (I've already checked my Office 365 for updates and got the message that I'm using the most up to date version). Thanks!
Not sure, Sergii. You might be on the semi annual update channel, in which case updates are much slower.
interesting! I use some mdx to achieve something similar with the data model. Does this interact with the data model in any way?
No, custom data types are only usable in the Excel worksheet.
Does the defined custom data type show up under Data Types in the Data ribbon tab? That is, are they reusable, or do they only live within the table output from Power Query?
No, it's only available in the file containing the query.
hi Mynda, great video, thank you very much.
I have a question.
How do I activate the option of data type?
I fit all the requirements of the system and the Data Type icon does not appear.
Any Idea?
Hi Edurado, Power Query Custom Data Types are currently in preview on the Beta channel for Microsoft 365 Windows users, however only 50% of Beta channel users will have received this new feature. I just happened to be in the lucky 50%! When the feature is generally available it may be restricted to a specific licence, but I don’t have details on that yet.
@@MyOnlineTrainingHub thanks for your answer.
Awesome stuff, thanks Mynda. Once challenge on my Power Query is that cannot find the "Create Data Type on clicking the Transform Tab". Any idea how to find on Office 365?
Thank you! This feature is still in beta, so you need to be an Office Insider and in the lucky 50% of users on the beta channel. insider.office.com/en-us/
@@MyOnlineTrainingHub Thanks for clarifying this!
I know this is old but I finally have a reason to use it but noticed the card does not handle carriage returns in columns that were included that have either #(lf) or #(cr). Is is even possible?
I'm not aware of a way to make the cards display the carriage returns, sorry 🤔Might be better to instead, split the data into rows where there are carriage returns.
Hi Mynda, I am trying to build an excel spreadsheet for stock valuation and would like to link the financial statement figure of a stock from internet(e.g. google finance or yahoo finance) to excel spreadsheet which can update automatically. Do you have any videos I can refer to? Thanks in advance! Appreciate with the quality of your video.
You can use the STOCKHISTORY function: th-cam.com/video/xzOvM164H6M/w-d-xo.html
MyOnlineTrainingHub thanks for your reply!!
I don’t see Data Type in the “Transform tab”, is this because the new version is not available yet?
Either that or you don't have Microsoft 365.
That is awesome. It was literally my first question when they introduced the stock data types! When will we be able to do it with our own data? Fantastic announcement.
One question, do you have to output the data types from power query, or can you leave as a connection only and reference the connection rather than the query table?
Thanks for this great video!
Glad you're excited about Custom Data Types too! You need to load them to the Excel grid for them to be usable in formulas.
@@MyOnlineTrainingHub that's what I thought, that's still a big development though. A fantastic feature
No luck. I am on the beta insider programme and updated the excel installation but no Create Data type button showing.
On the insiders office page it says You have to be in an organization with office 365?
Bummer. It's still not been made clear what they mean by 'be in an organisation' and how that translates to specific licenses e.g. E3 or E5 etc. as I suspect it may evolve.
Many thanks for sharing. Awesome new feature!! Do you by when more or less this will be rolled out to everyone?
My pleasure, Jose! I don't have an ETA on when this feature will be generally available. It all depends on how it goes during the beta phase.
I have a question. Can we scrape nationality flag icons from web site, and display them into Excel cells?
Yes, I do that in this tutorial: www.myonlinetraininghub.com/excel-image-function
Awesome Mynda, thanks for sharing! Do you happen to know if there are limits for how much data can be 'stored/associated' with each data type cell - ie how many columns and how much per cell - or is it just as per normal excel and therefore can be a huge data table?
My pleasure :-) I haven't heard about any column limitations so I expect the limit is 16,384 as per a standard Excel worksheet.
@@MyOnlineTrainingHub Thanks Mynda, looking forward to gen release and being able to play with it. Your example is a good insight on how much easier and nicer you can display and format a dynamic display of data compared to say using a user form or heaps of lookup formulas.
WOW. Thanks for the awesome video. No wonder you were so excited to share.
In cell E3, was it necessary to use XLOOKUP instead of just doing > ?
Or were you just showing different ways of interacting with the data, as you did with using VALUETOTEXT() instead of C3.Name?
Cheers, David! Correct...I was just trying to demonstrate different approaches and cover where people might get caught out by the differences between a data type and regular text strings.
@@MyOnlineTrainingHub Yes, that is a really key distinction. Essentially, one is a string and the other is key:value object, like JSON, a python dict, perl hash, etc.
Thanks again. The new Data Types functionality -- including the Wolfram Alpha connections -- is a transformational feature set.
thank you so much for sharing! Really great
My pleasure!
Ugh, I don't seem to have this or XLOOKUP on my old 2016 Excel. :( Looking forward to taking advantage when I can, though.
Unfortunately, these new features are only available in Microsoft 365.
@@MyOnlineTrainingHub I did find vba code to mimic xlookup so yay 😊
i can't find the button "create data type" help!
It's only available to a random 50% of Microsoft 365 users on the Beta channel. If you don't see it then you either don't have Microsoft 365, or aren't on the Beta channel, or aren't in the lucky 50%.
MyOnlineTrainingHub thanks a lot. no words to tell you how much we are grateful to you for the great job you are doing, your videos are wonderful
Pretty good, thank you!
🙏 Glad you like it!
Hi Madam,
Is there any option available in pivot the custom data types?
I have the data like
ID| Porg Name| PG Details Columns
11|PG1|PG1 Details
11|PG2|PG2 Details
11|PG3|PG3 Details
If i create a custom data type based on ID my ID is repeating but I want one row for one id and I want to PG1, PG2, PG3. Custom data type details as value in that column
You'd have to Pivot the Porg Name column.
@MyOnlineTrainingHub I have tried it but my custom data type is converting into record
You can try posting your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Very nice. Thanks for sharing
Thanks for watching, Rajan!
Finally got the Create data type tab on 365 for enterprise version.
Woo hoo! Have fun with them :-)
hi Mynda, I didn't find Create DATA TYPE, I have a BETA VERSION for microsoft 365 and it's UP TO DATE
It has only been rolled out to 50% of insider users on the beta channel. You mustn't be in the lucky 50%.
It's a neat feature!
Glad you liked it, Doug!
Amazing feature!!!
Glad you liked it, Felipe :-)
Hi Mynda - Unable to find CREATE DATA TYPE TAB In POWER QUERY EDITOR
Please help to resolve this...
I am using Excel 2019
This feature is only available in Excel for Microsoft 365 and only 50% of Beta channel users currently have it. If you want to get it when it's generally available you'll need to upgrade to Microsoft 365.
Ok thanks
Super cool!! Thank you
Glad you liked it!
Mind blowing indeed!
Glad yo agree, Jeff!
Amzing 🤩
Glad you liked it, Nicolas!
Mynda, do you know how to perform reverse operation: Import Excel table with Custom Data Type column to PowerQuery? 🙂
P.S. By default, if you copy/paste you output table to the new Excel file and import it to power query, column with Custom Data Type will contain only Errors (DataFormat.Error: Invalid cell value '#VALUE!'.) 🤔
Only data that is expanded to columns can be read by Power Query, so you'd have to expand the columns you want first.
In.my excel automatically does not show formula in.power query ....like text.
It should automatically comes rest formula but I have to type manually why
It sounds like you have an older version of Excel that doesn't have the intellisense formulas. You can try updating Excel to see if you get them.
@@MyOnlineTrainingHub oh thanks ...but how to fix it...mean how to upgrade pls help
You can either update Excel and if that doesn't work, then you need to get the latest version. I recommend Microsoft 365 so you always have the latest version.
@@MyOnlineTrainingHub 365 is not free 😢
Thank you!
My pleasure 😊
That's really great, thanks.
Great to hear, Augusto!
One word - magic! :)
Great way to describe it :-)
Well Done
Thanks, Shakira!
Awesomee. Thank you very much.
Glad you liked it!
So it's basically the "CUBEMEMBER" nomenclature?
Nah, CUBEMEMBER naming conventions (nomenclature) aren't related to Power Query Data Types. I guess conceptually the idea of extracting/summarizing data from a cube vs Power Query Data Types is similar, but the big difference is the data types store the transactional data in a cell, whereas the cube functions allow you to summarise a data model.
Superb presentation Mynda. Sadly all it did was introduce #UKNOWN! and #FIELD! errors to the workbook. I guess I am not one of the lucky 50%!
It does seem strange seeing advanced referencing mechanisms existing side-by-side with crude direct cell referencing, though.
Thanks, Peter! I'm not usually one of the lucky 50%, so when I discovered I had Data Types I couldn't pass up the opportunity to share it. I'm sure you could add some sophistication to my example ;-)
I like this!
Cheers, Oz! Thanks for stopping by :-)
Gosh, really amazing.
Indeed :-)
wonderful
Glad you liked it, Drew 😊
Amazing
Glad you liked it!
very useful!
Glad to hear that, Steven!
Thanks for sharing
My pleasure, Tobaye!
thanks
You're welcome!
if it ain't broke, don't fix it. this is re-learning Excel. I might as well migrate to Google sheet.
I don't follow your point, sorry. This is a completely new feature that's not available in Google Sheets.
I WANT IT!
It'll be worth the wait, Rob!
MyOnlineTrainingHub: I know, I know.
Thank you Mynda!
That’s great
Cheers, Graham :-)
I K,K?
I don't know what I K, K is, sorry.
hey. i don't see "create data types" in transform. can i add this?
No, there's nothing you can do to add it. Microsoft will add it when it's generally available.
@@MyOnlineTrainingHub Thanks for the quick answer. I will wait for it :) it's looks amazing. And thanks for the great tutorial