Index

Symbols

% (percent), comparing lines with, 71

= (equal sign), 292

3D Map, 245

bubble/pie charts, creating, 266

case studies, 271-273

column charts, formatting, 264

data over time, animating, 269

geocoding data, 262-263

geographic data, analyzing, 261-273

heat maps/region maps, applying, 266

labeling points, 266

navigating, 264-265

optimizing, 267-269

preparing data for, 261

tours, building, 270-271

videos, sharing, 271

A

Access (Microsoft), formatting pivot tables, 169-171

accessing functions, OLAP, 211-213

accounts, saving to OneDrive, 195

actions

column-level, 185

tables, 186-188

activating classic layouts, 30

ActiveX controls, 279

activity by market, analysis of, 38

Add This Data to the Data Model option, 25

add-ins

Pivot Power Premium, 47

Power Pivot, 225

applying, 234-238

defining relationships, 236

enabling, 234

importing text, 235-236

linking files, 236

AddFields method, 298

adding

calculated columns using DAX language, 237

calculated fields to data sources, 114

calculated items, 126

calculations to OLAP, 213-215, 222

data to Power BI, 197-198

fields, 251

creating pivot tables with VBA, 298-299

filters areas, 104

to reports, 26-28

Horizontal Multiples, 256

layers, 28

numeric fields to Values areas, 348-349

pages to sectors, 107

paths, 246

rank number fields, 359-360

regions, 27

slicers, 33

subtotals, 64-67, 80, 315

tables to Data Models, 163-165, 228, 346

thousands separators, 48-49

Timeline slicers, 36

Advanced Editor dialog box, 182

advantages of pivot tables, 11

analysis, 11, 157

Access (Microsoft), formatting, 169-171

activity by market, 38

calculations, 113

See also calculations

cleaning up data for, 23-24

Data Models

adding tables, 163-165

deleting tables, 165

formatting, 158-162, 166

limitations of internal, 167

managing relationships, 162-163

data sets, selecting, 25

geographic data with 3D Map, 261-273

OLAP what-if-analysis, executing, 220-222

Power Query

applying, 174-192

connecting, 188-192

formatting columns, 185

formatting tables, 186-188

managing, 183-185

overview of, 175-180

refreshing, 183

steps, 181-182

Quick Analysis option, 32

SQL Servers, formatting, 171-174

Analysis Services

options, 204

Tutorial cube, 205

animating scatter charts, 259-260

applying

AutoFilter, 363

automatic grouping, 77-85

AutoShow, 329-332

AutoSort, 312

calculated fields, 323-324

calculated items, 324-325

cells, references, 129

Compact layout, 52-53

conceptual filters, 339-340

conditional formatting, 149-153

constants, 129

Data Model, 345-351

Date Filters, 103-104

DAX language, 224, 239-243

drop-down lists, Areas section, 88-89

fields to lists, 83-89

filters areas, 104-112

functions, 64

heat/region maps, 3D Map, 266

Index function, 74

macros, 275-276

manual sequences to sorting, 92-93

named ranges, 129

object variables, 294-295

Outline layout, 53-54

pivot charts, 135-136, 145-148

pivot tables, 10-12

Power Pivot add-in, 234-238

Power Query, 174-192

restrictions, 368-371

search filters, 342-343

slicers, 33-38, 107-109

styles, restoring gridlines, 47

Tabular layout, 55

time intelligence, 242-243

timelines, 107-110

Top 10 filter, 101-102

Visual Basic Editor, 291

worksheet functions, 129

areas

Columns, 12, 14, 27

Filters, 12, 14, 27

navigating, 12

Rows, 12-13, 27

Values, 12-13, 27

Areas section, drop-down lists, 88-89

assigning macros, 279

authentication (Windows Authentication), 172

AutoComplete, 292

AutoFilter, 363

automatic grouping

applying, 77-85

undoing, 78

AutoShow, applying, 329-332

AutoSort, applying, 312

avoiding

GetPivotData, 376-380

groups, repeating, 21

section headings, storing, 20

B

backward compatibility, 15-17, 38

benefits of Data Models, 224-225

See also limitations

blank cells, 23

counts, causing, 63-64

Data areas, filling, 301

data sources, deleting, 22

eliminating, 311-312

Row areas, filling, 302

zeros, replacing, 49-50

BLANK function, 239

Blank Rows setting, 57

breaks (page), adding subtotals, 315

bubble charts, 141, 266

building. See formatting

buttons, fields, 138-139

C

caches, 15

defining, 347

overwriting, 39

sharing, 40-41

slicers, defining, 344

calculated columns (DAX)

adding, 237

applying calculations for, 239

calculated fields

applying, 323-324

creating, 116-123

data sources, adding to, 114

DAX language

defining, 240-241

filtering, 240

formulas, creating with, 115

inserting, 116

naming, 118

rules, 129

summaries, 120-123

calculated items

adding, 126

applying, 324-325

formatting, 124-127

naming, 125

rules, 131

solve order, modifying, 132

calculations, 10, 113

configuring, 67-75

customizing, 116

DAX language, applying, 239-243

deleting, 131

editing, 131

fields, 114

See also calculated fields

groups, 325-327

