Organizational Data Types in Excel M365 & Power BI. Excel Magic Trick 1735

Learn how to use Organizational Data Type in Excel to lookup records from an Power BI Online source.
1. (00:00) Introduction to how an Organizational Data Type works.
2. (00:50) Trick requires Microsoft 365 Excel, Power BI Desktop and Power BI Online.
3. (01:07) Source Table in Excel.
4. (01:13) Import Table into Power BI Desktop.
5. (01:34) In Model View apply “Is Featured Table” option to convert table to a Data Type that Power BI Online and Excel 365 can interpret.
6. (02:00) Publish to Power BI Online.
7. (02:16) Select workspace.
8. (02:22) Log into M365 Excel and use Organizational Data Types.
9. (02:47) Apply Data Type to see cards from the Single Source of Truth at Power BI Online.
10. (03:11) Comments?

[utubeAgape]

We are all so fortunate to have you keeping us as up-to-date as possible with all the explosive functionality growth that is happening with Excel - THANK YOU!

[TSSC]

Employee data is a good example. Personally, I think making product (portfolio) data available would be a sure way to make many new friends in any company. Likewise for similar data in organisations.

[vijayarjunwadkar]

Really cool tip Mike! Excel and Power BI together is a formidable combo to manage data in enterprise. Thank you for this video!

[daniellucas]

Thanks Mike, would never have realised that Excel and Power BI can interact in both directions and can see great applications for this !

[robrayborn]

This is GREAT! It would be nice to see some practical examples of how this can be used.

[miiihaaas]

it works for me, so i am able to make table with productivity of final products by ID of final products and by equipment. really cool!!!

[darrylmorgan]

Boom!Simply Amazing " Life Just Gets Better And Better"...Thank You Mike :)

[barttitulaerexcelbart]

Thanks Mike. This is an awesome feature with great potential. I am wondering how many Excel users have these features (365 and Pro). One small tip: sometimes you don't know the key or ID, if you just type and select a name, Excel finds it as well! Alternatively you can set up a pivot, as you showed us in your previous video.

[spilledgraphics]

No more XLookupps, VLookups, INDEX&MATCH ? woah ! but I am liking this integration of Power BI and Excel it´s like Batman and Superman, of course Excel being Batman 😁🤙

[jfernan]

Thanks Mike, awesome video. I've been using the organisational data types for months, but for some unknown reason, a couple of weeks ago the data type gallery disappeared from the Data menu and I'm not able to use them from the desktop application. Do you have any idea why this might happen?
Thanks in advance

[ivanmamchych]

New powerful perspectives
Thank you, Mike

[EricHartwigExcelConsulting]

This is really awesome! Thank you so much for sharing this!
Excel has really come along way since when I first starting use it years ago, I can not wait to see what they come up with next!

[anthonysaiz]

Hello, Thank you for this video.
Can you say us how we can find the Id Employee? Without this element we couldnt beging.
Anthony

[wayneedmondson]

Awesome Mike! Thanks for sharing this. I work outside an organization, but nice to know of this feature and capability. Love your new Latte vibe :)) Thumbs up!!

[workstuff]

I have just been playing around with this and I have a question around the data model. If I have a product table and a sales table, BI best practice would be to hide the product (key) field on the sales table. If I then enable this feature for both the sales and product table, the sales product field is invisible to Excel. How would I bring this data into Excel and see the product data and the corresponding sales data?

[omotossso]

This is awesome... 😲😲

This actually solves what all difficulties I have at work

But another issue is, my M365 is registered with my personal mail ID which Power BI isn't accepting when I try to sign up with it.

How do I make it work or have access to the Power BI workspace with a different mail ID in my M365?

If I could get this, then no troubles

I really appreciate your research and tutorials

[BarredowelGaming]

How to add Data Types In MS excel, and also after adding Data Types category did not Show in the Data menu can you give some information on it? Please.

[dontgetcaughtslippn]

Can you do this with other data sets, like product?

[Ahmad_Askar]

Hi Mike, awesome as usual, can we do the same using OneDrive? instead of Power BI online Pro ...

[Al-Ahdal]

Sir, F9 formula evaluation key should be available through out the Excel and Power BI platform, whether power query etc. Plus F9 key doesn't work in PQ, nor in LET formulas. YOU being MVP and Excel expert, I request you to kindly advise Microsoft to add these functionalities. I would further urge you to post these suggestions on Microsoft platform, so their development team can work on it. Thank you.