Power BI for the Excel Analyst E-Book

MrExcel's Holy Macro! Books

• Herausgeber: Packt Publishing
• Kategorie: Fachliteratur
• Sprache: Englisch

Seitenzahl: 229

Veröffentlichungsjahr: 2024

Beschreibung

Transform your Excel expertise into advanced Power BI skills with this targeted guide designed for analysts. You'll begin by setting up Power BI and learning how to integrate it with your existing Excel knowledge. The book will guide you through essential concepts like data cleaning, transformation, and visualization using Power Query. As you progress, you’ll dive into advanced data modeling and learn how to write powerful DAX measures to enhance your analysis.
This guide also covers the crucial steps of publishing and sharing your reports through PowerBI.com. You'll explore cloud-based data management, create Workspaces for collaboration, and learn about different licensing options. Additionally, advanced features like Row-Level Security and Dataflows are discussed to help you handle complex data scenarios.
By the end of this guide, you'll have the ability to build professional-grade reports, perform sophisticated data analysis, and confidently present your insights. Whether you're new to Power BI or looking to enhance your skills, this guide provides a clear and structured path to mastering the platform.

Leseprobe

Power BI for the Excel Analyst

© 2022 Tickling Keys, Inc. and Exponential BI

All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information or storage retrieval sys­tem without permission from the publisher. Every effort has been made to make this book as complete and accurate as possible, but no warranty or fitness is implied. The information is provided on an “as is” basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with re­spect to any loss or damages arising from the information contained in this book.

Author: Wyn Hopkins

Layout: Bronkella Publishing

Copyediting: Deanna Puls

Tech Edit: Ken Puls

Proofreader: Bill Jelen

Cover Design: Shannon Travise

Indexing: Nellie Jay

Published by: Holy Macro! Books, PO Box 541731, Merritt Island FL 32953, USA

Distributed by: Independent Publishers Group, Chicago, IL

Printed by Sheridan South, Brimfield Ohio

First Printing: August 2022 E-Book version 20220719c ePub version 20221030c

ISBN: 978-1-61547-076-1 Print, 978-1-61547-164-5 e-Book

Library of Congress Control Number: 2022934210

Foreword

I’m assuming that due to the title of this book you might be a bit like me, you’re that person from department XYZ who’s good with Excel and interested in learning Power BI. Welcome to the book.

I’m a massive fan of Power BI and Excel and I’ve been building solutions for clients using both products for many years. I’ve also trained a few thousand people in Excel and Power BI so I know the common hurdles and challenges that people face.

My first taste of data was as a fresh-faced intern with Hewlett Packard back in 1995. I was quickly hooked on Lotus123, one of the earliest spreadsheet packages, and within 8 months I had automated away most of my month-end tasks. I clearly had a knack for this stuff. Over the following years, I moved through my career learning more tricks and techniques from colleagues and the occasional training course. I always enjoyed the data part of my job. I liked the puzzles work presented and the workarounds and hacks were challenges that I enjoyed.

In 2007 I moved to Perth, Western Australia, and joined a dedicated Excel consulting and training company, I’m still there now. My timing was perfect as Excel was suddenly on a rapid path of improvement. Excel 2007 and 2010 with a new Ribbon and Tables and then… then came the big one… the functionality known as Power Pivot, closely followed by Power Query. If you haven’t heard of these things, then you’re not alone.

A silent revolution happened to Excel. In plain sight but under cover of add-ins and understated menus.

Power Pivot and Power Query – the Parents of Power BI

Have you heard of the concept of a “sleeper car”? It’s when someone takes a boring-looking beaten-up old car and puts fuel injection and souped-up suspension in it.

Power Pivot and Power Query brought that super-power to Excel. Suddenly you could build highly flexible reports that could be updated with a click of a button. No longer were you limited to 1 million rows of data or chained to the laborious tasks of copy-paste then filtering and writing thousands of VLOOKUPS that you must remember to drag down when new rows of data get added.

I think more people have now heard of Power BI than have heard of Power Pivot or Power Query, but the core concept was born out of making analysis easier for Excel users. The Power BI of today started by taking Power Query, Power Pivot, and a visualisation layer called Power View and wrapping them together into a single package. Have no doubt that Excel and Power BI are still strongly related with a core set of genes that are infused into both.

☕ If you’d like to hear more about the history of the product then I’d recommend this interview between Amir Netz (CTO of Microsoft Analytics) and Kasper de Jonge (Principal Program Manager Power BI) url.pbi.guide/kasper. At the 20-minute mark, Amir discusses how he came up with the algorithm for the magic behind the scenes of the Power Pivot / Power BI “engine” while sitting naked in his kitchen!

Why I Wrote this Book

I love helping people and I feel there is space for a book that gives an overall instructional guide on how to get started in Power BI aimed at the Excel Analysts of the world. There are millions of us and Power BI’s popularity is continuing to grow.

There are many great books out there that I have learned from, and they tend to have a focus on single elements such as Power Query or DAX or come at Power BI from an IT user perspective.

I wanted to be able to recommend a book to people that covers the whole Power BI process aimed at Excel users transitioning to Power BI. This has been my story and I think I have learned from enough mistakes over the last 7 years and seen enough people struggle with certain elements that I’m well positioned to write a book that helps Excel users make a successful start with Power BI.

The challenge with writing a book on Power BI is how quickly it changes and what to leave out. Since its launch in May 2015 Power BI has developed at an astonishing pace. Every month there are multiple updates, and it has now grown into a fully-fledged Business Intelligence ecosystem. It pulls together the two worlds of the Excel Analysts and the corporate IT departments with a shared product and language.

This book aims to help you learn the core essentials of Power BI from the viewpoint of an Excel user. Excel is the world’s most popular programming platform. That’s right, if you’re writing Excel formulas you ARE a programmer. Put “Functional Language Programmer” on your résumé right now! Many of us push Excel to its limits, creating and copying hundreds of thousands of formulas, VLOOKUPS, and XLOOKUPS everywhere, throwing in some Macros where required. But there is now a new way to build robust refreshable reports without any of that.

Chapter 10 of this book is an “Intermission for Excel fans”. This goes a little into the history of Power Pivot and Power Query and shows you how to apply the things you have learned in the book to Excel. One of the main reasons I’m such a fan of Power BI is that it doesn’t force you to choose Power BI or Excel, it’s about using both with a shared set of techniques.

I hope the book gives you a kick-start on your learning journey.

☕ It’s virtually guaranteed that the names or positions of certain buttons, labels and other elements will have changed by the time you read this book. However, the core principles you learn here will remain relevant for many years, so I hope you can forgive any user interface discrepancies. It’s simply impossible to have a book that is in exact step with a product that is evolving so rapidly.

Acknowledgements

I owe a debt of gratitude to all the Power BI content creators out there. I have learned so much from their books, videos, blogs and presentations that this book simply wouldn’t exist without them. Throughout the book I have added links to various additional resources created by many of the people I have learned from. There are also those who have inspired me to push myself past the point of procrastination and into the world of action. Often these people don’t realise that they lead by example, that they inspire others, and that they make all our lives that little bit better each day.

I’d also like to thank everyone that’s given me positive feedback after a training course, a thumbs up on a social media, or left a kind comment on my YouTube channel. All those moments acknowledging that I have something useful to share, encouraged me to write this book.

Thanks to Microsoft for building an awesome product and for listening to my feedback so willingly.

A massive thanks to Ken and Deanna Puls for helping to make the book far better than I would have managed on my own. And of course, a grateful shout-out to Bill Jelen, MrExcel himself, for publishing this book and patiently answering my questions.

Table of Contents

Foreword

Power Pivot and Power Query – the Parents of Power BI

Why I Wrote this Book

Acknowledgements

Chapter 1 - Getting Started with Power BI

Getting Set Up

Using this Book and Downloading Sample Files

Download the Exercises and view the List of URLs

The PBI.guide Website

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

Interacting with a Power BI Report

Introducing Power Query

Importing and Cleaning Data using Power Query

Summary of Your Introduction to Power Query

Chapter 3 - Publishing Your Report

Signing in to PowerBI.com for the First Time

The PowerBI.com Experience (aka “the Service”)

Creating a Workspace

Power BI Licence Options: A Brief Overview

Chapter 4 - Files Stored in SharePoint/OneDrive for Business

Step 1: Finding the Connection Path

Step 2: Using the Power BI Desktop Web Connection

Step 3: Pulling the Data into Power BI

Step 4: Build a Simple Visual

Step 5: Publish to Your New Workspace

Step 6: Set up a Scheduled Refresh

Chapter 5 - Creating a Power BI Model

Using a Template File with a Pre-built Calendar Table

Creating Relationships Between Tables

Managing Sort Order

Adding Additional “Lookup/Dimension” Tables

Adjusting Power BI Visuals

Filtering via Slicers and the Filter Panel

Exploring More Visuals

Chapter 6 - Getting Your Data into the “Right Shape”

Power Query’s Two Best Features in One Chapter!

Comparing Data from Two Fact Tables

Chapter 7 - DAX (Data Analysis eXpressions)

Writing Your First DAX Measure

Storing Measures in their Own Dedicated Table

Year to Date Measure

Prior Year Comparison and the CALCULATE Function

Removing Filters

Formatting Your DAX

Ratios and Percentages Using DIVIDE

Virtual Calculated Columns using the X Functions

Dealing with Multiple Date Fields in Your Fact Table

Organising Measures into Folders

DAX – Next Steps in Your Learning

Chapter 8 - The Calendar Table

Turning Off Auto Date/Time for New Files

Power Query Advanced Editor

Copying Queries Between files

Changing the Display Order of Fields

Marking as Date Table

Chapter 9 - Creating a Template File

Setting Your Default Theme Fonts and Colours

Adding a Measures Table

Using Your Template

Editing/Updating Templates

Chapter 10 - Intermission for Excel Fans

A Little History of Power BI

A Demonstration of Excel’s “Power” Features

Create an Interactive Pivot Chart

Chapter 11 - Enrich Your Power BI Report

Conditional Formatting

Tooltips

Drill-through Page

Report Design Tips

Making Analysis Easier

Natural Language Queries and AI-Driven Insights

Chapter 12 - Sharing Your Reports via Apps

Publish Your Report to the Workspace

Create an App from Your Workspace

Sharing the App

Updating a Report and an App

Scheduling a Refresh where a Gateway is Required

Chapter 13 - Additional Important Features

Row-Level Security

Dataflows

Connecting to a Dataset via Power BI Desktop

Chapter 14 - Where Do We Go from Here?

Some Topics We Didn’t Cover…

Next Steps in Your Power BI Journey

Pages after the Index in the printed book

Index

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 displayHover your mouse over the bar for Brisbane (1). A tooltip will pop up giving more informationClick 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 buttonRight-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 pageClick 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 laterClicking 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 development for the Excel Analyst… Power Query.

Introducing Power Query

Power Query is the Worlds’ greatest washing machine for dirty data.

I’ve worked with companies of all sizes, and no matter how big the company is there’s always a need to clean up the data before you can present or analyse it.

It is truly a revolutionary feature that was originally an add-in for Excel. It’s now natively part of Excel, found under the Data tab, and it’s the same Power Query that exists in Power BI. The Power Query lessons in this book are therefore also applicable to Excel. As you’ll see later you can even easily copy and paste queries between Excel and Power BI.

☕ I’ve seen people’s jaws drop when I show them what Power Query can do and then tell them it’s been built in since Excel 2016. They feel a combination of excitement that their daily lives are about to get much easier coupled with low-level resentment that no one told them about this earlier!

Importing and Cleaning Data using Power Query

Here are the steps you’ll follow to introduce Power Query in the context of Power BI:

1. You will import some data from an Excel file

2. Clean it up and re-organise it

3. Load it into the Power BI file and build a simple chart

4. Then go back into Power Query and do a little extra data clean up

Before you start, open the following file in Excel just to see what the data looks like: Exercises\Data Sources\Gym Membership\Gym Membership.xlsxClose the Excel file before proceeding. Power Query sometimes is unable to pull in data from open files. The screenshot above gives you an idea of the contents of the fileStart a new Power BI file. If a cover screen appears close it (1)

There are 3 different points you can click to import data from Excel. They all do the same thing, which is to allow you to connect to an Excel file and then import the data.

Click on any of the 3 Excel icons shown above and then navigate to the folder Exercises\Data Sources\Gym Membership\Double-click on the Membership Data Excel file (1) This launches the Power Query Navigator window as shown below. The left-hand side lists any sheets, Tables, or range names that exist in the Excel file. Click on the word “Members” (1) and a preview appears Right-click on the word “Members” (2) and 2 options appearClick on Transform data (3). 99% of the time you choose Transform data as this then allows you to clean up the data in the “washing machine” before you load it into the Data Model

💡