items, 124

See also calculated items

managing, 131-133

measures, formatting, 214-217

members, formatting, 217-219

OLAP (online analytical processing)

adding, 213-222

managing, 220

overview of, 113-114

percentages of parent items, 73

pivot tables (VBA), 319-329

Power View, modifying, 259

rules, 127-131

Show Values As tab, 327-329

Sum function, 321-322

summary calculations, modifying, 63-64

values areas, 12-13

Caption property, 322

case studies

3D Map, 271-273

analysis

activity by market, 38

cleaning up data for, 23-24

calculated fields, summaries, 120-123

Data Models, applying, 231-232

pivot charts

interactive reports, 142-144

synchronizing, 283-287

Power Query, transposing data sets, 190-192

text, grouping, 82-85

values, converting to, 55-57

categories, viewing, 307-319

categorizing fields, 245

causing counts, 63-64

cells

blank. See blank cells

deleting, 22

inserting, 302

pivot charts, applying as source data, 147-148

preventing errors, 302

references, applying, 129

Change PivotTable Data Source dialog box, 40

charts

3D Maps, creating, 266

columns, formatting, 264

filtering, 255

overview of, 136

See also pivot charts

Power View

adding drill-down to, 251-252

copying, 256

filtering, 252

scatter

animating, 259-260

XY, 141

titles, 141

check boxes, filtering, 96-97

CHOOSE function. See SWITCH function

classic layouts, activating, 30

See also layouts

cleaning up data for analysis, 23-24

closing GetPivotData, 379-380

code

Data Models, generating, 349

object-oriented, 292-293

pivot tables, generating, 300

reports, generating, 319

summaries, generating, 305

VBA (Visual Basic for Applications)

applying object variables, 294-295

editing, 282

shortening, 295

sizing data ranges, 293-294

Code window, 291

Collapse Entire Field (+) button, 138

Collapse Entire Field (-) button, 138

colors, selecting, 63

columns

areas, 12-14, 27

automatic grouping, applying, 77-85

charts, formatting, 264

data sources, expanding, 39

filtering, 96-104

formatting, 246

grand totals, viewing, 58

groups, avoiding repeating, 21

moving, 92

Off for Rows and Columns setting, 59

Power Query, formatting, 185

tabular layouts, 20

commands

Distinct Count, 233

From Web, 177

List Formulas, 133

MDX Calculated Measure, 215

MDX Calculated Member, 218

Query Settings, 181

Share with People, 196

Summarize Values By, 64

comments, 292

Compact form, 52-53, 86

Compact layout, 299

comparisons

calculated items, 124

lines with % (percent), 71

pivot tables/Power Pivot add-in, 238-239

tables, 361

Compatibility Checker, 16

Compatibility mode, 16

compatibility, backward. See backward compatibility

conceptual filters, applying, 339-340

conditional formatting

applying, 153

customizing, 152-156

pivot charts, applying, 149-151

configuring

See also formatting

calculations, 67-75

conditional formatting, 151

Power Query, 175-180

slicers, 343-345

connecting

Data Connection Wizard, 172

databases, 171

Existing Connections dialog box, 167

OLAP (online analytical processing), 204-206

Power Query, 188-192

slicers, 35

Workbook Connections dialog box, 164

constants, applying, 129

controlling

sort order, 312

totals, 302-304

controls

ActiveX, 279

forms

formatting interfaces with, 278-280

inserting scrollbars, 281

Convert option, 16

Convert to Formulas option, 212

converting

pivot tables to values, 55-57, 304-305

ranges, 163

tables to charts (Power View), 250-251

copying

charts (Power View), 256

pivot tables to workbooks, 314-315

reports, filtering, 105-107

Count Distinct option, 224

counting

causing, 63-64

records, 322

Create Cube File wizard, 210

Create PivotChart dialog box, 139

Create PivotTable dialog box, 25, 159, 166, 228

Create Table dialog box, 163

CreatePivotTable method, 297

cubes

Analysis Services Tutorial, 205

functions, navigating, 212-213

naming, 211

OLAP (online analytical processing)

accessing functions, 211-213

adding calculations, 213-222

connecting, 204-206

creating offline cubes, 209-211

limitations of, 208-209

navigating, 207-208

Custom Lists dialog box, 93

Custom Name dialog box, 51

customers, sorting, 89-92

customizing

calculations, 116

conditional formatting, 152-156

lists, sorting, 93-94

macros, 280-288, 290

pivot tables, 11, 45

adding/removing subtotals, 64-67

applying functions, 64

Compact layout, 52-53

configuring calculations, 67-75

formatting numbers, 48-49

modifying, 46-51

naming fields, 51

Outline layout, 54-55

replacing blanks with zeros, 49-50

report layouts, 52-60

restoring gridlines, 47

styles, 60-63

summary calculations, 63-64

Tabular layout, 55

toggling elements, 56-60

D

dashboards, 245

3D Map

analyzing geographic data with, 261-273

animating data over time, 269

applying heat maps/region maps, 266

building tours, 270-271

creating bubble/pie charts, 266

formatting column charts, 264

geocoding data, 262-263

labeling points, 266

navigating, 264-265

optimizing, 267-269

preparing data for, 261

