Excel or Power BI. Which is better for business reporting?

(In conversation with Jens, one of our Excel/Power BI trainers)

In this post we sit down with one of our training delivery team, Jens, to look at the pros and cons of Excel and Power BI when considering which reporting path is best for you and your organisation.

power_bi_excel

Jens, you mentioned a frequently occurring question you get in training delivery is – “I need to analyse huge datasets and create reports. Which Microsoft tool is the best? Should I use Excel or Power BI?”- How do you typically respond to this?

I always answer the question the same way. “I do not know because every organisation is different but, in my opinion, Power BI is a far more efficient tool for reporting”.

To answer a question like this, I will need to analyse the client’s business and needs in depth, but I am always happy to help my clients make their own decision. The consequence of a wrong decision can mean days of wasted work and even worse, if the output is inaccurate, very costly decisions can be the result.

That’s very helpful to bear in mind. So if you were to offer some help, what pointers would you share?

Here is a list of my thoughts which I hope can help them make the right decisions and get started in the most productive way.

    1. Take the time to do a good plan.
    2. Make sure that you know in depth what you need in the output from the data. If you need a very simple output, Excel may be enough.
    3. Collaborate with colleagues who may need to create reports from the same data set. The options to share data models and collaborate with colleagues are far more superior in Power BI.
    4. Make sure that you know the “audience’s” expectations and needs. Again, the options are limited in Excel and the needs can change over time so Excel’s limitations could be costly as the report would not be able to display the required information.
    5. How is your accessibility to the source data? Excel and Power BI have almost the same options to connect to data sources but in Power BI, Python can be used for more advanced options.
    6. How can the report be created that is flexible so future changes can be made easily and efficiently? If the reports and data model are structured the right way, changes can be made more easily in Power BI.
    7. Appropriate reports must reflect key business assumptions clearly and accurately without being over-built or filled with pointless details. Whether Excel or Power BI is the chosen application, the danger is that the report can mislead its audience who may make wrong decisions as a result. Microsoft had this in mind when they developed Power BI by developing clear and easily readable visuals, but it is still the report designer’s responsibility to achieve clarity.
    8. Confidence in a financial report’s integrity can only be achieved with a clear logical structure and layout. It may be easier to create confidence in an Excel report if the organisation is used to using Excel and is confident with its tools.
    9. How would you and your audience like the report to be presented? Power BI is light years ahead of Excel when it comes to presenting options.
    10. When and how often should the report be updated? Power BI has features to allow for full control of automated refreshing.

I have in the listed similarities between using Power BI versus Excel.

similarities_BI_Excel

The real difference between using Excel and Power BI to create reports or dashboards from huge multiple data sources is about publishing, visualisation, automation options and how the two tools co-operate with other Microsoft tools. Well, it is also a question about Microsoft’s visions and priorities for the two tools.

Visuals_go_BI

Thanks Jens that’s really helpful, so in essence you are saying that whilst the data manipulation capabilities of Excel and Power BI aren’t radically different, when it comes to the creation of reports, that’s where things start to take off with Power BI?

Yes that’s it, and I will now focus on the differences.

Excel:

Visualisation:

The data model can only be visualised in Pivot Tables and Pivot Charts. Information from the data model can be shown on worksheets by using Cube functions.

Publishing:

Like other Excel files, you can share a workbook, but you have no more high-level options.

Automation options:

Compared to creating the data model in Power BI, there are fewer possibilities in Excel to automate tasks and create your own tools.

Co-operate with other Microsoft tools:

If the data model is created in Excel, the data model can only be used in the file containing the data model. An Excel data model can be imported to Power BI but the data cannot be refreshed.

Microsoft has developed many new tools on the 365 platform which co-operate with Power BI data models and not Excel.

Microsoft’s visions and priorities:

We get a completely new version of Power BI every month, whereas Microsoft has not updated Power Pivot (the data modelling tool in Excel) for years. Everything points in one direction: Power BI is the future.

