Table Of Content

Excel® Power Pivot & Power Query For Dummies® Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com Copyright © 2016 by John Wiley & Sons, Inc., Hoboken, New Jersey Published simultaneously in Canada No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without the prior written permission of the Publisher. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions. Trademarks: Wiley, For Dummies, the Dummies Man logo, Dummies.com, Making Everything Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and may not be used without written permission. Excel is a registered trademark of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book. LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ. For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317- 572-3993, or fax 317-572-4002. For technical support, please visit www.wiley.com/techsupport. Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com. Library of Congress Control Number: 2016933854 ISBN 978-1-119-21064-1 (pbk); ISBN 978-1-119-21066-5 (ebk); ISBN 978-1-119- 21065-8 (ebk) Excel® Power Pivot & Power Query For Dummies® Visit www.dummies.com/cheatsheet/excelpowerpivotpowerquery to view this book’s cheat sheet. Table of Contents Cover Introduction About This Book Foolish Assumptions How This Book Is Organized Icons Used In This Book Beyond the Book Where to Go from Here Part I: Supercharged Reporting with Power Pivot Chapter 1: Thinking Like a Database Exploring the Limits of Excel and How Databases Help Getting to Know Database Terminology Understanding Relationships Chapter 2: Introducing Power Pivot Understanding the Power Pivot Internal Data Model Activating the Power Pivot Add-In Linking Excel Tables to Power Pivot Chapter 3: The Pivotal Pivot Table Introducing the Pivot Table Defining the Four Areas of a Pivot Table Creating Your First Pivot Table Customizing Pivot Table Reports Understanding Slicers Creating a Standard Slicer Getting Fancy with Slicer Customizations Controlling Multiple Pivot Tables with One Slicer Creating a Timeline Slicer Chapter 4: Using External Data with Power Pivot Loading Data from Relational Databases Loading Data from Flat Files Loading Data from Other Data Sources Refreshing and Managing External Data Connections Chapter 5: Working Directly with the Internal Data Model Directly Feeding the Internal Data Model Adding a New Table to the Internal Data Model Removing a Table from the Internal Data Model Creating a New Pivot Table Using the Internal Data Model Filling the Internal Data Model with Multiple External Data Tables Chapter 6: Adding Formulas to Power Pivot Enhancing Power Pivot Data with Calculated Columns Utilizing DAX to Create Calculated Columns Understanding Calculated Measures Free Your Data With Cube Functions Chapter 7: Publishing Power Pivot to SharePoint Understanding SharePoint Understanding Excel Services for SharePoint Publishing an Excel Workbook to SharePoint Publishing to a Power Pivot Gallery Part II: Wrangling Data with Power Query Chapter 8: Introducing Power Query Installing and Activating a Power Query Add-In Power Query Basics Understanding Column-Level Actions Understanding Table Actions Chapter 9: Power Query Connection Types Importing Data from Files Importing Data from Database Systems Managing Data Source Settings Chapter 10: Transforming Your Way to Better Data Completing Common Transformation Tasks Creating Custom Columns Grouping and Aggregating Data Chapter 11: Making Queries Work Together Reusing Query Steps Understanding the Append Feature Understanding the Merge Feature Chapter 12: Extending Power Query with Custom Functions Creating and Using a Basic Custom Function Creating a Function to Merge Data from Multiple Excel Files Creating Parameter Queries Part III: The Part of Tens Chapter 13: Ten Ways to Improve Power Pivot Performance Limit the Number of Rows and Columns in Your Data Model Tables Use Views Instead of Tables Avoid Multi-Level Relationships Let the Back-End Database Servers Do the Crunching Beware of Columns with Non-Distinct Values Limit the Number of Slicers in a Report Create Slicers Only on Dimension Fields Disable the Cross-Filter Behavior for Certain Slicers Use Calculated Measures Instead of Calculated Columns Upgrade to 64-Bit Excel Chapter 14: Ten Tips for Working with Power Query Getting Quick Information from the Workbook Queries Pane Organizing Queries in Groups Selecting Columns in Queries Faster Renaming Query Steps Quickly Creating Reference Tables Copying Queries to Save Time Setting a Default Load Behavior Preventing Automatic Data Type Changes Disabling Privacy Settings to Improve Performance Disabling Relationship Detection About the Author Cheat Sheet Advertisement Page Connect with Dummies End User License Agreement 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