sharing videos, 271

formatting, 196

Map, 257

Power View

adding drill-down to charts, 251-252

animating scatter charts, 259-260

applying tile boxes, 255

converting tables to charts, 250-251

copying charts, 256

creating slicers, 253

filtering charts, 252

formatting, 247-255

modifying calculations, 259

navigating Filters pane, 254-255

overview of, 261

populating with images, 258

preparing data, 245-247

sorting, 249

starting elements, 252

sharing, 202

Data areas, filling blank cells, 301

Data Connection Wizard, 172, 204

data mining, 203

See also OLAP

Data Models, 158-167, 223

applying, 345-351

benefits of, 224-225

code, generating, 349

DAX language, applying, 224

formatting, 158-162

limitations of, 225-226

multiple tables

joining, 226-233

merging, 223

navigating, 223-226

pivot tables, formatting, 232-233

relationships

defining, 229-230

managing, 162-163

tables

adding, 163-165, 228, 346

deleting, 165

formatting, 166

unlimited rows, importing, 224

data over time, animating 3D Map, 269

data ranges, expanding, 361

data sets, 9

exploding, 367-368

selecting, 25

data sources

analysis, 157

blank cells, deleting, 22

calculated fields, adding to, 114

design, 22

modifying, updating, 39-40

pivot tables, formatting, 168-174

databases

connecting, 171

OLAP (online analytical processing)

accessing functions, 211-213

adding calculations, 213-222

connecting, 204-206

creating offline cubes, 209-211

limitations of, 208-209

navigating, 207-208

overview of, 203-204

pivot tables, formatting, 169-171

tabular layouts, 20

transactional, 203

Date Filters, applying, 103-104

dates

grouping, 77-85

selecting, 37

weeks, grouping, 81

DAX language

applying, 224

calculated columns

adding, 237

applying calculations for, 239

calculated fields

defining, 240-241

filtering, 240

calculations, applying, 239-243

days

filtering, 37

grouping, 79

debugging, 292

default number formats, modifying, 312-313

default pivot tables, 46

See also pivot tables

default sorting, overriding, 89

Defer Layout Update option, 41-42, 297

deferring layout updates, 41-42

defining

caches, 347

calculated fields (DAX), 240-241

custom lists, 94

pivot tables, 9

relationships

in Data Models, 229-230

Power Pivot add-in, 236

deleting

blank cells, 22

calculations, 131

cells, preventing errors, 302

connections, 190

pivot tables, 146

subtotals, 64-67

tables, 165

descriptions of macros, 276

design

See also formatting

data sources, 22

workbooks as web pages, 193-196

Design tab, PivotTable Styles gallery, 60

dialog boxes

Advanced Editor, 182

Change PivotTable Data Source, 40

Create PivotChart, 139

Create PivotTable, 25, 159, 166, 228

Create Table, 163

Custom Lists, 93

Custom Name, 51

Excel Options, 78, 276

Existing Connections, 167

Field Settings, 84, 89

Format Cells, 49

Format Object, 281

Go To Special, 357

Grouping, 79-81

Import Data, 170, 206

Insert Calculated Field, 117, 120

Insert Calculated Item, 124

Insert Slicer, 34

Insert Timelines, 36, 143

Load To, 180

Macro, 286

Manage Relationships, 163

Modify PivotTable Quick Style, 62

More Sort Options, 91

Move Chart, 137

Move PivotTable, 43

New Calculated Measure, 216

New Calculated Member, 218

New Formatting Rule, 153

PivotTable Options, 209

Record Macro, 276

Report Connections, 35

Select Table, 170

Share, 196

Show Detail, 53

Show Report Filter Pages, 106

Solve Order, 132

Sort (Sector), 91

Value Field Settings, 48, 67, 233

Workbook Connections, 164

dimensions (OLAP), 207

Distinct Count command, 233

distributions, frequency, 82

docking fields, 87

documents

formulas, 133

themes, modifying, 62-63

dragging fields, 30

See also moving

drill-down, adding to charts, 251-252

driving multiple pivot tables, slicers, 110-112

drop-down lists, Areas section, 88-89

E

editing

calculations, 131

VBA code, 282

Visual Basic Editor, 291

elements

Power View, starting, 252

resizing, 251

tables, starting, 249

toggling, 56-60

eliminating blank cells, 311-312

empty cells, filling, 356-358

enabling

macros, saving workbooks, 290

Power Pivot add-in, 234

VBA (Visual Basic for Applications), 289-290

End key, 294

End With statements, 295

entering

formulas, 115

GetPivotData, preventing, 379

Entire Field (+) button, 138

equal sign (=), 292

errors, preventing, 302

ETL processes, 174

Excel Help, 292

Excel Options dialog box, 78, 276

Excel Pro Plus (Power Pivot add-in), 225, 234-238

executing

calculations, 113

See also calculations

OLAP what-if-analysis, 220-222

executive overviews, formatting, 329-332

Existing Connections dialog box, 167

Expand Entire Field (+) button, 138

expanding

data ranges, 361

ranges, 37

exploding workbooks to pivot tables, 372-373

expressions, MDX (multidimensional expressions), 214

external data sources, formatting, 157, 168-174

