Microsoft® Excel® Power Pivot & Power Query For Dummies®, 2nd Edition

Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com

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. Microsoft and Excel are registered trademarks of Microsoft Corporation in the United States and other countries. 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: WHILE THE PUBLISHER AND AUTHORS HAVE USED THEIR BEST EFFORTS IN PREPARING THIS WORK, THEY 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 ANY IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES REPRESENTATIVES, WRITTEN SALES MATERIALS OR PROMOTIONAL STATEMENTS FOR THIS WORK. THE FACT THAT AN ORGANIZATION, WEBSITE, OR PRODUCT IS REFERRED TO IN THIS WORK AS A CITATION AND/OR POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE PUBLISHER AND AUTHORS ENDORSE THE INFORMATION OR SERVICES THE ORGANIZATION, WEBSITE, OR PRODUCT MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING PROFESSIONAL SERVICES. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR YOUR SITUATION. YOU SHOULD CONSULT WITH A SPECIALIST WHERE APPROPRIATE. FURTHER, READERS SHOULD BE AWARE THAT WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ. NEITHER THE PUBLISHER NOR AUTHORS SHALL BE LIABLE FOR ANY LOSS OF PROFIT OR ANY OTHER COMMERCIAL DAMAGES, INCLUDING BUT NOT LIMITED TO SPECIAL, INCIDENTAL, CONSEQUENTIAL, OR OTHER DAMAGES.

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 https://hub.wiley.com/community/support/dummies.

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: 2022930084

ISBN 978-1-119-84448-8 (pbk); ISBN 978-1-119-84449-5 (ebk); ISBN 978-1-119-84450-1 (ebk)

Microsoft® Excel® Power Pivot & Power Query For Dummies®

To view this book's Cheat Sheet, simply go to www.dummies.com and search for “Microsoft Excel Power Pivot Power Query For Dummies Cheat Sheet” in the Search box.

Table of Contents

Cover

Title Page

Introduction

About This Book
Foolish Assumptions
Icons Used in This Book
Beyond the Book
Where to Go from Here

Part 1: Supercharged Reporting with Power Pivot

Chapter 1: Thinking Like a Database
1. Exploring the Limits of Excel and How Databases Help
2. Getting to Know Database Terminology
3. Understanding Relationships
Chapter 2: Introducing Power Pivot
1. Understanding the Power Pivot Internal Data Model
2. Linking Excel Tables to Power Pivot
Chapter 3: The Pivotal Pivot Table
1. Introducing the Pivot Table
2. Defining the Four Areas of a Pivot Table
3. Creating Your First Pivot Table
4. Customizing Pivot Table Reports
5. Understanding Slicers
6. Creating a Standard Slicer
7. Getting Fancy with Slicer Customizations
8. Controlling Multiple Pivot Tables with One Slicer
9. Creating a Timeline Slicer
Chapter 4: Using External Data with Power Pivot
1. Loading Data from Relational Databases
2. Loading Data from Flat Files
3. Loading Data from Other Data Sources
4. Refreshing and Managing External Data Connections
Chapter 5: Working Directly with the Internal Data Model
1. Directly Feeding the Internal Data Model
2. Managing Relationships in the Internal Data Model
3. Managing Queries and Connections
4. Creating a New Pivot Table Using the Internal Data Model
5. Filling the Internal Data Model with Multiple External Data Tables
Chapter 6: Adding Formulas to Power Pivot
1. Enhancing Power Pivot Data with Calculated Columns
2. Utilizing DAX to Create Calculated Columns
3. Understanding Calculated Measures
4. Free Your Data with Cube Functions
Chapter 7: Diving into DAX
1. DAX Language Fundamentals
2. Understanding Filter Context

Part 2: Wrangling Data with Power Query

Chapter 8: Introducing Power Query
1. Power Query Basics
2. Understanding Column-Level Actions
3. Understanding Table Actions
Chapter 9: Power Query Connection Types
1. Importing Data from Files
2. Importing Data from Database Systems
3. Managing Data Source Settings
4. Data Profiling with Power Query
Chapter 10: Transforming Your Way to Better Data
1. Completing Common Transformation Tasks
2. Creating Custom Columns
3. Grouping and Aggregating Data
4. Working with Custom Data Types
Chapter 11: Making Queries Work Together
1. Reusing Query Steps
2. Understanding the Append Feature
3. Understanding the Merge Feature
4. Understanding Fuzzy Match
Chapter 12: Extending Power Query with Custom Functions
1. Creating and Using a Basic Custom Function
2. Creating a Function to Merge Data from Multiple Excel Files
3. Creating Parameter Queries

