X

Combining
Excel and Power BI
for smart reporting

How Excel users rapidly implement modern dashboards with Power BI

Broad data analytics
and visualization capabilities

Microsoft Power BI first appeared under the name ‘Project Crescent’ in July 2011 in a bundle with Denali Microsoft SQL Server. In September 2013, it was then introduced in Office365 as Power BI, to become a standalone product in July 2015.

Official Microsoft documentation refers to Power BI as a “unified, scalable platform for self-service and enterprise business intelligence (BI) that’s easy to use and helps you gain deeper data insight.” [2]. Indeed, Power BI is a multi-purpose software able to pull data from different sources and aims to help non-technical users while approaching various aspects of the data analytics process.

As anticipated in our previous article of this series, Power BI’s central functionality is Data Visualization. Exploring and analysing complex data can be handled easily through click-and-drag actions, choosing from a rich library of visuals. Additional visuals are available in the in-app Microsoft Marketplace.

The man with runner on the street be running for exercise.
DATA STRATEGY QUICK CHECK

Check your data maturity with our free tool developed by leading business analysts and get actionable insights.

Data visualisation aside, Power BI offers some remarkable features in comparison with other BI tools:

  • Exploit Machine Learning algorithms in a code-free fashion through the integration with Azure Machine Learning. Image recognition and text analytics facilities are now built-in tools in Power BI.
  • Subset the data and automatically gain simple insights through the Quick Insight
  • Connect to several different data sources from Microsoft, Salesforce, and other vendors (Hybrid deployment support). Data can be ingested and transformed via Power Query to be used across multiple dashboards.
  • Perform Data Modelling directly in a built-in facility able to render standardised results. Furthermore, a Modelling View allows users to better interact with the data models through a graphical interface.
  • Exploit sample code and APIs to embed dashboards inside other applications.

Web, Mobile or Desktop App:
Which is right for your project?

Power BI functionalities are distributed across the 3 Power BI products available:

  • Power BI Desktop
  • Power BI Web-app
  • Power BI Mobile

 

Despite sharing some basic features, each of these products is explicitly meant for specific users.

Power BI Desktop is designed as the tool through which it is possible to carry out data modelling, analysis, and visualization most effectively. Although web and mobile apps offer some editing capabilities, the desktop version provides a complete user experience. Power BI Desktop gives far more control over visualization than the Power BI Web app and Power BI Mobile. For the very same reason, Power BI Desktop is the only instrument capable of performing some meaningful data analysis. It allows, for instance, to define of new measures and fully supports DAX, the native formula and query language. On the other hand, the desktop app is designed as a relatively static instrument, making it less effective when sharing the analysis results.

Power BI Web app draws on shareability. It allows sharing of both data and visuals. Put differently, through the web app, it is possible to build interactive reports and dashboards. The dashboard object in Power BI is highly effective. A dashboard is simply a pinned report: objects in a report are fixed in the canvas so the viewer can interact with them by only selecting the appropriate filters and drill-downs, navigating through an already optimized visualization.
Power BI Mobile app brings shareability to the next level. It is optimized for mobile so that reports and dashboards can always be at hand.

Combining Excel and Power BI
to boost productivity

Quoting ex-Power BI Solutions Marketing Manager Nic Smith “BI is about providing the right data at the right time to the right people so they can take the right decisions”. This statement synthetically embeds the three critical aspects of modern Business Intelligence and analytics:

  • Ability to handle a large amount of data.
  • Allow for live data analysis.
  • Possibility to continuously communicate results to all the relevant stakeholders.

Combined with the fact that data-driven decision-making is increasingly needed no matter the size of the business, such key factors are driving the upward trend in the implementation of self-service analytics solutions, spotted, among others, by [3], [4], and [5].

Forrester Consulting reported in 2011 that 88% of non-technical BI users rely heavily to exclusively on spreadsheets for analysis and reporting [7]. Certainly, things have changed since then, but considering the spread of Excel and Google Sheets combined, it is hard to believe that spreadsheets are out of the game anytime soon. The reason for such resilience needs to be searched in the large degree of flexibility that spreadsheets allow, both in terms of analysis and data visualisation. Such tools enable non-technical users to perform relatively complex calculations and plot graphs, and design visuals providing valuable insights. Indeed, Excel is still considered the most used software for self-service ad-hoc analysis.

However, modern business intelligence and analytics solutions are not intended to substitute spreadsheets. Instead, they emerge as complementary tools to enrich the user experience. That is, while spreadsheets are still one of the best tools to perform calculations and analyse ad hoc instances, BI tools simplify the visualisation, collaboration, and spreading of information across hybrid workplaces. For example, in Six ways Excel users save time with Power BI, Microsoft points out how Excel users can benefit from Power BI to save time with mobile-ready reporting capabilities.

With the large offer of premium self-service analytics and BI solutions, which often come with a free community version, the slow adoption of modern tools in small and medium-sized enterprises often depends on scarce resources, mainly a time for learning something new. Put differently; it is more effective to exploit the “good old” Excel to get things done quickly and confidently than to explore any alternative.

Given this evidence, Power BI may have a competitive advantage over other tools. It shares several functionalities with Excel, dramatically improving the learning time. Users can exploit powerful capabilities using the skills they already have.

88% of non-technical BI users rely heavily to exclusively on spreadsheets for analysis and reporting

Conclusion

In the following article of this series, a practical use case will be presented showing how to create a financial dashboard combining data coming from different sources, including Excel, and of course Power BI.

References

[1] J. Scardina und L. Horwitz, “Microsoft Power BI“, 2018
[2] Microsoft Inc., “What is Power BI?“, 2022.
[3] Fortune Business Insights, “Business Intelligence (BI) Market Size, Share & COVID-19 Impact Analysis“, 2022, Report ID: FBI103742
[4] Analytics Insights – Market Trends, “Top business intelligence trends and predictions for 2022“, 2021
[5] B. Calzon, “Top 10 Analytics And Business Intelligence Trends For 2022“, 2021
[6] Forrester Consulting, “Self-Service: An Essential Capability Of BI“, 2011
[7] DataPine, “Self-Service BI Tools – How to take advantage of modern self-service BI?“, 2020

HOW WE CAN HELP YOU FURTHER

Related Services

The data services we offer are comprehensive in nature and cover a wide scope, however, as an elementary introduction, you can rely on us to provide the following solutions.

Data Analysis

We organize and examine data to create actionable intelligence.

Dashboard Design

The creation of visualization tools that allow data to be assessed efficiently and effectively.

Data Governance

We implement architectures and procedures that manage the full data lifecycle needs of a business.

Data Architecture

The integration of technologies to provide optimal end-to-end data management.

Calculation Engines for Finance

Whether for internal or regulatory purposes, we create the data gathering, calculation, and reporting processes.

About the Authors

Francesco Di Cugno, Managing Director, Convolut GmbH
Francesco di Cugno
Senior Technology Leader & Founder of Convolut
Francesco is a customer-centric and versatile Senior Technology Director/Consultant who has built a reputation for moulding intricate business solutions using the latest technological innovations, and assists global organisations, including Fortune 500 companies, to realise strategic vision, goals and objectives.
Lorenzo Zannin
Lorenzo Zannin
Data Analyst
Certified business and functional analyst with extensive financial data analysis and automatic reconciliation knowledge.