Simplifying enterprise data discovery and analysis in Microsoft Excel

[Brian Jones (OFFICE)]

We know that Excel is one of the most widely used tools in the world for understanding data and making decisions based on that data. It's used by hundreds of millions of users each month in almost every type of company imaginable. To better meet the needs of our users, Excel has evolved and transformed to understand you and your data in a way that has not been available until now. We are excited to share new capabilities that will ease access to trusted datasets and new ways to analyze and manipulate that data.

Over the years you have told us that you wanted Excel to have better integration with Power BI, more flexibility to get data in the grid, and better management for sensitive data, and today we are announcing this exciting step towards that goal! By simplifying access to Power BI data, we have streamlined the ability for users to consume, manipulate, and analyze reliable data all within Excel. In short, Excel now better understands you and your data.

Connect to Power BI Datasets Without Leaving Excel

Today, we are introducing two new ways to access Power BI data from within Excel — Excel data types and through PivotTables connected to Power BI. With the click of a button users can now discover Power BI datasets all without having to leave Excel! By using Power BI certified and promoted dataset capabilities, organizations enable more users to find and use relevant and refreshable data to make better decisions.

Using Data Types, you can quickly find details from featured tables in Power BI datasets within Excel’s data types gallery. Data Types give users the flexibility to organize information in any way they want within the Excel grid. This allows you to use formulas, build reports and analyze your data in a familiar and simpler way.

 Power BI data type in Excel

Analyzing Power BI datasets in Excel using PivotTable

We have also added the ability for PivotTables connected to datasets stored in Power BI to be created in Excel, allowing you to continue to use of the tools you know and love. This new capability creates a live connection to the underlying dataset in Power BI, meaning updates within the source are published to the reports connected to it. Excel PivotTables also respect the Microsoft Information Protection sensitivity labels applied to the file when the PivotTable was created. This evolution of Excel promotes the adoption of certified datasets within your organization, helping to ensure authoritative data sources are used when making critical decisions in your business.

These new capabilities will start rolling out to Office Insiders later this year. Please refer to the Release Notes for Office Insiders for the latest update.

Microsoft Information Protection (MIP)

Sensitivity labels from the Microsoft Information Protection framework, available in both Power BI and Office apps, lets you classify and protect your organization's data while making sure that user productivity and their ability to collaborate is not hindered. Sensitivity labels are currently available and will persist when applied from Power BI content and exported to Excel, PowerPoint, and PDF files. However, with this release we are taking Sensitivity labels one step further. When you connect to a Power BI dataset from Excel, that dataset's Sensitivity label will be inherited and applied to the Excel file and all associated outcomes like headers/footers and encryption.

Power BI dataset's sensitivity label shown in Excel 

With the new, more robust integrations between Excel and Power BI we have given your organization more potential, more visibility and more information protection in Excel’s familiar and easy to use interface, all with fewer clicks. We welcome you to try this out for yourself and sign up to be an insider to get all the hottest tips and promotions!

Excel’s new capabilities will start rolling out to Office Insiders later this year. Refer to these Release Notes for Office Insiders for the latest update.

To learn more,

• Subscribe to the Excel Blog for the latest product announcements and updates
• Join the Excel Community to connect with others, ask questions and get answers
• Follow us on social @msexcel and /microsoftexcel

[Sergei Baklan]

That's cool! The only question how late this year will start the rolling out...

[Alan Murray]

Very interesting

[Ilgar_Zarbaliyev]

Amazing...

[excelpbi]

Brian,

Power BI as a DataType is brilliant !!. While live connect to a PBI Dataset has been around for 2 years with Connect to PBI - making it native inside a Pivot Button is fantastic.

All that is remaining is to bring all the distributed components together on a unified Power BI Tab - Remember "We are better together" ....and all those nice sounding conference only taglines   - you also need to Include Custom Visuals instead of prematurely deprecated Power View

Feel free to suggest this to the Office UX team !

Best Regards

Sam

PS: A intelligently designed Home Tab would not hurt either

[excelpbi]

You forgot to mention Power Query Data Types which is also amazing

Best Regards

Sam

[wumolad]

Cool

[Wes Miller]

I'm assuming that "within the Microsoft E5 license" should say "within the Microsoft 365 E5 license"?

It'd be ideal if posts like this that cross technology elaborated a bit more on exactly what the licensing requirements were, particularly since Microsoft 365 E5 (the most expensive variant of Microsoft 365 suites) isn't licensed by all organizations, and it's not necessarily clear which feature requires which license.

[Ed Hansberry]