Part 3: The Part of Tens

Chapter 13: Ten Ways to Improve Power Pivot Performance
1. Limit the Number of Rows and Columns in Your Data Model Tables
2. Use Views Instead of Tables
3. Avoid Multi-Level Relationships
4. Let the Back-End Database Servers Do the Crunching
5. Beware of Columns with Many Unique Values
6. Limit the Number of Slicers in a Report
7. Create Slicers Only on Dimension Fields
8. Disable the Cross-Filter Behavior for Certain Slicers
9. Use Calculated Measures Instead of Calculated Columns
10. Upgrade to 64-Bit Excel
Chapter 14: Ten Tips for Working with Power Query
1. Getting Quick Information from the Queries & Connections Pane
2. Organizing Queries in Groups
3. Selecting Columns in Queries Faster
4. Renaming Query Steps
5. Quickly Creating Reference Tables
6. Viewing Query Dependencies
7. Setting a Default Load Behavior
8. Preventing Automatic Data Type Changes
9. Disabling Privacy Settings to Improve Performance
10. Disabling Relationship Detection

Index

About the Author

Connect with Dummies

End User License Agreement

List of Illustrations

Chapter 1

FIGURE 1-1: Data is stored in an Excel spreadsheet using a flat-table format.
FIGURE 1-2: Databases use relationships to store data in unique tables and simp...

Chapter 2

FIGURE 2-1: The Power Pivot Ribbon interface.
FIGURE 2-2: You want to use Power Pivot to analyze the data in the Customers, I...
FIGURE 2-3: Convert the data range into an Excel table.
FIGURE 2-4: Give your newly created Excel table a friendly name.
FIGURE 2-5: The Power Pivot window shows all the data that exists in your data ...
FIGURE 2-6: Each table you add to the data model is placed on its own tab in Po...
FIGURE 2-7: Diagram view allows you to see all tables in the data model.
FIGURE 2-8: To create a relationship, you simply click and drag a line between ...
FIGURE 2-9: When you create relationships, the Power Pivot diagram shows join l...
FIGURE 2-10: Use the Manage Relationships dialog box to edit or delete existing...
FIGURE 2-11: Use the Edit Relationship dialog box to adjust the tables and fiel...
FIGURE 2-12: You now have a Power Pivot-driven pivot table that aggregates acro...

Chapter 3

