Microsoft enhances integration between Power BI and Excel

Possible cause for concern

Spreadsheets remain the most popular platform for analytics despite the development of more advanced business intelligence software tools. The format is so familiar, in fact, that some startup analytics vendors -- Grid, Kloud.io and Sigma Computing among them -- have developed analytics platforms that employ spreadsheet interfaces rather than dashboards.

Microsoft, meanwhile, continues to invest in Excel despite developing Power BI and now Azure Synapse Analytics, and first introduced what it calls data types in 2018 when it enabled users to access stock market and geographical data in single Excel cells through live, connected sets of information.

Since then, however, Excel users have asked to be able to work with their own business data in addition to stock and geographical data types, according to the blog post. In response, Microsoft developed the new integration between Excel and Power BI.

The move is an incremental advance for Excel, according to Donald Farmer, principal of TreeHive Strategy, who before becoming a consultant led design and innovation teams at Microsoft that worked on Power BI.

"Excel has always been the primary data source for Power BI," he said. "They've always been closely integrated in some ways, but it's mostly been one way with Excel as a source for Power BI, so now Power BI integrates back into Excel and Excel users can leverage Power BI. It's not a very surprising move, and it's another incremental integration."

He added, however, that despite the benefits of added access to data for Excel users, the update could lead to data governance problems.

Because of its ease of use, Farmer said, users sometimes choose Excel when more secure -- but more complex -- platforms might be more appropriate. The new integration between Power BI and Excel, he continued, appears to be more apt for a database or business intelligence application rather than a spreadsheet.

"I understand why they're [adding data types], but there is a filing cabinet full of examples where the use of Excel goes wrong, and it's nearly always because people use Excel because it's easy, because it's free-form, and they wind up using it in contexts which are inappropriate and for processes which require a better-governed application," Farmer said.

Continuing to enable people who are comfortable and efficient in Excel by providing them access to more complex data types in a tool they know and love is critical to the democratization of analytics within organizations.

"That blog post shows that Microsoft still hasn't taken the question of governance and compliance for Excel users seriously," he added.

In response to Farmer's concerns, Brian Jones, head of product for Microsoft Excel, provided a statement.

"Data governance is at the core of the data types integration with Power BI," he wrote in an email. "Data types remain connected to the underlying source in Power BI, allowing you to pull data from Power BI without needing to flatten it or lose all the information about the source of truth for that data.

"Power BI provides some of the most advanced governance controls over datasets, including permissions-based access, row level security, full application life cycle management, the ability for any user to apply Microsoft Information Protection labels and for IT to certify," the statement said.

Although Farmer expressed concern about data governance problems resulting from the new integration, Mike Leone, senior analyst at Enterprise Strategy Group, said that the integration has the potential to better democratize data in organizations by enabling users most comfortable working in Excel to work with more complex data.

"While Microsoft continues to add self-service features to Power BI, Excel is still the de facto standard for so many people," he said. "Continuing to enable people who are comfortable and efficient in Excel by providing them access to more complex data types in a tool they know and love is critical to the democratization of analytics within organizations."

Beyond Power BI and Excel

While users of both Power BI and Excel will now be able to take advantage of the integration to create new data types, the update also gives Excel users who aren't also Power BI customers an option to create data types. It's estimated that there are more than 750 million Excel users, while Power BI had about 5 million subscribers as of 2016.

Using the Power Query technology in Excel, users can manually connect to data sources, import that data and specify that they want it structured as a data type.

And beyond enabling Excel users -- both those who are also Power BI customers and those who aren't -- to create their own data types, the partnership with Wolfram Alpha will provide access to more than 100 structured data types that will pull information from Wolfram Alpha's online service into Excel.

Wolfram Alpha, a subsidiary of Wolfram Research, is an answer engine of which customers can ask factual questions, and rather than get a list of documents or web pages that might contain the answer -- as in a search engine -- get direct answers based on curated data.

Using Wolfram Alpha's curated data, Excel users will be able to access data types that will enable them to do such things as track their exercise, monitor the nutritional information in the food they consume, research potential universities, and even learn about the arts and sciences.

Those data types, Leone said, are appropriate for Excel.

"For basic querying and analysis of smaller, structured data sets with common data types of text or numbers, Power BI is overkill and Excel is more than enough to get the job done," Leone said. "When talking scale, complex data types, complex queries and interactive dashboards, you'll likely hit the limits of Excel."

Farmer, meanwhile, again expressed caution.

"[The update] does enable a lot more power for the Excel user," he said. "[But] the kind of people who can take advantage of these features could probably do this kind of work more effectively in Power BI. Excel is a great tool, but I don't think it's necessarily the appropriate tool for some of these use cases."

Rather than adding more data types and adding the new integration between Power BI and Excel, Farmer said he'd instead like to see Microsoft integrate more data governance features in Excel.

Microsoft, meanwhile, stated that Power BI provides advanced governance controls over datasets, and that when working with those datasets Excel users will benefit from those Power BI governance capabilities.

In addition to more governance tools, Farmer said future Excel updates could enable data modeling capabilities in the same vein as Grid, and low-code application development capabilities such as those Google added with its acquisition of AppSheet.