@excelpbi - what Power Query data types are you referring to?
@Sergei Baklan - the Access Power BI DataSets is already in my Insider build. Showed up yesterday. Seems the same as the Analyze in Excel tools, but integrated and much more stable.

[Brian Jones (OFFICE)]

@Sergei Baklan , @Ed Hansberry is correct... the access to PBI data sets and "insert Pivot table from PBI" are already available to Insiders. PBI data types will be available to Insiders later this month.

@Ed Hansberry another piece of this release that @excelpbi was referring to is that you will be able to use Power Query to generate your own data types from any data source you connect to. We didn't cover it in the blog post, but we covered it in the video session yesterday afternoon. That functionality is probably a month or so out, so we didn't talk about it as much and will cover in more depth once it's closer to release. I agree with @excelpbi though, it's very powerful.

@Wes Miller , sorry about any confusion there. Are you saying the confusion was that we weren't clear between M365 and O365, or that we just said Microsoft E5 rather than Microsoft 365 E5?

[Sergei Baklan]

@Brian Jones (OFFICE) , @Ed Hansberry  - yes, it appeared in half an hour after I posted the comment, both on Insider Fast and Monthly (Targeted). What is not clear is E5 licensing, doesn't matter how we call this plan. In particular, it's not clear why practically exactly the same functionality of Analyse in Excel requires mainly Power BI Pro license and doesn't care if user on E5 or not. IMHO, differentiation in production on E5 and the rest will kill new functionality or people will find the other way to use it, e.g. with Pro license Analyse in Excel for Pivot Tables and Power BI Desktop for creating new data types. I don't know how it will be, but many chances the story with licensing of Power Pivot will be repeated. 

[Ed Hansberry]

Very cool @Sergei Baklan - didn't even think to test the Monthly Targeted build.

Not sure I understand your issue on the licensing. This seems to require a Power BI Pro license which is included in E5. I have an E3 license with a Power BI Pro license and it works fine.

[ricafonyat]

How to see "Organization" in Excel Data Types available?

[Ed Hansberry]

Is anyone able to insert a TimeLine slicer from a workbook connected to a Power BI Dataset? My Excel (Insider 2006) just changes the cursor to the spinning circle and never recovers.

Regular slicers seem to work ok, but not timelines.

[Sergei Baklan]

@ricafonyat , it's not available and will not be available from the box. That will be custom data type which you create yourself in Power Query joining few columns (aka fields) into one type list (or set? - don't know how to name). You may create as many such data types as needed,

[chauggaard]

@Brian Jones (OFFICE) Finally, been waiting for this for a while! But does the power bi data model (i.e. relationships) get transferred to excel? Because this is what has been missing in the "Analyze in Excel" feature, the tables from the power bi dataset get exported to excel but not the relationships between the tables. Also, how do I preview this feature? (i.e. how do I become an insider?)

[Sergei Baklan]

@Ed Hansberry , it's the same with me, I tried to add time line and Excel hangs in never ended loop (I waited about 20 min). Will send a frown.

[Sergei Baklan]

@chauggaard , to become an insider that's a bit different procedure for business users and consumers. Please check this site Become an Office Insider , all information is here.

Functionality of the new connector to Power BI dataset is exactly the same as for Analyse in Excel, some cosmetic is different. You are connected to data model published on Power BI services and could build Pivot Tables based on it. The only you can do all above within the Excel. You can't add additional tables to data model and add additional relationships, only use already existing ones. 

[Ed Hansberry]

Thanks for the confirm @Sergei Baklan 

I too sent a frown. @Brian Jones (OFFICE) seems a bug with the timeline slicer that will hang excel and cause you to lose all changes since last save.

[Sergei Baklan]

@Ed Hansberry , timeline works! Just add it not from the ribbon, but right click Calendar or whatever you have, Add as Timeline

and it's here

I checked both on Insiders Fast and Monthly (Targeted)

[Wes Miller]

"Are you saying the confusion was that we weren't clear between M365 and O365, or that we just said Microsoft E5 rather than Microsoft 365 E5?"

Yes. 

1: There are a lot of services and suites - it's ideal to always be explicit if you mean "Microsoft 365 E5", particularly now with the (confusing, IMHO) rebranding of many Office 365 services/features as "Microsoft 365" - especially since there's no "Microsoft E5" suite.

