An Introduction to Power BI

An Introduction to Power BI

Previous Session Summary

In the previous lesson, we looked at some of the basics of functions in M code. We looked at how function names consist of:
a) the subsection of functions to which they belong
b) a full stop
c) a description of what the function does
We reviewed table functions in some detail looking at its parameters and where a table function fits into the structure M code and then looked at some examples of functions in practice.

Lesson Synopsis

We’ve got some good news for you!

Now you’ve got a good basic understanding of PowerQuery in Excel, you’re well on your way to being able to use Power BI!

What is Power BI you say? Power BI is a reporting tool, a piece of software from Microsoft that solves a whole host of inefficiencies in the process of creating a report and sharing it with stakeholders, particularly if you’re currently using a mix of Excel and Powerpoint. 

Interested? We hope so, because we’re going to have a look at what Power BI is in this lesson.

Resource Listing

The Video

Play Video

Lesson Notes

  1. What is Power BI?

As we said in the lesson synopsis, Power BI is a reporting tool, a piece of softward dedicated to cleaning and transforming information from multiple sources, bringing all that information together, permitting custom calculations from data from all sources and then presenting the result in a beautiful, easy to read format. The resulting report can be shared, either through a desktop file or online, greatly speeding up the reporting process (by which we mean the creation of any sort of report, from a standard sales report to an online version of Financial Statements).

There are 3 key advantages to using Power BI in a finance team and these are as follows:

a) Speeding up collation of information

By far and away the biggest time waster of analysts and the reporting process is the collation of information. From PowerQuery to the Data Model, Power BI has so many ways of speeding up the collection of data that result in reports being created in a fraction of the time of previous reporting processes.  Power BI is also designed to be used with other parts of the Power Platform including PowerApps and Power Automate, speeding up the reporting process still further.

b) Ugly reports that are hard to interpret

A huge amount of Finance Teams still report using a mixture of Excel, Powerpoint and Word. Other than making the reporting process long winded and prone to error (because you have to rely on people not systems going through a manual process correctly every time) the results can often be …. underwhelming. They (generally) look clunky and are slow to work.

If you’re using static data tables to drive reports then that slows down reporting even more and even if you’re using pivot tables (which are great and so are pivot charts), they’re not exactly lookers! Additionally not everyone understands PivotTables. Did you know, back around 2015 Microsoft used to estimate that only around 5-10% of its Excel users actually created pivot tables, and we’re not talking fancy pivot tables either. PivotTables also speed things up…but it’s much more difficult to create custom calculations,  and drilling down for information doesn’t lead to a great experience

Power BI makes it very easy to create really beautiful reports that users find EASIER to use and much easier to extract insights. Due to the Power BI engines, Power BI is built to work with millions of rows of data and therefore it’s performance is much better than any large model you build in Excel (unless you’re using PowerPivot)

c) Sharing the information

Email and shared drives are the popular choices but both have significant drawbacks. With email, you’re constantly having to resend reports, and with shared drives, anyone can access any of the information in a file. Additionally there are frequently times, when you want people to only see parts of a report – data relating to their own cost centre only for example. If you’re using Excel or Powerpoint, the only way to restrict access to data in a report is to create a different report for each person and then send it to them. If you know VBA, this can speed things up, but it’s still a slow process. You’re reporting choice in these circumstances then is speedy and insecure, or secure and time consuming.

With Power BI, you create a report, post it online, and then either share it with everyone or restrict the people who can access it, or which parts of it they can access. You can also easily set up automated refreshing so data is refreshed up to 8 times a day.

Admittedly, this is only available if you buy a Power BI Pro license (about £8 per month per user). However if sharing the information isn’t a problem and you don’t mind clicking a refresh button when you open a file, then the software to create a PBI model is …… FREE.

This means that you can still take advantage of creating beautiful reports quickly and easily and simply pop them on a shared drive or SharePoint, like you used to with an Excel file.

What works for you?

In my personal experience whether as an auditor, an analyst or a finance manager, I used to work in Excel for probably 95% of my working life. Now I work in Power BI for about 95% of my working life. It has completely changed how I work, vastly for the better, cutting out the boring bits like collation of data and swapping it for the fun parts like finding insights. I’ve used Power BI for more or less every part of my job (apart from technical accounting papers!) but here are a few examples:

  1. Cost centre reporting vs budget/forecasts
  2. Payroll reconciliations
  3. Creating lengthy repetitive journals
  4. Intercompany matrix creations
  5. Cost analysis
  6. Parts of Financial Statement preparation
  7. Invoice validation
  8. Invoice Creation
To that end, if you are using Excel and Powerpoint non stop in your everyday working life particularly to create reports for sharing, I would thoroughly recommend Power BI.
That said, if all you want is something that helps you create reports more quickly or do repetitive tasks such as journal creation more quickly, but you aren’t worried about making reports pretty or sharing them with security, Power BI might not be the route for you.
PowerQuery and Powerpivot in Excel are very similar (in most cases identical!) to PowerQuery and the Data Model you find in Power BI so you’ll still save tons of time just sticking with these options in Excel.

.... and a little more!

The 3 stages of Power BI – In the accompanying video to this lesson we talk about the three stages of Power BI being :

– Collation of data (PowerQuery)
– Data Modelling (Powerpivot – where data from different sources is related together and calculations are performed)
– Creation of Visuals

We then talk about a 4th stage being the sharing of created reports.

You might think from this that you have to learn the Powerpivot stage to be able to use Power BI, and yes, you will get the most out of Power BI if you understand how to use the Data Model effectively. However, the Data Modelling phase is essentially one big pivot table, so if you understand pivot tables, that’s enough to get started in the data model phase.