FIGURE 3-1: The values area of a pivot table calculates and counts data.
FIGURE 3-2: The row area of a pivot table gives you a row-oriented perspective.
FIGURE 3-3: The column area of a pivot table gives you a column-oriented perspe...
FIGURE 3-4: The filter area allows you to easily apply filters to the pivot tab...
FIGURE 3-5: Start a pivot table via the Insert tab.
FIGURE 3-6: The Create PivotTable dialog box.
FIGURE 3-7: The PivotTable Fields task pane.
FIGURE 3-8: Select the Market check box.
FIGURE 3-9: Add the Sales Amount field by selecting its check box.
FIGURE 3-10: Adding a layer of analysis is as easy as bringing in another field...
FIGURE 3-11: Your business segments are now column oriented.
FIGURE 3-12: Adding Region to the Filters drop zone displays a Region drop-down...
FIGURE 3-13: Refreshing the pivot table captures changes made to your data.
FIGURE 3-14: Changing the range that feeds the pivot table.
FIGURE 3-15: Select the new range that feeds the pivot table.
FIGURE 3-16: The three layouts for a pivot table report.
FIGURE 3-17: Changing the layout of the pivot table.
FIGURE 3-18: Right-click the target field to select the Value Field Settings op...
FIGURE 3-19: Use the Custom Name input box to change the name of the field.
FIGURE 3-20: Changing the type of summary calculation used in a field.
FIGURE 3-21: Subtotals sometimes muddle the data you’re trying to show.
FIGURE 3-22: Use the Do Not Show Subtotals option to remove all subtotals at on...
FIGURE 3-23: The report shown in Figure 3-21, without subtotals.
FIGURE 3-24: Choose the None option to remove subtotals for one field.
FIGURE 3-25: To remove Bikes from this analysis …
FIGURE 3-26: … deselect the Bikes check box.
FIGURE 3-27: The analysis from Figure 3-25, without the Bikes segment.
FIGURE 3-28: Clicking the Select All check box forces all data items in that fi...
FIGURE 3-29: All sales periods are showing.
FIGURE 3-30: Filtering for the Europe region causes certain sales periods to di...
FIGURE 3-31: Select the Show Items with No Data option to force Excel to displa...
FIGURE 3-32: All sales periods are now displayed, even if there is no data to b...
FIGURE 3-33: Applying a sort to a pivot table field.
FIGURE 3-34: Default pivot table Filter fields do not work together to limit fi...
FIGURE 3-35: Filter fields show the phrase (Multiple Items) whenever multiple s...
FIGURE 3-36: Slicers work together to show you relevant data items based on you...
FIGURE 3-37: Slicers do a better job at displaying multiple item selections.
FIGURE 3-38: Inserting a slicer.
FIGURE 3-39: Select the fields for which you want slicers created.
FIGURE 3-40: Select the fields you want filtered using slicers.
FIGURE 3-41: The fact that you can see the current filter state gives slicers a...
FIGURE 3-42: Clearing the filters on a slicer.
FIGURE 3-43: Adjust the slicer size and placement by dragging its position poin...
FIGURE 3-44: The Format Slicer pane offers more control over how the slicer beh...
FIGURE 3-45: Adjust the Number of Columns property to display the slicer data i...
FIGURE 3-46: The Slicer Settings dialog box.
FIGURE 3-47: Choose the pivot tables to be filtered by this slicer.
FIGURE 3-48: Select the date fields for which you want slicers created.
FIGURE 3-49: Click a date selection to filter the pivot table or pivot chart.
FIGURE 3-50: You can expand the range on the Timeline slicer to include more da...
FIGURE 3-51: Quickly switch among Quarters, Years, Months, and Days.

Chapter 4

FIGURE 4-1: Open the Table Import Wizard and select Microsoft SQL Server.
FIGURE 4-2: Provide the basic information needed to connect to the target datab...
FIGURE 4-3: Choose to select from a list of tables and views.
FIGURE 4-4: The Table Import Wizard offers up a list of tables and views.
FIGURE 4-5: The Preview & Filter screen allows you to uncheck columns you don’t...
FIGURE 4-6: Use the drop-down arrows next to each column to filter out unneeded...
FIGURE 4-7: The last screen of the Table Import Wizard shows you the progress o...
FIGURE 4-8: Open the Table Import Wizard and select Microsoft Access.
FIGURE 4-9: Provide the basic information needed to connect to the target datab...
FIGURE 4-10: Open the Table Import Wizard and select your target relational dat...
FIGURE 4-11: Enter the connection string for your database system.
FIGURE 4-12: Use the Data Link Properties dialog box to configure a custom conn...
FIGURE 4-13: The Table Import Wizard displays the final syntax for your connect...
FIGURE 4-14: Open the Table Import Wizard and select Excel File.
FIGURE 4-15: Provide the basic information needed to connect to the target work...
FIGURE 4-16: Select the data sources to import.
FIGURE 4-17: Open the Table Import Wizard and select Text File.
FIGURE 4-18: Provide the basic information needed to connect to the target text...
FIGURE 4-19: You can copy data straight out of Microsoft Word.
FIGURE 4-20: The Paste Preview dialog box gives you a chance to see what you’re...
FIGURE 4-21: Power Pivot allows you to refresh one table or all tables.
FIGURE 4-22: Select a connection and click the Properties button.
FIGURE 4-23: The Connection Properties dialog box lets you configure the chosen...
FIGURE 4-24: The Connection Properties dialog box lets you configure the chosen...
FIGURE 4-25: Use the Existing Connections dialog box to reconfigure your Power ...

Chapter 5