See also data sources

F

Field Settings dialog box, 84, 89

fields

See also columns; rows

adding, 251, 298-299

buttons, pivot charts, 138-139

calculated. See calculated fields

categorizing, 245

filtering, 96-104

filters areas, adding, 104

formatting, 22

formulas, converting to, 213

lists

applying, 83-89

docking/undocking, 87

moving, 87

models, adding to pivot tables, 348

modifying, viewing, 73

moving, 29

multiple, troubleshooting, 319-321

naming, 51, 121

numeric, grouping, 82

pivot charts, moving, 139-141

PivotTable Fields list

searching, 26

viewing, 208

rank number, adding, 359-360

Region, adding, 27

reports, adding to, 26-28

restrictions, applying, 368-371

rows

filling empty cells left by, 356-358

suppressing subtotals, 313-314

subtotals, adding, 67, 80

values, modifying calculations, 67-75

files

formatting, 14

importing, 197

linking, 236

naming, 211

filling

blank cells

Data areas, 301

Row areas, 302

empty cells, 356-358

ranges, 23

filtering, 77, 95

AutoFilter, 363

calculated fields (DAX), 240

charts

applying tile boxes (Power View), 255

Power View, 252

check boxes, 96-97

columns, 96-104

conceptual filters, 339-340

Date Filters, 103-104

fields

applying lists, 83-89

converting to formulas, 213

items

in grand totals, 225

selecting, 105

labels, 98-99

multiple items, 105

pivot tables, 338-339

recordsets, 332-334

reports

copying, 105-107

formatting, 31

rows, 96-104

search boxes, 97-98

search filters, applying, 342-343

slicers, 37, 107-109

applying, 34

configuring, 343-345

timelines, applying, 107-110

Top 10 filter, applying, 101-102

Values columns, 99-101

filters areas, 12, 14, 27

applying, 104-112

fields, adding, 104

Filters pane, navigating Power View, 254-255

fonts, 62

See also themes

Format Cells dialog box, 49

Format Object dialog box, 281

formatting

3D Map charts, 266

calculated fields, 116-123

calculated items, 124-127

calculations

measures, 214-217

members, 217-219

columns, 246

charts, 264

Power Query, 185

dashboards, 196

Data Models, 158-162

fields, 22

files, 14

formulas, 115

frequency distributions, 82, 366-367

interfaces with form controls, 278-280

numbers, 48-49, 312-313, 364-365

pivot charts, 136-139

applying conditional, 149-151

as images, 146

customizing conditional, 152-156

limitations, 141

reports, 142-144

pivot tables, 14-15, 117

creating, 24-31

Data Model, 232-233

GetPivotData, 381-391

Power Pivot add-in, 237

preparing data for reporting, 19-24

SQL Servers, 171-174

summaries, 355-356

VBA (Visual Basic for Applications), 296-301

with external data sources, 168-174

Power View, 247-255

reports

filters, 31

models, 334-338

Power BI Desktop, 199-200

regions, 334-338

viewing by categories, 307-319

shell reports, 385-387

slicers

Power View, 253

timelines, 36-38

subtotals, 315

tables

Data Model, 166

Power Query, 186-188

relationships, 346-347

views, 9

web pages, sharing pivot tables, 193-196

forms

Compact, 52-53, 86

controls

formatting interfaces with, 278-280

inserting scrollbars, 281

formulas, 11

calculated fields, creating with, 115

Convert to Formulas option, 212

documenting, 133

entering, 115

GetPivotData, preventing, 379

troubleshooting, 377

frames, resizing elements, 251

frequency distributions, 82, 366-367

From Web command, 177

functions, 11

applying, 64

BLANK, 239

CHOOSE. See SWITCH function

cubes, navigating, 212-213

GETPIVOTDATA, 225

INDEX, 285

Index, 74

OLAP (online analytical processing), accessing, 211-213

Sum, 321-322

SUMIFS, 239

SWITCH, 240

TEXT, 239

VLOOKUP, 239

worksheets, applying, 129

G

generating code

Data Models, 349

pivot tables, 300

reports, 319

summaries, 305

geocoding data, 262-263

GetPivotData, 375-376

avoiding, 376-380

closing, 379-380

pivot tables, formatting, 381-391

preventing, 379

GETPIVOTDATA function, 225

Go To Special dialog box, 357

grand totals

items, filtering, 225

subtotals, suppressing, 64-67

viewing, 58

gridlines, restoring, 47

grouping, 77

automatic, undoing, 78

calculating, 325-327

dates, 77-85

selecting, 78

weeks, 81

Layout, 52

months, 80

numeric fields, 82

repeating, avoiding, 21

text, 82-85

years, 80

Grouping dialog box, 79-81

H

hard data, formatting, 355-356

See also summaries

hard values, modifying, 145

heat maps, 3D Map, 266

help (Excel Help), 292

hierarchies (OLAP), 207

hours, grouping, 79

How Many Customers to Show slicer, 194

I

images

pivot charts, formatting as, 146

Power View, populating with, 258

Import Data dialog box, 170, 206

importing

data to Power BI, 197-198

text (Power Pivot add-in), 235-236

unlimited rows with Data Models, 224

