Excel Power Pivot & Power Query For Dummies

Оглавление

Michael Alexander. Excel Power Pivot & Power Query For Dummies

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

List of Tables

List of Illustrations

Guide

Pages

Introduction

About This Book

Foolish Assumptions

Icons Used in This Book

Beyond the Book

Where to Go from Here

Supercharged Reporting with Power Pivot

Thinking Like a Database

Exploring the Limits of Excel and How Databases Help

Scalability

Transparency of analytical processes

Separation of data and presentation

Getting to Know Database Terminology

Databases

Tables

Records, fields, and values

Queries

Understanding Relationships

Introducing Power Pivot

Understanding the Power Pivot Internal Data Model

WHERE’S THE POWER PIVOT TAB?

A WORD ON COMPATIBILITY

Linking Excel Tables to Power Pivot

Preparing Excel tables

Adding Excel Tables to the data model

Creating relationships between Power Pivot tables

Managing existing relationships

Using the Power Pivot data model in reporting

The Pivotal Pivot Table

Introducing the Pivot Table

Defining the Four Areas of a Pivot Table

Values area

Row area

Column area

Filter area

Creating Your First Pivot Table

Changing and rearranging a pivot table

Adding a report filter

Keeping the pivot table fresh

Customizing Pivot Table Reports

Changing the pivot table layout

Customizing field names

Applying numeric formats to data fields

Changing summary calculations

Suppressing subtotals

Removing all subtotals at one time

Removing the subtotals for only one field

Removing grand totals

Showing and hiding data items

Hiding or showing items without data

Sorting the pivot table

Understanding Slicers

Creating a Standard Slicer

Getting Fancy with Slicer Customizations

Size and placement

Data item columns

Miscellaneous slicer settings

Controlling Multiple Pivot Tables with One Slicer

Creating a Timeline Slicer

Using External Data with Power Pivot

Loading Data from Relational Databases

Loading data from SQL Server

IMPORTING TABLES VERSUS IMPORTING VIEWS

Loading data from Microsoft Access databases

Loading data from other relational database systems

Loading Data from Flat Files

Loading data from external Excel files

Loading data from text files

Loading data from the Clipboard

Loading Data from Other Data Sources

Refreshing and Managing External Data Connections

Manually refreshing Power Pivot data

Setting up automatic refreshing

Preventing Refresh All

Editing the data connection

Working Directly with the Internal Data Model

Directly Feeding the Internal Data Model

THE LIMITATIONS OF POWER PIVOT-DRIVEN PIVOT TABLES

Managing Relationships in the Internal Data Model

Managing Queries and Connections

Creating a New Pivot Table Using the Internal Data Model

Filling the Internal Data Model with Multiple External Data Tables

Adding Formulas to Power Pivot

Enhancing Power Pivot Data with Calculated Columns

Creating your first calculated column

Formatting calculated columns

Referencing calculated columns in other calculations

Hiding calculated columns from end users

Utilizing DAX to Create Calculated Columns

Identifying DAX functions that are safe for calculated columns

Building DAX-driven calculated columns

Month sorting in Power Pivot–driven pivot tables

Referencing fields from other tables

Nesting functions

Understanding Calculated Measures

Creating a calculated measure

Editing and deleting calculated measures

Free Your Data with Cube Functions

Diving into DAX

DAX Language Fundamentals

Using DAX operators

Applying conditional logic in DAX

Working with DAX aggregate functions

Exploring iterator functions and row context

Understanding Filter Context

Getting context transitions with the CALCULATE function

Adding flexibility with the FILTER function

WHERE TO GO FROM HERE

Wrangling Data with Power Query

Introducing Power Query

Power Query Basics

Starting the query

Understanding query steps

VIEWING THE ADVANCED QUERY EDITOR

Refreshing Power Query data

Managing existing queries

Understanding Column-Level Actions

Understanding Table Actions

Power Query Connection Types

Importing Data from Files

Getting data from Excel workbooks

Getting data from CSV and text files

Getting data from PDF files

Getting data from folders

Importing Data from Database Systems

A connection for every database type

Relational and OLAP databases

Azure databases

ODBC connections to nonstandard databases

Getting data from other data systems

Walk-through: Getting data from a database

Managing Data Source Settings

Data Profiling with Power Query

Data Profiling options

Data Profiling quick actions

Transforming Your Way to Better Data

Completing Common Transformation Tasks

Removing duplicate records

Filling in blank fields

Replacing null values

Filling in empty strings

Concatenating columns

Changing case

Finding and replacing specific text

Trimming and cleaning text

Extracting the left, right, and middle values

Extracting first and last characters

Extracting middle characters

Splitting columns using character markers

Pivoting and unpivoting fields

Unpivot Columns command

Unpivot Other Columns command

Pivot Columns command

Creating Custom Columns

Concatenating with a custom column

Understanding data type conversions

Spicing up custom columns with functions

Adding conditional logic to custom columns

Grouping and Aggregating Data

Working with Custom Data Types

Making Queries Work Together

Reusing Query Steps

Understanding the Append Feature

Creating the needed base queries

Appending the data

BEWARE OF MISMATCHED COLUMN LABELS

Understanding the Merge Feature

Understanding Power Query joins

Merging queries

Understanding Fuzzy Match

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

Preparing for a parameter query

THE IMPORTANCE OF KNOWING YOUR PARAMETERS

Creating the base query

Creating the parameter query

The Part of Tens

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 Many Unique 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

Ten Tips for Working with Power Query

Getting Quick Information from the Queries & Connections Pane

Organizing Queries in Groups

Selecting Columns in Queries Faster

Renaming Query Steps

Quickly Creating Reference Tables

Viewing Query Dependencies

Setting a Default Load Behavior

Preventing Automatic Data Type Changes

Disabling Privacy Settings to Improve Performance

Disabling Relationship Detection

Index. Symbols

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

About the Author

Author’s Acknowledgments

WILEY END USER LICENSE AGREEMENT

Отрывок из книги

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:

.....

Databases aren’t only for computers. Manual databases are sometimes referred to as manual filing systems or manual database systems. These filing systems usually consist of people, papers, folders, and filing cabinets — paper is the key to a manual database system. In a real-life manual database system, you probably have in-baskets and out-baskets and some type of formal filing method. You access information manually by opening a file cabinet, removing a file folder, and finding the correct piece of paper. Customers fill out paper forms for input, perhaps by using a keyboard to input information that is printed on forms. You find information by manually sorting the papers or by copying information from many papers to another piece of paper (or even into an Excel spreadsheet). You may use a spreadsheet or calculator to analyze the data or display it in new and interesting ways.

A database stores information in a carefully defined structure known as a table. A table is just a container for raw information (called data), similar to a folder in a manual filing system. Each table in a database contains information about a single entity, such as a person or product, and the data in the table is organized into rows and columns. A relational database system stores data in related tables. For example, a table containing employee data (names and addresses) may be related to a table containing payroll information (pay date, pay amount, and check number).

.....