FIGURE 5-1: This table shows transactions by employee number.
FIGURE 5-2: This table provides information on employees: first name, last name...
FIGURE 5-3: When you create a new pivot table from the Transactions table, be s...
FIGURE 5-4: Create a new pivot table from the Employees table, and select Add T...
FIGURE 5-5: Select ALL in the PivotTable Fields list to see both tables in the ...
FIGURE 5-6: When Excel prompts you, choose to create the relationship between t...
FIGURE 5-7: Build the appropriate relationship using the Table and Column drop-...
FIGURE 5-8: You’ve achieved your goal of showing sales by job title.
FIGURE 5-9: The Manage Relationships dialog box enables you to make changes to ...
FIGURE 5-10: Use the Queries & Connections task pane to manage the queries and ...
FIGURE 5-11: Open the Create PivotTable dialog box and choose the external data...
FIGURE 5-12: Use the Existing Connections dialog box to select the Data Model a...
FIGURE 5-13: The newly created pivot table shows all tables in the internal dat...
FIGURE 5-14: Getting data from a Microsoft Access database.
FIGURE 5-15: Enable the selection of multiple tables.
FIGURE 5-16: Place a check next to each table you want import to the internal d...
FIGURE 5-17: Create a PivotTable Report from the Import Data dialog.
FIGURE 5-18: You’re ready to build your pivot table analysis based on multiple ...

Chapter 6