INDEX function, 285

Index function, applying, 74

Insert Calculated Field dialog box, 117, 120

Insert Calculated Item dialog box, 124

Insert Slicer dialog box, 34

Insert Timelines dialog box, 36, 143

inserting

calculated fields, 116

cells, preventing errors, 302

scrollbars, 281

slicers, 33

timeline slicers, 36, 143

intelligence, time, 242-243

IntelliSense, 298

interactive reports, pivot charts, 142-144

interfaces, formatting, 278-280

internal Data Models, 158-167

items

calculated, applying, 324-325

calculations, 124

See also calculated items

copying, 105-107

grand totals, filtering, 225

percentages, tracking, 73

selecting, filtering, 105

sorting in unique orders, 355

specifying sorting, 91

J

joining multiple tables with Data Models, 226-233

K

keys

End, 294

primary, 162

L

Label Filters option, 98-99

labels

filtering, 98-99

points (3D Map), 266

repeating, 53

languages

DAX, 224

See also DAX language

VBA. See VBA (Visual Basic for Applications)

layers, adding, 28

Layout group, 52

layouts

See also formatting

classic, starting, 30

Compact, applying, 52-53

Outline, applying, 54-55

pivot tables, 299-301

report, modifying, 52-60

reports, 27-28

Tabular, 20, 55

troubleshooting, 33

updating

deferring, 41-42

levels (OLAP), 207

limitations

of Data Model, 225-226

of internal Data Models, 167

of OLAP (online analytical processing), 208-209

of VLOOKUP, 230

pivot charts, formatting, 141

pivot tables, 15, 301-307

lines

% (percent), comparing with, 71

trends, 141

linking

files (Power Pivot add-in), 236

Web workbooks, 196

List Formulas command, 133

lists

drop-down, applying, 88-89

fields

applying, 83-89

docking/undocking, 87

moving, 87

PivotTable Fields

searching, 26

viewing, 208

sorting, customizing, 93-94

Load button, 176

Load To dialog box, 180

locations

pivot charts, moving, 137

reports, 25

M

Macro dialog box, 286

macros, 275

applying, 275-276

assigning, 279

customizing, 280-288

enabling, saving workbooks, 290

forms, creating interfaces with controls, 278-280

naming, 276

pivot charts, synchronizing, 283-287

recorders, 292

recording, 276-278

scrollbars, inserting, 281

security, 277-278

shortcuts, 276

storing, 276

VBA. See VBA (Visual Basic for Applications)

maintenance, calculations, 131-133

Manage Relationships dialog box, 163

management

caches, sharing, 40-41

calculations, 131-133

OLAP (online analytical processing), 220

Power Query, 183-185

query steps, 182

relationships, 162-163

manual sequences, sorting, 92-93

ManualUpdate property, 297

Map, 257

maps. See 3D Map

MDX (multidimensional expressions), 214

MDX Calculated Measure command, 215

MDX Calculated Member command, 218

measures

calculations, formatting, 214-217

OLAP (online analytical processing), 208

members

calculations, formatting, 217-219

OLAP (online analytical processing), 207

memory, 14

merging multiple tables with Data Models, 223

methods

AddFields, 298

CreatePivotTable, 297

Microsoft Access, formatting, 169-171

minutes, grouping, 79

models

Data Models. See Data Models

fields, adding to pivot tables, 348

internal Data Model, 158-167

reports, formatting, 334-338

modes, Compatibility, 16

Modify PivotTable Quick Style dialog box, 62

modifying

calculations (Power View), 259

data sources, updating, 39-40

default number formats, 312-313

document themes, 62-63

fields

naming, 51

viewing, 73

macros, 280-288

pivot tables

customizing, 46-51

hard values 145

modifying pivot charts, 139

relationships, 162

report layouts, 52-60

styles, 62-63

summary calculations, 63-64

months

filtering, 37

grouping, 79, 80

More Sort Options dialog box, 91

Move Chart dialog box, 137

Move PivotTable dialog box, 43

moving

columns, 92

fields, 29

lists, 87

pivot charts, 139-141

pivot tables, 43

multidimensional expressions. See MDX

multiple fields, troubleshooting, 319-321

multiple items, filtering, 105

multiple pivot tables, driving slicers, 110-112

multiple ranges, 157

See also data sources

multiple row fields, suppressing subtotals, 313-314

multiple subtotals, adding fields, 67

multiple tables (Data Model)

joining, 226-233

merging, 223

N

Name property, 322

named ranges, applying, 129

named sets, 225

naming

calculated fields, 118

calculated items, 125

cubes, 211

fields, 51, 121

macros, 276

pivot tables, 36

navigating

3D Map, 264-265

cubes, functions, 212-213

Data Model, 223-226

End keys, 294

Filters pane (Power View), 254-255

OLAP (online analytical processing), 207-208

pivot tables, 12-14

Recommended Pivot Table feature, 31-33

tools, new features, 41-43

Navigator pane, 176

New Calculated Measure dialog box, 216

New Calculated Member dialog box, 218

new features, 2

tools, 41-43

New Formatting Rule dialog box, 153

numbers

default, modifying, 312-313

formatting, 48-49, 364-365

numeric fields

