Introduction

Over the past few years, the concept of self-service business intelligence (BI) has taken over the corporate world. Self-service BI is a form of business intelligence in which end users can independently generate their own reports, run their own queries, and conduct their own analyses, without the need to engage the IT department.

The demand for self-service BI is a direct result of several factors:

• More power users: Organizations are realizing that no single enterprise reporting system or BI tool can accommodate all of their users. Predefined reports and high-level dashboards may be sufficient for casual users, but a large portion of today’s users are savvy enough to be considered power users. Power users have a greater understanding of data analysis and prefer to perform their own analysis, often within Excel.
• Changing analytical needs: In the past, business intelligence primarily consisted of IT-managed dashboards showing historic data on an agreed-upon set of key performance metrics. Managers now demand more dynamic predictive analysis, the ability to perform data discovery iteratively, and the freedom to take the hard left and right turns on data presentation. These managers often turn to Excel to provide the needed analytics and visualization tools.
• Speed of BI: Users are increasingly dissatisfied with the inability of IT to quickly deliver new reporting and metrics. Most traditional BI implementations fail specifically because the need for changes and answers to new questions overwhelmingly outpaces the IT department’s ability to deliver them. As a result, users often find ways to work around the perceived IT bottleneck and ultimately build their own shadow BI (under the radar) solutions in Excel.

Recognizing the importance of the self-service BI revolution and the role Excel plays in it, Microsoft has made substantial investments in making Excel the cornerstone of its self-service BI offering. These investments have appeared starting with Excel 2007. Here are a few of note: the ability to handle over a million rows, tighter integration to SQL Server, pivot table slicers, and not least of all, the introduction of the Power Pivot and Power Query add-ins.

With the release of Excel 2016, Microsoft has aggressively moved to make Excel a player in the self-service BI arena by embedding both Power Pivot and Power Query directly into Excel.

For the first time, Excel is an integral part of the Microsoft BI stack. You can integrate multiple data sources, define relationships between data sources, process analysis services cubes, and develop interactive dashboards that can be shared on the web. Indeed, the new Microsoft BI tools blur the line between Excel analysis and what is traditionally IT enterprise-level data management and reporting capabilities.

With these new tools in the Excel wheelhouse, it’s becoming important for business analysts to expand their skill sets to new territory, including database management, query design, data integration, multidimensional reporting, and a host of other skills. Excel analysts have to expand their skill set knowledge base from the one-dimensional spreadsheets to relational databases, data integration, and multidimensional reporting,

That’s where this book comes in. Here, you’re introduced to the mysterious world of Power Pivot and Power Query. You find out how to leverage the rich set of tools and reporting capabilities to save time, automate data clean-up, and substantially enhance your data analysis and reporting capabilities.

About This Book

The goal of this book is to give you a solid overview of the self-service BI functionality offered by Power Pivot and Power Query. Each chapter guides you through practical techniques that enable you to

• Extract data from databases and external files for use in Excel reporting
• Scrape and import data from the web
• Build automated processes to clean and transform data
• Easily slice data into various views on the fly, gaining visibility from different perspectives
• Analyze large amounts of data and report them in a meaningful way
• Create powerful, interactive reporting mechanisms and dashboards

Foolish Assumptions

Over the past few years, Microsoft has adopted an agile release cycle, allowing the company to release updates to Microsoft Office and the power BI tools practically monthly. This is great news for those who love seeing new features added to Power Pivot and Power Query. (It’s not-so-great news if you’re trying to document the features of these tools in a book.)

My assumption is that Microsoft will continue to add new bells and whistles to Power Pivot and Power Query at a rapid pace after publication of this book. So you may encounter new functionality not covered here.

The good news is that both Power Pivot and Power Query have stabilized and already have a broad feature set. So I’m also assuming that although changes will be made to these tools, they won’t be so drastic as to turn this book into a doorstop. The core functionality covered in these chapters will remain relevant — even if the mechanics change a bit.

How This Book Is Organized

The chapters in this book are organized into three parts. Part I focuses on Power Pivot. Part II explores Power Query. Part III wraps up the book with the classic Part of Tens.

Icons Used In This Book

As you look in various places in this book, you see icons in the margins that indicate material of interest (or not, as the case may be). This section briefly describes each icon in this book.

Tips are beneficial because they help you save time or perform a task without having to do a lot of extra work. The tips in this book are time-saving techniques or pointers to resources that you should check out to get the maximum benefit from Excel.

Try to avoid doing anything marked with a Warning icon, which (as you might expect) represents a danger of one sort or another.

Whenever you see this icon, think advanced tip or technique. You might find these tidbits of useful information just too boring for words, or they could contain the solution you need to get a program running. Skip these bits of information whenever you like.

If you get nothing else out of a particular chapter or section, remember the material marked by this icon. This text usually contains an essential process or a bit of information you ought to remember.

Paragraphs marked with this icon reference the sample files for the book. If you want to follow along with the examples, you can download the sample files at www.dummies.com/go/powerpivotpowerqueryfd. The files are organized by chapter.

Beyond the Book

A lot of extra content that you won’t find in this book is available at www.dummies.com. Go online to find the following:

• Excel files used in the examples in this book can be found at

  www.dummies.com/go/excelpowerpivotpowerqueryfd

• Online articles covering additional topics are at

  www.dummies.com/extras/excelpowerpivotpowerquery

  On this page, you can see how to integrate Power Pivot and Power Query to create a dynamic reporting duo. You can also uncover a list of resources to aid you in your Power BI journey.

• The Cheat Sheet for this book is at

  www.dummies.com/cheatsheet/excelpowerpivotpowerquery

  On this page, you find a list of useful Power Query functions that can be used to enhance the data clean-up and transformation process.

• Updates to this book, if we have any, are also available at

  www.dummies.com/extras/excelpowerpivotpowerquery

Where to Go from Here

It’s time to start your self-service BI adventure! If you’re primarily interested in Power Pivot, start with Chapter 1. If you want to dive right into Power Query, jump to Part II, which begins at Chapter 8.