FIGURE 6-1: Start the calculated column by entering an operation on the Formula...
FIGURE 6-2: Calculated columns automatically show up in the PivotTable Fields L...
FIGURE 6-3: You can use the formatting tools found on the Power Pivot window’s ...
FIGURE 6-4: The new Gross Margin calculation is using the previously created [T...
FIGURE 6-5: Right-click and select Hide from Client Tools.
FIGURE 6-6: Hidden columns are grayed-out, and calculated columns have darker h...
FIGURE 6-7: The Insert Function dialog box shows you all available DAX function...
FIGURE 6-8: The DAX SUM function can only sum the column as a whole.
FIGURE 6-9: DAX functions can help enhance the invoice header data with Year an...
FIGURE 6-10: Using DAX functions to supplement a table with Year, Month, and Mo...
FIGURE 6-11: DAX calculations are immediately available in any connected pivot ...
FIGURE 6-12: Month names in Power Pivot-driven pivot tables don’t automatically...
FIGURE 6-13: The Sort by Column dialog box lets you define how columns are sort...
FIGURE 6-14: The month names now show in the correct month order.
FIGURE 6-15: The discount amount in the Customers table can be used in a calcul...
FIGURE 6-16: Use the RELATED function to look up a field from another table.
FIGURE 6-17: The final discount amount calculated column using the Discount% co...
FIGURE 6-18: Creating a new calculated measure.
FIGURE 6-19: Calculated measures can be seen in the PivotTable Fields List.
FIGURE 6-20: The Manage Measures dialog box lets you edit or delete your calcul...
FIGURE 6-21: These cells are now a series of Cube functions.
FIGURE 6-22: Excel gives you the option of converting your report filter fields...
FIGURE 6-23: Cube functions give you the flexibility of restructuring your pivo...

Chapter 7

FIGURE 7-1: Creating a new measure that calculates Total Revenue.
FIGURE 7-2: The results of a calculated measure can be seen by adding it to a p...
FIGURE 7-3: Creating a new measure that will calculate Total Units.
FIGURE 7-4: DAX allows you to use existing measure as arguments in other measur...
FIGURE 7-5: DAX formulas recalculate to show appropriate results based on the d...
FIGURE 7-6: I need a calculated measure that returns OrderQuantity * UnitPrice ...
FIGURE 7-7: The [Realized Sales] measure uses a simple Sum aggregator function ...
FIGURE 7-8: The [Realized Sales] measure looks good at the granular OrderNumber...
FIGURE 7-9: The math falls apart when you move to any granularity above OrderNu...
FIGURE 7-10: Using the SUMX function enables the measure to retain row context ...
FIGURE 7-11: The new [Realized PriceX] measure remains accurate now at every ag...
FIGURE 7-12: You need to calculate the sum of Sales Amount in cell E1, but only...
FIGURE 7-13: The resulting answer is for the specific filter context applied.
FIGURE 7-14: Each cell in a pivot table contains its own filter context.
FIGURE 7-15: Filter context is propagated between tables via relationships in t...
FIGURE 7-16: The [Bike Sales] measure has its own product category context, so ...
FIGURE 7-17: Using CALCULATE and FILTER together to establish a new filter cont...

Chapter 8

FIGURE 8-1: Starting a Power Query web query.
FIGURE 8-2: Enter the target URL containing the data you need.
FIGURE 8-3: Select the correct data source and then click the Transform Data bu...
FIGURE 8-4: The Query Editor window allows you to shape, clean, and transform d...
FIGURE 8-5: Change the data type of the High, Low, and Close fields to currency...
FIGURE 8-6: Select the columns you want to keep, and then select Remove Other C...
FIGURE 8-7: Removing errors caused by text values that could not be converted t...
FIGURE 8-8: The Power Query Editor can be used to apply transformation actions ...
FIGURE 8-9: The Import Data dialog box gives you more control over how the resu...
FIGURE 8-10: Your final query pulled from the internet: transformed, put into a...
FIGURE 8-11: You can view and manage query steps in the Applied Steps section o...
FIGURE 8-12: Right-click on any query step to edit, rename, delete, or move the...
FIGURE 8-13: Right-click any query in the Queries & Connections pane to see the...
FIGURE 8-14: Click the Table Actions icon in the upper-left corner of the Query...

Chapter 9

FIGURE 9-1: Select the data sources you want to work with, and then click the L...
FIGURE 9-2: Preview the data and use the option drop-down menus to tell Power Q...
FIGURE 9-3: The available tables and pages in the PDF are shown in the Navigato...
FIGURE 9-4: Data preview of the files in the target folder.
FIGURE 9-5: Use the Power Query Editor to add more file attributes to the impor...
FIGURE 9-6: Power Query offers connection types for many of the popular databas...
FIGURE 9-7: Tools for connection to Microsoft Azure cloud database services.
FIGURE 9-8: Starting an ODBC connection.
FIGURE 9-9: Select the view you want imported, and then click the Load button.
FIGURE 9-10: The final imported database data.
FIGURE 9-11: Edit a data source by selecting it and clicking the Edit Permissio...
FIGURE 9-12: Edit a data source by selecting it and clicking the Transform Data...
FIGURE 9-13: Data Profiling options are found in the Data View group under the ...
FIGURE 9-14: Choose Column Profiling Based on Entire Data Set to get a more com...
FIGURE 9-15: Exposing the quick actions for a column using the data column qual...
FIGURE 9-16: Right-clicking a column profile histogram bar exposes the quick ac...

Chapter 10

FIGURE 10-1: Does this table have duplicate records? It depends on how you defi...
FIGURE 10-2: Removing duplicate records.
FIGURE 10-3: Undo the removal of records by deleting the Removed Duplicates ste...
FIGURE 10-4: Replacing null with 0.
FIGURE 10-5: Replacing empty strings with the word Undefined.
FIGURE 10-6: Merging the Type and Code fields.
FIGURE 10-7: The Merge Columns dialog box.
FIGURE 10-8: The original columns are removed and replaced with a new, merged c...
FIGURE 10-9: Reformatting the ContactName field to proper case.
FIGURE 10-10: Replacing text values.
FIGURE 10-11: Leading spaces can cause issues in analysis.
FIGURE 10-12: The Trim command.
FIGURE 10-13: The Extract command allows you to pull out parts of the text foun...
FIGURE 10-14: Extracting the first three characters of the Phone field.
FIGURE 10-15: Extracting the two middle characters of the SicCode.
FIGURE 10-16: The Split Column command can easily split the ContactName Field i...
FIGURE 10-17: Splitting the ContactName column at every occurrence of a comma.
FIGURE 10-18: The ContactName field has been split successfully into three colu...
FIGURE 10-19: Matrix layouts are problematic for data analysis.
FIGURE 10-20: All months are now in a tabular format.
FIGURE 10-21: Use Unpivot Other Columns when the number of matrix columns is va...
FIGURE 10-22: Pivoting the Month and Value columns.
FIGURE 10-23: Confirm the aggregation operation to finalize the pivot transform...
FIGURE 10-24: The Custom Column dialog box.
FIGURE 10-25: A formula to merge the Type and Code columns.
FIGURE 10-26: Use the Data Type drop-down menu to discover and select the data ...
FIGURE 10-27: A formula to create a consistent ten-digit padded CompanyNumber.
FIGURE 10-28: Applying an IF statement in a custom column.
FIGURE 10-29: Using the Group By dialog box to create a view of 2021 Total Pote...
FIGURE 10-30: The resulting aggregate view by State and City.
FIGURE 10-31: Creating a custom data type.
FIGURE 10-32: Each value in a data type column contains the data for underlying...
FIGURE 10-33: Data types have a special icon next to each value and allow you t...
FIGURE 10-34: Referencing a data type value and entering the dot (.) operator a...

Chapter 11

FIGURE 11-1: This data can be used as the source for various levels of aggregat...
FIGURE 11-2: Merge the Last_Name and First_Name columns to create a new Employe...
FIGURE 11-3: Group the Employee field and Sum Sales Amount to create a new Reve...
FIGURE 11-4: All the query steps before Grouped Rows are needed in order to pre...
FIGURE 11-5: Naming the new query SalesByBusiness.
FIGURE 11-6: The two queries are now sharing the extracted steps.
FIGURE 11-7: The data found on each region tab needs to be consolidated into on...
FIGURE 11-8: Create a connection-only query for each region.
FIGURE 11-9: Appending multiple queries to NorthData.
FIGURE 11-10: The final output.
FIGURE 11-11: The kinds of joins supported by Power Query.
FIGURE 11-12: You need to merge the Questions and Answers queries into one tabl...
FIGURE 11-13: Activating the Merge dialog box.
FIGURE 11-14: The Completed Merge dialog box.
FIGURE 11-15: Expand the NewColumn field and choose the merged fields you want ...
FIGURE 11-16: The final table with merged questions and answers.
FIGURE 11-17: Right-click the Source query step and select Edit Settings to rea...
FIGURE 11-18: The Merge dialog box with Fuzzy Matching selected.

Chapter 12

FIGURE 12-1: Enter your custom code in the Advanced Editor window.
FIGURE 12-2: Give your custom function a friendly name.
FIGURE 12-3: A text file containing Invoice details.
FIGURE 12-4: Use the Custom Column action to invoke your function.
FIGURE 12-5: The custom column showing the results of the function for each row...
FIGURE 12-6: You need to merge into one table the data in all the Excel files i...
FIGURE 12-7: Connect to one of the Excel files in the target folder, and naviga...
FIGURE 12-8: Use the Query Editor to apply any necessary transformation actions...
FIGURE 12-9: Open the Advanced Editor to see the starter code.
FIGURE 12-10: Wrapping the starter code with function tags and replacing the ha...
FIGURE 12-11: The Query Editor after defining parameters.
FIGURE 12-12: Create a new query using the From Folder connection type to retri...
FIGURE 12-13: Use the Custom Column action to invoke the function.
FIGURE 12-14: Power Query triggers the function and returns a table array for e...
FIGURE 12-15: Click the Custom column header to expand the table arrays.
FIGURE 12-16: Power Query exposes the columns pulled from each Excel file and a...
FIGURE 12-17: Confirming that the parameters in the URL actually work.
FIGURE 12-18: The clean base query.
FIGURE 12-19: Wrapping the starter code with function tags and specifying a Yea...
FIGURE 12-20: Create a simple parameter table.
FIGURE 12-21: Use the Custom Column action to invoke the function.
FIGURE 12-22: The combining of Excel and Web data triggers Power Query to ask a...
FIGURE 12-23: Choose to load the final query results under the parameters table...
FIGURE 12-24: The final parameter query provides an interactive mechanism to fl...

Chapter 13

FIGURE 13-1: A star schema is the most efficient data model, with a single fact...
FIGURE 13-2: Snowflake schemas are less efficient, causing Power Pivot to perfo...
FIGURE 13-3: Slicers work together to show relevant data items based on a selec...
FIGURE 13-4: Deselecting the Visually Indicate Items option with No Data disabl...

Chapter 14

FIGURE 14-1: Hover the cursor over a query to get quick information, including ...
FIGURE 14-2: Queries can be organized into groups.
FIGURE 14-3: Use the Choose Columns command to find and select columns faster.
FIGURE 14-4: Get in the habit of renaming applied steps.
FIGURE 14-5: Create a new query from an existing column.
FIGURE 14-6: The Query Dependencies dialog box displays how each of your querie...
FIGURE 14-7: Use the Global Data Load options to set a default load behavior.
FIGURE 14-8: Power Query automatically adds a step to change data types when da...
FIGURE 14-9: Disabling the type detection feature.
FIGURE 14-10: Disabling the privacy-level settings.
FIGURE 14-11: Disabling relationship detection.

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 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 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.

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

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.

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.

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.

Beyond 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 Sheet in 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.

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

Part 1

Supercharged Reporting with Power Pivot

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.