Power BI for the Excel Analyst: Your Essential Guide to Power BI

Chapter 1 - Getting Started with Power BI

Power BI allows you to create and share robust, interactive, refreshable reports in a secure environment. It is a platform consisting of several elements that allow report creators to provide information that is easily accessible and easily refreshable.

Power BI was officially launched in July 2015 and is rapidly becoming commonplace in workplaces around the world. You may well have seen it in action already.

The aim of this book will be to teach you how to build a simple interactive report like the image below and for you to understand how to refresh it and share it with others.

Importantly I’ll be sharing my best practice tips and advice to give you a solid foundation in building and sharing reports the right way.

Power BI has brought about a complete change from the old days of Business Intelligence projects. Those solutions required dealing with software salespeople followed by weeks of requirements gathering by business analysts. The requirements were then sent to distant developers creating what they think you said you need, rounded off with out of scope re-work. Ultimately the project took 6-12 months, and it was then out of date or not exactly what the business needed.

Power BI enables business users, especially those currently living in Excel, to build these fully functional business intelligence solutions themselves in a few weeks. This is not hyperbole; it is a proven fact. The ability for someone who already knows the business to quickly build something useful is what Excel analysts have always done. The difference with Power BI is there’s now an entire ecosystem designed to make these reports more robust and easier to scale out and re-use.

Power BI consists of 2 main elements:

1. Power BI Desktop is where you build your reports

2. PowerBI.com (otherwise known as the service) is where you share your reports

Power BI desktop consists of 3 core areas of functionality.

Power Query is the World’s greatest washing machine for dirty data. It allows you to connect to different sources of information and re-organise it to make your report creation easier. If you’ve ever copied and pasted multiple blocks of data, filtered a column, used Text to Columns, MID, RIGHT, LEFT, CONCATENATE, etc. then Power Query will blow your mind!

The Data Model is the world’s greatest data wardrobe. Hang up all your nice clean data (from Power Query) and then organise the related items so that you can easily press a button to see blue clothes or shirts or evening wear. An Excel sheet has about 1 million rows, whereas the Power BI Data Model can technically hold unlimited rows and 1,999,999,997 distinct values per column. Yep, that does say 2 billion!

The Canvas is where you add your visuals using the data from your Data Model. Charts, Tables, Matrix visuals, Slicers, buttons, text boxes all contribute to communicating useful information to the report consumer to assist them in making an informed decision.

This is also where you start to write formulas to enrich your report with ratios, variances, cumulative totals, etc. This formula language is called DAX (Data Analysis eXpressions) and takes your reporting to the next level.

PowerBI.com, also known as the service, is where you save your reports to share with others. The report consumers can log in to PowerBI.com to view the reports that have been shared with them. Alternatively, the reports that have been published to PowerBI.com can be embedded into Teams, SharePoint, and even websites. There is also the ability to share a report with the entire world for free via a Publish to Web option. The Data Model you build for your report can also then be connected to via other Power BI reports and Excel to create a suite of reports from this single source of truth.

Getting Set Up

Before you start with Power BI you will need to have Power BI Desktop installed on your machine (Windows machines only, no Mac sorry). Your IT department may be in control of this process and have different methods to the ones I’m about to suggest.

The recommended approach is to install the version from the Microsoft Store as this automatically stays up to date. Make sure it is Power BI Desktop that you download, as there’s also a view only application called Power BI. Here’s the link to the correct version https://aka.ms/pbidesktopstore

Occasionally this option may be blocked for you, so then you will need to go to https://powerbi.microsoft.com/en-us/desktop/ or use this shorter link url.pbi.guide/Man64 where you will see something like the screenshot below. I’m sure these screens will change by the time you’re reading this book, but hopefully, you’ll get the idea.

You’ll ignore the Download free button (1) as that simply takes you to the Microsoft Store version again. Click See download or language options (2). You should pick the 64-bit version. With this method, you will then be prompted each month to download the new updated version.

💡 The 64-bit version can utilise more RAM from your machine and is, therefore, a more powerful and less crash-prone option than the alternative 32-bit version. The more RAM you have on your machine the better when it comes to Power BI. It will improve your report development experience as tasks will run quicker. In my view, 16GB RAM is the minimum that you need.

Using this Book and Downloading Sample Files

For the best result, you should follow along with the Power BI software open in front of you, clicking the clicks, and physically replicating the exercises. There is no substitute for hands-on practice to help you remember what you need to do. However, I also wanted people to be able to read this book and follow along even without a copy of Power BI desktop to play with. Hopefully, I’ve achieved that.

I’ve added a lot of screenshots marked up with icons that I will reference in the paragraphs before the image such as the black numbered icons like (1) which generally indicate something you should (left) click on. Yellow icons like (2) which mean look but no need to click, and then green icons next to a mouse signifying a right-click (3). There’s also the occasional use of orange rectangles and arrows just to highlight elements.

You will notice a few icons have been used in comment boxes

☕ - coffee chat moments, consisting of my thoughts and general comments

