Michael Alexander - Excel Power Pivot & Power Query For Dummies

Recognizing the importance of the self-service BI revolution and the role Excel plays in it, Microsoft has made substantial investments in making Excel a player in the self-service BI arena by embedding both Power Pivot and Power Query directly into Excel.

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.

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

Within this book, you may note that some web addresses break across two lines of text. If you’re reading this book in print and want to visit one of these web pages, simply key in the web address exactly as it’s noted in the text, pretending as though the line break doesn’t exist. If you’re reading this as an e-book, you’ve got it easy — just click the web address to be taken directly to the web page.

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.

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.

In addition to the book you have in your hands, you can access some extra content online. Check out the free Cheat Sheet for lists of Power Query text functions and Power Query date functions that are good to know. Just go to www.dummies.com and type Excel Power Pivot & Power Query For Dummies Cheat Sheetin the Search box.

If you want to follow along with the examples in this book, you can download the sample files at www.dummies.com/go/excelpowerpivotpowerqueryfd2e . The files are organized by chapter.

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 2, which begins at Chapter 8.

Part 1

IN THIS PART …

Think about data like a relational database.

Create your own Power Pivot data model.

Explore the workings of pivot tables.

Use external data with Power Pivot.

Manage the Power Pivot internal data model.

Create your own formulas in Power Pivot.

Delve deeper into the DAX formula language.

Chapter 1

IN THIS CHAPTER

Examining traditional Excel limitations

Keeping up with database terminology

Looking into relationships

With the introduction of business intelligence (BI) tools such as Power Pivot and Power Query, it’s becoming increasingly important for Excel analysts to understand core database principles. Unlike traditional Excel concepts, where the approach to developing solutions is relatively intuitive, you need to have a basic understanding of database terminology and architecture in order to get the most benefit from Power Pivot and Power Query. This chapter introduces you to a handful of fundamental concepts that you should know before taking on the rest of this book.