2: A lot of customers - whether it works out ideally or not in the end, license things on an à la carte basis rather than as whole suites. So for things like this, which would appear to require subscriptions to Microsoft 365 Apps for enterprise (the artist formerly known as Office 365 ProPlus), Power BI Pro, and
Azure Information Protection Premium (P2? P1?), it is really useful for customers if you can define what licensing is necessary in order for a feature to be available (or even just licensed correctly, as so many things don't do comprehensive license enforcement anymore).

Thanks!

[excelpbi]

Brian - on a lighter note is there a "ReName" Vertical in Microsoft whose job is to Rename products and make them sound new

So Data Explorer becomes Power Query (which the Rename vertical thinks is too intimidating because of the use of the word power) which becomes

Get and Transform (and loses it decicated tab in Excel) which becomes Get Data in PBI Desktop and Data Flows in the Service

Likewise Gemini becomes Power Pivot and fortunately does not become the less intimidating Crunch and Punch and becomes Data model in PBI Desktop

So too with Office 365 Pro Plus becoming Microsoft 365 Apps for enterprise - Has anyone evaluated the cost of Renaming - The hundreds of places where some poor bloke has to go and update the names (docs, webpages, application flash screens )

Or is there a Parameter set where you can change the name once - and its reflected everywhere

Cheers

Sam

[RakeshSinghr]

If i use analyse in excel .odc connection to create a pivot table and a chart with a simple excel slicer. Would i be able to then upload that excel to power bi service and interact in excel online?

Currently external connections are not supported in excel online so if i click on slicer within workbook published onto power bi service, it errors out. Even though its like a live connection to power bi dataset.

If the dataset is promoted within org, does this error go away?

[Phil Peddle]

Is there any chance we will ever get the ability to connect to Dataflows using PowerQuery/GetData?

Most of our key users want to create their own Excel-based analysis (Not PowerBI), we need a way to give them access to a consistent, up to date version of the data. Analyse in Excel is not a good solution (even with a new makeover), users want to be able to easily manipulate data structures etc.

I can't believe this functionality is currently missing unless its a deliberate strategy from MS for reasons I don't understand.

[Ed Hansberry]

I was hoping Power Query to data flows was coming. For many months there was a Power Platform menu in Power Query that was greyed out. Now it is gone. Disappeared the same build the Power BI Dataset showed up. 

[doughza]

All super cool, but  MSOLAP to PBI Azure datasets still missing from Excel Mac.   ODC connections are useful only for windows users, Mac users have to fall back to ODBC on premise datasets. IT departments have to maintain legacy connections instead of all cloud PBI.  How long until we see this in excel for mac Brian?

[excelpbi]

@Brian

The From Power BI Option in the Pivot button Disappeared in Ver 2005, Build 12827.20160 Monthly Channel (Targeted) Office 365 Pro Plus - Is this a temporary glitch or are we moving towards E$(5) only !

Cheers 

Sam

[Sergei Baklan]

@excelpbi  Sam, it's here, exactly the same build and channel. The only it is updated - menu is named not as From Power BI dataset but as From Power BI (tenant name). Perhaps some other changes as well, didn't find so far.

[Ed Hansberry]

Does anyone have the Power BI Data Types? I have an Insider build, updated today, and many Monthly Targeted builds updated recently (last week or so) and still no Power BI Data Types, and this is even more critical given the new Featured Tables feature released in Power BI today for the March 2020 build.

[excelpbi]

@Sergei

I checked - its not there - The Pivot Table button is no longer a split button 

I searched in the Command Well as well under From -- not there either

Regards

Sam

[Sergei Baklan]

@excelpbi when most probably that's on Microsoft site, they switched off the functionality for your tenant. Send a frown. What I see

on

[Sergei Baklan]

@Ed Hansberry , so far nothing. I created couple of datasets with featured tables in them, checked that all settings in Power BI Admin center are correct, but nothing appeared in Excel.

[Ed Hansberry]

Thanks @Sergei Baklan 

@Brian Jones (OFFICE) - any ideas when this will roll out to insiders at least? Very frustrating that there are new features in the Power BI service but we've got nothing to actually mess with. 

[Calum_L1]

Why make this dependant on having the E5 SKU, which is mostly about security and telecoms? This feature of Excel should be available if you have a Power BI Pro license!

[Alan Murray]

Absolutely @Calum_L1 

[doughza]

@Brian Jones (OFFICE)  , @Calum_L1  , @Alan Murray   and Mac with 365 business standard and power bi pro.  Power BI has been left behind on Excel desktop for Mac

[Ed Hansberry]

@Calum_L1 this does not require an E5 license. The original article is misleading. It is Power BI Pro or E5. E5 has Power BI Pro included, so if you have E5, you don't need a separate Power BI Pro license, but you do not need E5 for this feature.

[Sergei Baklan]

@Ed Hansberry , yes. On the one hand. On the other nobody articulated exact licensing scheme. Especially if take into account that few functionalities were announced. Access to datasets, custom data types, MIP. They could be licensed differently. IMHO, so far that's only the options. What's for sure Microsoft shall somehow return money invested into development of new functionality, the only question is how. And I hope Excel never will be split at least between E3 and E5.

[Ed Hansberry]

Well @Sergei Baklan it appears this feature may be split. What I'm hearing now is accessing Power BI via the dataset feature works with a Power BI Pro license, however you get it - Power BI Pro, E5, etc.

But getting tables via the Featured Table feature in Power BI and through data types in Excel does require an E5 license, which explains why I see one feature (Power BI Dataset) but not the other.

Not happy about this. 

[t-rev]

@Sergei Baklan MS has provided the following timeline:

We will begin roll-out at the end of June and expect to be complete by the end of August.

[Sergei Baklan]

@Ed Hansberry , people on E5 don't see it as well, it's coming

[Sergei Baklan]

@t-rev , hope on Beta Channel it'll be earlier

[Carlos Otero]

Hi all - a couple of thoughts addressing some of the questions.

The Power BI Data Types feature is not yet available for Insiders.  We are working hard to bring this to Insiders Fast as soon as possible – will post here once available.   The PivotTables features is already available to Insiders as a Preview.

Thank you for all the feedback about licensing. As mentioned previously, the current plan is for both capabilities to only be available for E5 subscriptions when they roll out to production.  E5 includes Power BI Pro - and has a focus on analytics in addition to conferencing and security features.  We are aware of the community feedback on the subject and may evaluate different ways to get the capabilities in the future.

@Ed Hansberry / @Sergei Baklan / @Calum_L1 

[Ed Hansberry]

Thanks for the info @Carlos Otero 

This is a horrible decision for Power BI users that are trying to create a more robust way to share data with peers. There is no financial justification to go from a $12-18/mo Office 365 license + Power BI Pro of $10/mo to a $50/mo E5 just to get access to Power BI datasets. 

I hope MS changes its mind. My team has stopped all work on taking advantage of these features. To us, they are as unobtainable as Paginated Reports and other premium only features.

[excelpbi]

@Ed Hansberry

Don't worry -Remember Powerpivot - The "BI Tool for the masses" was available for every one in 2010 when it was an Addin - Then it became the BI tool for the "Classes" in 2013 - when some genius in M$ started thinking about $ - but eventually was again available across Office Flavours

So as Carols mentioned in his comments "We are aware of the Community Feedback".... it's just that it make take couple of years for the Excel Team to navigate through the Organisational  bureaucracy to get it to the masses.

Read my comment here where I guessed it might be E$ only

https://techcommunity.microsoft.com/t5/resources-and-community/excel-at-mbas-2020-highlights/m-p/137...

Cheers

Sam

[seb_m]

Thank you. This is quite interesting.

I wished however that the Data Type "functions/properties" could take parameters, eg:

- for a Stock, the Price at a specific date: Price(date)

- for a Geography, the population at a specific date: Population("date","2020-03-01"), or by other criterion

- and for PowerBI Datasets, the value filtered by a specific column, eg: ContactTitle("date","2020-03-01") but similar to a lookup, ie the value for smallest date >= 2020-03-01

In my work, many of our business entities change often and while the current values can be helpful, they are too limited to be really useful.

Thank you.

[martymoose]

Will this feature be enabled for Office 365 users connected to a tenant, that only use local instances of PowerBI? We do not have PowerBI enabled on our tenant yet for enterprise use, although some power users can use the PowerBI Desktop and such right now.

Thanks!

[Sergei Baklan]

@martymoose , I don't think so. This functionality is for enterprises only, at least for now. As a minimum it shall be Power BI Pro license and proper configured Power BI workspaces and datasets. And yes, on the top E5 license as it was announced.

[Concensure]

From working on imported data to working on integrated data, Excel has really come a long way in the last five years. 

Another step forward for citenzen tech.

Personally also looking forward to great upcoming functions such as LET.

[Carlos Otero]

Quick update on this topic. We are adjusting the production rollout of the features mentioned in this post (i.e. Power BI PivotTables and Power BI Data Types). The availability will be broadened to include all commercial O365 customers with an Excel license. Since these are Power BI connected features, they will also require a “Power BI Pro” license. I edited the article to remove the specific mention of the E5 requirement. Note that these features are still in the process of being rolled out and might not be immediately available yet. Thank you for your feedback!

[Ed Hansberry]

Thank you @Carlos Otero - this is ABSOLUTELY FANTASTIC NEWS!!!!!!! :thumbs_up: