Power BI for the Excel Analyst © 2022 Tickling Keys, Inc. and Exponen al BI All rights reserved. No part of this book may be reproduced or transmi ed in any form or by any means, electronic or mechanical, including photocopying, recording, or by any informa on or storage retrieval system without permission from the publisher. Every e ort has been made to make this book as complete and accurate as possible, but no warranty or tness is implied.
The informa on is provided on an “as is” basis. The authors and the publisher shall have neither liability nor responsibility to any person or en ty with respect to any loss or damages arising from the informa on contained in this book. Author: Wyn Hopkins Layout: Bronkella Publishing Copyedi ng: Deanna Puls Tech Edit: Ken Puls Proofreader: Bill Jelen Cover Design: Shannon Travise Indexing: Nellie Jay Published by: Holy Macro! Books, PO Box 541731, Merri Island FL 32953, USA Distributed by: Independent Publishers Group, Chicago, IL Printed by Sheridan South, Brim eld Ohio First Prin ng: 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 tle 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 solu ons 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 rst taste of data was as a fresh-faced intern with Hewle 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 stu. 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 consul ng and training company, I’m s ll there now. My ming 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 func onality known as Power Pivot, closely followed by Power Query. If you haven’t heard of these things, then you’re not alone.
A silent revolu on 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 injec on and souped-up suspension in it. Power Pivot and Power Query brought that super-power to Excel.
Suddenly you could build highly exible reports that could be updated with a click of a bu on. No longer were you limited to 1 million rows of data or chained to the laborious tasks of copy-paste then ltering and wri ng 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 visualisa on layer called Power View and wrapping them together into a single package.
Have no doubt that Excel and Power BI are s ll 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 Microso Analy cs) and Kasper de Jonge (Principal Program Manager Power BI) url. 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 si ng 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 instruc onal 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 con nuing 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 perspec ve. I wanted to be able to recommend a book to people that covers the whole Power BI process aimed at Excel users transi oning 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 posi oned to write a book that helps Excel users make a successful start with Power BI. The challenge with wri ng 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 mul ple updates, and it has now grown into a fully- edged 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 essen als of Power BI from the viewpoint of an Excel user.
Excel is the world’s most popular programming pla orm. That’s right, if you’re wri ng Excel formulas you ARE a programmer. Put “Func onal Language Programmer” on your résumé right now! Many of us push Excel to its limits, crea ng 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 li le 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 posi ons of certain bu ons, labels and other elements will have changed by the me 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 gra tude to all the Power BI content creators out there.
I have learned so much from their books, videos, blogs and presenta ons that this book simply wouldn’t exist without them. Throughout the book I have added links to various addi onal 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 procras na on and into the world of ac on. O en these people don’t realise that they lead by example, that they inspire others, and that they make all our lives that li le bit be er each day.
I’d also like to thank everyone that’s given me posi ve feedback a er a training course, a thumbs up on a social media, or le 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 Microso 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 be er 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 pa ently answering my ques ons. Table of Contents Foreword Power Pivot and Power Query – the Parents of Power BI Why I Wrote this Book Acknowledgements Chapter 1 - Ge ng Started with Power BI Ge ng 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 Introduc on to Power BI Desktop Interac ng with a Power BI Report Introducing Power Query Impor ng and Cleaning Data using Power Query Summary of Your Introduc on to Power Query Chapter 3 - Publishing Your Report Signing in to PowerBI.com for the First Time The PowerBI.com Experience (aka “the Service”) Crea ng a Workspace Power BI Licence Op ons: A Brief Overview Chapter 4 - Files Stored in SharePoint/OneDrive for Business Step 1: Finding the Connec on Path Step 2: Using the Power BI Desktop Web Connec on 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 - Crea ng a Power BI Model Using a Template File with a Pre-built Calendar Table Crea ng Rela onships Between Tables Managing Sort Order Adding Addi onal “Lookup/Dimension” Tables Adjus ng Power BI Visuals Filtering via Slicers and the Filter Panel Exploring More Visuals Chapter 6 - Ge ng 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) Wri ng Your First DAX Measure Storing Measures in their Own Dedicated Table Year to Date Measure Prior Year Comparison and the CALCULATE Func on Removing Filters Forma ng Your DAX Ra os and Percentages Using DIVIDE Virtual Calculated Columns using the X Func ons Dealing with Mul ple Date Fields in Your Fact Table Organising Measures into Folders DAX – Next Steps in Your Learning Chapter 8 - The Calendar Table Turning O Auto Date/Time for New Files Power Query Advanced Editor Copying Queries Between les Changing the Display Order of Fields Marking as Date Table Chapter 9 - Crea ng a Template File Se ng Your Default Theme Fonts and Colours Adding a Measures Table Using Your Template Edi ng/Upda ng Templates Chapter 10 - Intermission for Excel Fans A Li le History of Power BI A Demonstra on of Excel’s “Power” Features Create an Interac ve Pivot Chart Chapter 11 - Enrich Your Power BI Report Condi onal Forma ng Tool ps 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 Upda ng a Report and an App Scheduling a Refresh where a Gateway is Required Chapter 13 - Addi onal Important Features Row-Level Security Data ows Connec ng to a Dataset via Power BI Desktop Chapter 14 - Where Do We Go from Here?