grouping, 82

Values area, adding to, 348-349

O

object-oriented code, 292-293

objects

PivotLayout, 136

variables, applying, 294-295

Off for Rows and Columns setting, 59

offline cubes, creating, 209-211

OLAP (online analytical processing)

calculations

adding, 213-222

managing, 220

connecting, 204-206

functions, accessing, 211-213

limitations of, 208-209

navigating, 207-208

offline cubes, creating, 209-211

overview of, 203-204

what-if-analysis, executing, 220-222

One Click, 42

OneDrive

files, importing from, 198

saving to, 195

online analytical processing. See OLAP

operator precedence, 128

optimizing

3D Map, 267-269

pivot tables, 11, 329-345

options

Add This Data to the Data Model, 25

Analysis Services, 204

Convert, 16

Convert to Formulas, 212

Count Distinct, 224

Defer Layout Update, 41-42

Index, 74

Label Filters, 98-99

macros, 280-288, 290

Quick Analysis, 32

Repeat All Item Labels, 53, 357

Summarize Values By command, 64

Value Field Settings dialog box, 67

order

solve, modifying calculated items, 132

sort

controlling, 312

formatting, 90

See also sorting

Outline layout, 54-55, 299

overriding

Count calculations, 64

default sorting, 89

overwriting caches, 39

P

page breaks, adding subtotals, 315

Page Layout tab, 62

pages, adding sectors, 107

parent items, tracking percentages, 73

paths, adding, 246

percentages

totals, viewing, 70

tracking, 73

percentages, calculating parent items, 73

pictures. See images

pie charts, creating, 266

pivot charts

alternatives to, 145-148

conditional formatting

applying, 149-151

customizing, 152-156

fields

applying buttons, 138-139

moving, 139-141

formatting, 136-139

images, formatting as, 146

interactive reports, 142-144

limitations of formatting, 141

locations, moving, 137

overview of, 135-136

rules, 139-144

source data, applying cells as, 147-148

synchronizing, 283-287

types of, selecting, 136

viewing, 137

Pivot Power Premium add-in, 47

pivot tables

advantages of, 11

analysis, 157

applying, 10-12

AutoFilter, 363

automating, refreshing, 353

backward compatibility, 15-17

caches, defining, 347

code, generating, 300

comparing, 361

creating, 24-31

customizing, 45

adding/removing subtotals, 64-67

applying functions, 64

Compact layout, 52-53

configuring calculations, 67-75

formatting numbers, 48-49

modifying, 46-51

naming fields, 51

Outline layout, 54-55

replacing blanks with zeros, 49-50

report layouts, 52-60

restoring gridlines, 47

styles, 60-63

summary calculations, 63-64

Tabular layout, 55

toggling elements, 56-60

Data Models, formatting, 232-233

defining, 9

deleting, 146

external data sources, formatting, 168-174

filtering, 338-339

formatting, 14-15, 117

frequency distributions, formatting, 366-367

GetPivotData, formatting, 381-391

hard values, modifying, 145

layouts, 299-301

limitations, 15, 301-307

model fields, adding to, 348

moving, 43

naming, 36

numbers, formatting, 364-365

optimizing, 329-345

overview of, 12-14

Power Pivot add-in

comparing, 238-239

formatting, 237

preparing data for reporting, 19-24

rank number fields, adding, 359-360

rearranging, 29

refreshing, 276

reports, sizing, 360

restrictions, 368-371

sharing, 193

formatting web pages, 193-196

linking Web workbooks, 196

with Power BI Desktop, 196-202

simultaneously, refreshing, 354

slicers, configuring, 343-345

SQL Servers, formatting, 171-174

summaries, formatting, 355-356

tabs, exploding data sets to, 367-368

values, converting to, 55-57, 304-305

VBA (Visual Basic for Applications)

calculating, 319-329

formatting, 296-301

workbooks

copying, 314-315

exploding to, 372-373

PivotLayout objects, 136

PivotTable Fields list

searching, 26

viewing, 208

PivotTable Options dialog box, 209

PivotTable Styles gallery, 60

points, labeling (3D Maps), 266

populating

Power View images, 258

shell reports, 387-390

Position property, 298

Power BI Desktop

pivot tables, sharing, 196-202

reports, formatting, 199-200

Power Pivot add-in, 225

applying, 234-238

enabling, 234

files, linking, 236

pivot tables

comparing, 238-239

formatting, 237

relationships, defining, 236

text, importing, 235-236

Power Query

applying, 174-192

columns, formatting, 185

connecting, 188-192

managing, 183-185

overview of, 175-180

refreshing, 183

steps, 181-182

tables, formatting, 186-188

Power View, 245

calculations, modifying, 259

charts

adding drill-down to, 251-252

copying, 256

filtering, 252

elements, starting, 252

Filters pane, navigating, 254-255

formatting, 247-255

images, populating with, 258

overview of, 261

preparing data, 245-247

scatter charts, animating, 259-260

slicers, creating, 253

sorting, 249

tables, converting to charts, 250-251

tile boxes, filtering charts, 255

precalculations, 114

See also calculations

precedence, operators, 128

preparing data for reporting, 19-24

preventing

errors, 302