Wow! That’s certainly impressive and I’ve heard that Power BI users can even suggest and vote for new features?

Yes that’s correct, the investment Microsoft have put into power BI over the last few years is really incredible! Now I want to continue and share some of the differences you find in Power BI.

Power BI

Visualisation:

If you like Pivot Tables in Excel, the matrix tool in Power BI can do the same. The options to visualise your data in Power BI are outstanding. You have several options to add interactivity to your reports. The design options are far better than the those in Excel. You have artificial intelligence in Power BI which can cross analyse the data set and visualise key indicators. It is like comparing a stone against mount Everest when comparing visualisation options in Excel against Power BI!

More advanced and sophisticated filter options than you will find in Excel.

By downloading an Excel Power BI add-in, charts, tables, and everything else created in Excel can be visualised on a Power BI dashboard and published.

Publishing:

You can publish your Power BI report to someone inside your organisation and outside. You invite them through their email address. The invited audience can then see the online report live in real time. Power BI reports can be watched on mobile devices. From published reports, dashboards can be created. Dashboards are tailored from one or many reports. This is ideal if someone needs information from many reports or only needs key information from one report. When a report is published you can give colleagues access to create reports from the same data model.

Restrictions can be made in Power BI (called row level security). You can tell Power BI that when you invite a specific person to view your report, you only want this person to be able to see information about the London sales team and another person only to be able to see information about the Manchester sales team, for example.

If you have 25 sales teams, create the data model in Excel and you want the same restrictions (ie. you only want the sales teams to be able to see their own data), you will need to create 25 Excel files, 25 data models, and in all 25 models you will need to remove the data from the 24 sales teams who you don’t want to give access to!

Just in the above example, sharing the report could easily be the difference of minutes in Power BI vs hours in Excel. Imagine the boost in productivity by choosing the right tool!

Again, it is like comparing a stone against Mount Everest.

Automation options:

Python and R Scripts can be used for more advanced automation options and for creating your own visualisation tools. Python can also be used for more advanced connectivity options.

Co-operate with other Microsoft tools:

Microsoft has developed many new tools on the , which co-operate with Power BI and not Excel.

An example is the Form tool in 365. You can create a form and when someone fills it in, the information goes straight to the data model and your Power BI report.

Microsoft’s visions and priorities:

The effort and time Microsoft puts into Power BI can, in my eyes, only mean one thing. This is the future and here Microsoft wants us to create our reports.

Pros and cons – Excel versus Power BI as a reporting tool.

excel vs powerbi
Excel
Pros
    • Most organisations already have a lot of Excel knowledge.
    • No extra costs or training may be needed.
Cons
    • Very limited options for visualisation.
    • No options for publishing.
    • Data model cannot be shared.
    • Very few Excel users know about data modelling in Excel and training may be needed.
    • It doesn’t seem like Microsoft spends much energy to develop the options in Excel.

Power BI

Pros
    • Endless number of visualisation options.
    • More advanced and sophisticated filter options than you will find in Excel.
    • Publishing online.
    • Better control of who can see the reports.
    • Reports and dashboard can be viewed live on mobile phones and tablets.
    • Artificial Intelligence used to provide insights into datasets.
    • One data model can be used for many reports.
    • One data model can be shared within the organisation so many users can create reports from the same model.
    • The data model is protected so no one can change it by mistake.
    • Data access restrictions can control how much information each user can see.
    • Many options for interactivity between different visualisations.
    • Tailored dashboards can be created from one or many reports.
    • Excel items can be used on dashboards.
    • Python and R Scripts can be used.
Cons
    • There are costs to be consider if you need Pro License or Premium License options.
    • You will need a 365 platform to get all the benefits from Power BI.

Jens, this has been a really comprehensive step through the data reporting capabilities of both Excel and Power BI, so thank you! In summary then and with your experience where do you see things heading?

I have taught Excel for more than 20 years, data modelling for about 10 years and Power BI for the last 5 years and in my opinion Power BI has revolutionised reporting.