💡 - insights and tips that relate to the topic

⚠ - warnings and other very important things to be aware of

Download the Exercises and view the List of URLs

I have created a companion website for this book called PBI.guide, where you can download all the exercies and files used in this book (see 2 in the screenshot below) along with example Power BI files at various stages of completion as the book progresses. For a direct link to the downloads page use this shortened URL url.pbi.guide/PBIXL. After you download the zipped folder, you will need to extract the files or open it and copy and paste the exercises folder into a different location. That page also includes all the links referenced in this book, so if you’re reading this in hard copy go to that page and simply click the links there.

If you are going to use the solution files I’ve created, then I recommend that you open the folder called Solution Files and read the PDF Changing connections on the solution files to point to your system.

The PBI.guide Website

PBI.guide is a useful resource for continuing your Power BI learning journey beyond the end of this book. I will be adding content, articles, and flagging updates to keep you up to speed with the rapidly evolving world of Power BI. Bookmark the site or add it to your favourites bar for ongoing future reference.

Chapter 2 - First Look – an Introduction to Power BI Desktop

Once you have downloaded the folder as mentioned on the previous page, go into the exercises folder, and double-click on the file called First Look Demonstration. This should launch Power BI desktop. For those of you not sitting in front of your laptop don’t worry we’ll have screenshots for it all.

💡 You can also look at the online version via url.pbi.guide/FirstLook, noting that you don’t need to hold Ctrl when clicking buttons with the Web Version

The cover page should appear.

Interacting with a Power BI Report

Hold the Ctrl key and click on the button labelled First Look (1)

These first few pages are simply a showcase of a few of Power BI’s report features. The detailed instructions will come in later chapters, for now just follow along and see what’s possible.

Hold Ctrl and click on the Show Tips button in the top right corner (1) and multiple text boxes will appear with tips about how you can interact with the report

💡 You quickly discover that when building reports in Power BI desktop you must hold the Ctrl key when clicking buttons to trigger their action. The report consumer will not need to hold Ctrl once you share this report with them via Power BI.com. Clicking on bars or values in visuals will magically filter the other visuals on the page. This does not require you to hold Ctrl.

Left-click on any bar in any visual. This causes all the other visuals to be filtered by that selection. This means that consumers of your report can explore the information you provide and potentially answer their own specific questions without having to come back to you and ask for a slightly different view of the data. This is fantastic!

Click on the same bar a second time to remove that filter and return the report to its original state.

From this point on, unless the book states to use the right-click option then assume click means left-click

Right-click on the bar for the brand Reboot (1) and Drill down (2) to see the products

Right-click on one of the product bars (1) and select drill up (2) to return to the original display

Hover your mouse over the bar for Brisbane (1). A tooltip will pop up giving more information

Click on the Reset button in the top right corner to remove any filters you may have applied. Remember that you need to hold the Ctrl key when you click the button

Right-click on the Neptunes Restaurant February value of 20 (1) and then hover over the Drill through option and click the Sales Detail label (2)

You will jump to a page showing detailed transactions for that customer. Ctrl-click the Back Button (3) to return to the main page

Click on the Analysis Page. This page contains a visual called the Decomposition Tree which allows you to explore the factors influencing Actual Sales $

Click on the different branches to explore the data, and hovering will also display the tooltip!

You will explore and understand these features along with many other elements as you progress through the book.

The Four Screens of Power BI Desktop

The Report Canvas (1). It’s the one you’re already seeing and is where you add your charts and other visual elements that the report consumer will see and interact with.

The Data View (2) where you can see the underlying data.

The Model View (3) where you link tables to each other.

Power Query (4) where you connect to and clean up/reorganise your data into well-structured tables. This is where your Power BI journey will start in the next chapter.

To wrap up this first look chapter take a quick look at screens 2 and 3.

Click the data view icon (see 1 in the image below). This screen is never visible to the report consumer. It is an interface where you, as the report builder, can view and filter and sort the data without impacting the report in any way. There is the ability to add extra calculated columns but more on that later

Clicking the name of a Table of data on the right (2) changes which data is being displayed

☕ Occasionally I’ve seen those learning Power BI mix up this screen and the Power Query screen. You’ll see next that they do look similar at first glance, but they are very different. The important thing to note for now is that filtering your data here has no impact on your report.

Click the Model View icon (see 1 in the image below)

This is where much of the real power of Power BI lies. It is here that you relate tables to each other so that data can be sliced and diced a hundred different ways without having to write thousands of different formulas. It’s in this screen that a single line between 2 tables can replace millions of lookup type formulas. Whether you use VLOOOKUP, INDEX MATCH or XLOOKUP think about the power that replacing millions of formulas with a few lines can give you.

That’s the brief introduction and first look at Power BI desktop. I hope that you’re interested in learning more about these features and building some beautiful, useful reports that are simple to update.

Close this demonstration file, there’s no need to save any changes

You will now build a report from scratch starting with what I consider to be the greatest ever