GetPivotData, 379

primary keys, 162

processes, ETL, 174

productivity, 11

Project Explorer, 291

properties

Caption, 322

ManualUpdate, 297

Name, 322

Position, 298

ShowDetail, 332-334

Properties window, 291

Q

Q&A, querying with, 200

quarters

filtering, 37

grouping, 79

queries

See also Power Query

Access (Microsoft), 169

Power Query, applying, 174-192

starting, 175

steps, managing, 182

with Q&A, 200

Query Editor, 177, 182

Preview pane, 187

Query Settings command, 181

Quick Analysis option, 32

R

ranges

converting, 163

data, sizing, 293-294

expanding, 37, 361

multiple, 157

named, applying, 129

selecting, 23

updating, 40

rank

number fields, adding, 359-360

viewing, 71

rearranging. See moving

Recommended Pivot Table, navigating, 31-33

Record Macro dialog box, 276

recorders, macros, 292

recording macros, 276-278

records, counting, 322

recordsets, filtering, 332-334

references

cells, applying, 129

totals, 129

refreshing

caches, 15

pivot tables, 276

automatically, 353

simultaneously, 354

Power Query, 183

Region field, adding, 27

region maps (3D Maps), 266

regions, formatting reports, 334-338

relationships

Data Models

defining, 229-230

managing, 162-163

Power Pivot add-in, defining, 236

tables, formatting, 346-347

relocating. See moving

Repeat All Item Labels option, 53, 357

repeating groups, avoiding, 21

replacing blanks with zeros, 49-50

replicating. See copying

Report Connections dialog box, 35

reports

code, generating, 319

executive overviews, formatting, 329-332

fields, adding to, 26-28

filtering, copying, 105-107

formatting

filters, 31

models, 334-338

pivot charts, 142-144

Power BI Desktop, 199-200

regions, 334-338

viewing by categories, 307-319

layouts, 27-28, 52-60

locations, 25

macros, 275

See also macros

applying, 275-276

creating interfaces with form controls, 278-280

customizing, 280-288

inserting scrollbars, 281

recording, 276-278

synchronizing pivot charts, 283-287

preparing data for, 19-24

shell. See shell reports

sizing, 360

Top 10 filter, applying, 101

resizing elements, 251

restoring gridlines, 47

restrictions, applying, 368-371

Row areas, 12-13, 27, 302

rows

Blank Rows setting, 57

data sources, expanding, 39

fields

filling empty cells left by, 356-358

suppressing subtotals, 313-314

filtering, 96-104

grand totals, viewing, 58

Off for Rows and Columns setting, 59

rules

calculated fields, 129

calculated items, 131

calculations, 127-131

New Formatting Rule dialog box, 153

pivot charts, 139-144

running macros, 276

See also macros

running totals, tracking, 72

S

saving

to OneDrive, 195

workbooks, enabling macros, 290

scatter charts, 141, 259-260

scrollbars, inserting, 281

search boxes, filtering, 97-98

search filters, applying, 342-343

searching PivotTable Fields lists, 26

section headings, avoiding storing, 20

sectors

pages, adding to, 107

sorting, 91

security, macros, 277-278

Select Table dialog box, 170

selecting

colors, 63

data sets, 25

dates, grouping, 78

items, filtering, 105

pivot charts, 136

ranges, 23

separators, adding thousands, 48-49

sequences (manual), applying to sorting, 92-93

services, Analysis Services option, 204

sets, named, 225

shaping (data), 10

See also formatting

Share dialog box, 196

Share with People command, 196

sharing

caches, 40-41

dashboards, 202

pivot tables, 193

formatting web pages, 193-196

linking Web workbooks, 196

with Power BI Desktop, 196-202

videos (3D Map), 271

shell reports

building, 385-387

populating, 387-390

updating, 390-391

shortcuts

macros, 276

See also macros

pivot tables, starting, 25

Recommended Pivot Table feature, 31-33

shortening VBA code, 295

Show Detail dialog box, 53

Show Report Filter Pages dialog box, 106

Show Values As tab, 67-69, 327-329

ShowDetail property, 332-334

sizing

data ranges, 293-294

reports, 360

slicers

applying, 33-38, 107-109

configuring, 343-345

connecting, 35

How Many Customers to Show, 194

inserting, 33

multiple pivot tables, driving, 110-112

Power View, creating, 253

timelines

creating, 36-38

inserting, 143

Solve Order dialog box, 132

solve order, modifying calculated items, 132

Sort (Sector) dialog box, 91

sorting, 77, 89-94

AutoSort, applying, 312

customers, 89-92

fields, applying lists, 83-89

items

in unique orders, 355

specifying, 91

lists, customizing, 93-94

manual sequences, applying, 92-93

Power View, 249

sectors, 91

source data, applying cells as pivot charts, 147-148

spans, filtering, 101

specifying items, sorting, 91

SQL Servers, formatting, 171-174

starting

classic layouts, 30

elements (Power View), 252

pivot tables, 24

queries, 175

Recommended Pivot Tables, 31

tables, elements, 249

statements, End With, 295

steps (Power Query), 181-182

stock charts, 141

storing

macros, 276

section headings, avoiding, 20

styles