All reports should be and can be created fully automated whether you choose to do it in Excel or Power BI.

Many of my clients spend days every month creating reports in Excel. However, they may not know the options to automate the process, saving them possibly months in a year!

Should we create our reports in Excel or Power BI?

If your organisation wonders if it is worth investing time and money going from Excel reporting to Power BI, my short answer is – ‘yes’!

The Magic of Power Query

One word That describes Power Query in Excel – MAGIC! 

 Power Query will completely change the way you work in Excel! For most Excel users, Power Query could be the tool that reduces time spent in Excel dramatically. Power Query could also minimise the use of VBA coding in Excel. Power Query can be seen as a machine. Once the “machine” is built, it will continuously repeat the tasks for new data added to Excel. It is a much easier alternative for automating processes than VBA programming because Power Query does not necessitate coding. In this article, we will explore the magic of Power Query.

The Magic of Power Query

key features of Power Query

You can: 

  • Connect live to an external data source
  • Structure internal and external data
  • Clean, merge, append, and group internal and external data
  • Automate tasks
  • Transfer data from the query connection to the Excel data model
Connecting live to an external data source 

The tools to connect to external data are found on the Data tab in Excel, in the Get & Transform Data group. You can connect to any live data if you have legal access to it. You may need to get help from your IT department to connect to your source, depending on what your source is. When you have created the connection, you can create your reports, analyses, or dashboards and they will auto-update when new data is added to the source. 

You can also connect to folders, which can be extremely useful if you receive your data from clients. If you connect Power Query to a folder, drop any new data into that folder and your report, dashboard, or analysis will update, including the new data. 

Structure internal and external data 

Many Excel users struggle with source data that is structured incorrectly. This makes working in Excel much more time-consuming. Excel prefers lists, and all source data should be in a well-structured list. Power Query can restructure your data sets. When you have set up the query, the “machine” will restructure any newly added data in future.

 The Power Query Editor

Clean, merge, append, and group internal and external data 

If your data contains US dates, extra spaces, unprintable characters, incorrect spellings, empty rows or columns, incorrect formats, or hundreds of other issues, Power Query is the right tool for you. 

Power Query can also merge any number of tables. Say goodbye to complicated and memory-heavy lookup and reference functions. 

Connect to many lists and turn them all into one list by using the Append option in Power Query. 

The Group Data tool in Power Query can replace the use of Pivot Tables and the Subtotal tool, but is also particularly useful as a part of the Merge Data tool which generates related keys between the tables. 

All tools in Power Query are available both for external and internal data. 

Automate tasks 

Power Query works in much the same way as the macro recorder in Excel. The query records the steps you are doing in your data sets and writes code in a language called Power Query M.

Code example: 

One of the differences between a macro and steps in Power Query is how the code gets executed. Recorded macros will only run with user input, so you need to execute the code manually. Power Query M steps will automatically execute every time there is a task to do. 

Transfer data from the query connection to the Excel data model 

Excel has a limit of 1,048,576 rows. However, the number of rows you can add to the memory of the Data Model is almost limitless. Few Excel users are aware of a data model in Excel called Power Pivot. Not only can it store billions of records, but the compression technology Microsoft has developed for this tool is outstanding.

If you are working with huge data sets, the combination of Power Query and Power Pivot is the ultimate winner. You can connect to huge data sets. Then you can clean, structure, merge and append your data in Power Query. Afterwards, you can transfer the data to the Excel data model and relate the data sets. 

Power Query – THe Pros and Cons 

To be honest, there are really only pros. 

Any Excel user above a basic level should explore this tool; it will change how you use Excel. For most users, it will also have a significant impact on the time spent in Excel. Such is the magic of Power Query.

Pros: 

  • Positively impacts the efficiency of Excel tasks 
  • Removes boring time-consuming repetitive tasks 
  • Automates tasks without complicated VBA codes 
  • Makes other tasks simpler because data quality and structure will be improved 

Cons: 

N/A