customizing, 46, 60-63

gridlines, restoring, 47

layouts, 52

See also layouts

modifying, 62-63

PivotTable Styles gallery, 60

subtotals, 299

adding, 64-67, 80, 315

deleting, 64-67

formatting, 315

suppressing, 313-314

Sum function, 321-322

SUMIFS function, 239

summaries, 309-311

calculated fields, 120-123

calculations, modifying, 63-64

code, generating, 305

pivot tables, formatting, 355-356

Summarize Values By command, 64

summing totals, 298

suppressing subtotals, 64-67, 313-314

SWITCH function, 240

synchronizing pivot charts, 283-287

T

tables

See also pivot tables

comparing, 361

Data Model

adding, 163-165, 228, 346

deleting, 165

formatting, 166

elements, starting, 249

joining, 226-233

pivot. See pivot tables

Power Query, formatting, 186-188

Power View, converting to charts, 250-251

relationships, formatting, 346-347

Transactions, 158

tabs

data sets, exploding, 367-368

pivot tables, exploding data sets to, 367-368

Tabular layout, 20, 299, 309

text

grouping, 82-85

Power Pivot add-in, importing, 235-236

TEXT function, 239

themes, modifying, 62-63

thousands separators, adding, 48-49

tile boxes (Power View), 255

time

data over, animating 3D Map, 269

filtering, 37

grouping, 79

intelligence, applying, 242-243

scatter charts, animating, 259-260

timelines

applying, 107-110

slicers

creating, 36-38

inserting, 143

titles, charts, 141

toggling elements, 56-60

tools

new features, 41-43

pivot tables. See pivot tables

Power BI Desktop, 196-202

VBA (Visual Basic for Applications), 291-292

Top 10 filter, applying, 101-102

totals, 65-67

See also grand totals; subtotals

controlling, 302-304

percentages, viewing, 70

references, 129

running, tracking, 72

summing, 298

tours, building 3D Maps, 270-271

tracking

Index function, 74

percentages, 73

running totals, 72

transactional databases, 203

Transactions table, 158

trend lines, 141

troubleshooting

caches, overwriting, 39

calculations, 131-133

counting, 63-64

Excel Help, 292

formulas, 377

GetPivotData, avoiding, 376-380

layouts, 33

multiple fields, 319-321

Trust Center, enabling VBA, 289-290

turning off GetPivotData, 379-380

types

of filters, 339-340

of pivot charts, selecting, 136

U

undocking fields, 87

undoing automatic grouping, 78

unique orders, sorting items in, 355

unlimited rows, importing Data Models, 224

updating

data sources, modifying, 39-40

layouts, deferring, 41-42

ranges, 40

shell reports, 390-391

V

Value Field Settings dialog box, 48, 67, 233

values

fields, modifying calculations, 67-75

pivot tables, converting to, 55-57, 304-305

slicers, selecting, 34

Values areas, 12-13, 27

blanks, replacing with zeros, 49-50

fields, naming, 51

numeric fields, adding to, 348-349

Values columns, filtering, 99-101

variables, applying objects, 294-295

VBA (Visual Basic for Applications), 289

AutoShow, applying, 329-332

code

applying object variables, 294-295

editing, 282

shortening, 295

sizing data ranges, 293-294

conceptual filters, applying, 339-340

Data Models, applying, 345-351

enabling, 289-290

macro recorders, 292

object-oriented code, 292-293

pivot tables

calculating, 319-329

filtering, 338-339

formatting, 296-301

limitations, 301-307

region reports, formatting, 334-338

reports, creating, 307-319

search filters, applying, 342-343

ShowDetail property, 332-334

slicers, configuring, 343-345

tools, 291-292

versions, 295-296

Visual Basic Editor, 291

workbooks, saving to enable macros, 290

versions

checking, 16

VBA (Visual Basic for Applications), 295-296

videos, sharing, 271

viewing

categories, 307-319

fields, modifying, 73

grand totals, 58

percentages of totals, 70

pivot charts, 137

PivotTable Fields list, 208

rank, 71

running totals, 72

views, formatting, 9

Visual Basic Editor, 283, 291

Visual Basic for Applications. See VBA

VLOOKUP

functions, 239

limitations of, 230

tables, merging without, 223

W

web pages, sharing pivot tables, 193-196

weeks, grouping dates, 81

what-if-analysis, executing OLAP, 220-222

windows

Code, 291

Power View, 248

See also Power View

Properties, 291

Windows Authentication, 172

wizards

Create Cube File, 210

Data Connection Wizard, 172, 204

Workbook Connections dialog box, 164

Workbook Queries pane, 183

workbooks

macros, assigning, 279

pivot tables

copying, 314-315

exploding to, 372-373

saving, enabling macros, 290

web pages, designing as, 193-196

Web, linking, 196

worksheets

backward compatibility, 15-17

Data Models, formatting, 158-162

functions, applying, 129

writing VBA code

applying object variables, 294-295

shortening, 295

sizing data ranges, 293-294

X

x-axis, 140

XY (scatter) charts, 141

Y

y-axis, 140

years

filtering, 37

grouping, 79, 80

Z

zeros, replacing blanks, 49-50

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.117.105.74