Index

A

Access

connecting to 12

actual-to-target values 162

actual value calculation 160

Add Conditional Column dialog box 72–73

Advanced Editor 45, 49

AdventureWorks2016 Database 5

aggregate functions 126–127

ALL 147

Analysis Services 13–17, 141

AND 129

Append transformation 55–57

area charts 206–207

arithmetic operators 123

automatic relationships 104–105

AVERAGE 126

AVERAGEX 128

Azure

data sources, connecting to 24–26

subscription 24

Azure Data Lake 25–27

Azure SQL

connecting to 24

Azure SQL Data Warehouse

connecting to 24

B

bar charts 202–203, 205

Blank Query 30

box and whisker plots 216–217

bubble charts 207

business hierarchies 156–157

business rules

applying 71

C

CALCULATE 135–136, 147

calculated columns 90, 116–118, 128, 158

Calculation Area 89

CALENDAR 130

CALENDARAUTO 130

cardinality 101, 102

categorization 113, 190

charts. See PivotCharts

child functions 140

Clear All command 179

column charts 202–203

columns

adding 43–44, 56

calculated 90, 116–118, 128, 158

conditional 72–73

extracting values from existing 47–48

formatting 112–115

formatting, in PivotTables 183–184

from examples 71

from source systems 90

hiding 110–111, 187–188

index 73

merging 48–49

rearranging 42–43, 61–62

related 101

relationship 105

removing 46–47

renaming 44–45, 189

Sort By 111–112

sorting 190

splitting 66–67

Combine Files tranform 53–55

combo charts 205–206

Compact Form

for PivotTables 178

comparison operators 123

composite keys 103

CONCATENATE 132

CONCATENATEX 132

conditional columns 72–74

CONTAINS 134

COUNT 127

COUNTA 127

COUNTAX 128

COUNTBLANK 127

counting functions 127

COUNTROW 127

COUNTX 128

credentials 70

CSV data sources

connecting to 19–20

cube functions 150–152, 212

CUBEKPIMEMBER 150

CUBEMEMBER 150, 151–152

CUBEMEMBERPROPERTY 150

CUBERANKEDMEMBER 150

CUBESET 150

CUBESETCOUNT 150

CUBEVALUE 150, 151–152

Custom Column dialog 56

custom functions

invoking 71–72

D

data

aggregating 3

business rules for 71–73

changing format to support visualization 73–74

cleansing 79–83

compression 89

encryption 8

filtering 74–79, 193–198, 220–221

for decision making 199

formatting 79

grouping 198

importing 2–32

from Excel workbooks 27

from Power BI 224–231

to support basic transformations 33–37

incomplete, managing 80–81

loading into Data Models 68–69

manipulation, in Power BI 232

merging 57–61

parameters 76–79

pivoting 83

pre-aggregated 74–75

presenting for end users. See data visualizations

Privacy Levels 30–32

received as report 81–83

summarizing 198

traditional integration of 99

unpivoting 81–83

data analysis

Analysis Services 13–17

in Excel 3–4

Data Analysis Expressions (DAX) 100, 109, 116

basics of 117–126

calculated columns 117–118

data types 122–123

evaluation contexts 123–126, 128

formulas 116–141

aggregate functions 126–127

counting functions 127

date and time functions 130–132

filter functions 134–137

information functions 134

iterators 128

logical functions 129–130

other functions 141

parent and child functions 140

statistical, math, and trig functions 139–140

text functions 132–133

time intelligence functions 138–139

hierarchies 158

measures 118–122, 147

operators 123

projection techniques 145

queries

creating 141–149

structure 142–149

SWITCH statement 129–130

syntax 117, 122

Database Management Systems (DBMS)

connecting to 18

databases

Access

connecting to 12

connecting to and importing from 2–24

Oracle

connecting to 17–18

SQL Server

connecting to 5–11

importing data from 93–97

SQL Server Analysis Services

connecting to 13–17

data filters 74–79, 193–198

options 195–196

PivotCharts 220–221

PivotTables 193–198

slicers 197–198

timelines 198

data models 3, 51, 87–170, 171

advantages of 89–90

calculated columns 117–118, 128, 158

composition of 90

creating 87–108

data types 122–123, 189

DAX formulas 116–141

DAX queries 141–149

Excel formulas 149–152

facts about 91–92

for decision making 199

hierarchies

creating 152–159

KPIs 160–163

loading data into 68, 91–97

manually entering data into 97–98

measures 118–122, 191–193

naming conventions and descriptions 110

optimization for reporting 108–115, 185–192

Perspectives 110–111

Power Pivot interface 88–89

relationships 90, 99–108

specifications and limits 91

synonyms 110

understanding 88–91

Data preview pane 80

data relationships. See relationships

data shaping 32, 73–74, 87

data sources

connecting to 4–5

Access 12

Analysis Services 13–17

Azure 24–26

Database Management Systems 18

folders 21–24

JSON 21

online services 27

Oracle 17–18

SQL Server 5–11

text/CSV 19–20

XML 20

importing from 2–32

linking to data in other 27–30

PivotTables 180

Privacy Levels 30–32

settings 69–70

data transformations 32–79

adding columns 43–44, 56

advanced 53–70

appending queries to files 55–57

applying business rules 71–73

basic 33–37, 39–53

combining files 53–55

date and time 44

designing and implementing 32–70

extracting values 47–48

filtering 74–79

importing data for 33–37

merging columns 48–49

merging queries 57–61

query folding 51–53

rearranging columns 42–43, 61–62

removing columns 46–47

renaming columns 44–45

replacing null values 62

splitting columns 66–67

data types 109, 113, 122, 189

Data View 89

data visualizations 171–238

changing data format to support 73–74

PivotCharts 198–221

PivotTables 171–198

Power BI and 221–232

presentation types 200

data warehouses 33

DATE 131

DATEDIFF 131

date filters 75–76

date functions 130–132

date hierarchies 153–156

date tables 114–115

date transformations 44

DATEVALUE 132

DAX. See Data Analysis Expressions

DAY 132

default query load settings 11

Diagram View 99–100

DISTINCTCOUNT 127

doughnut charts 204

drivers 17, 18

duplicate rows

removing 63

E

EDATE 131

Edit Permissions dialog 70

Edit Table Properties dialog 186

encryption 8

EOMONTH 131

EVALUATE 142, 144

evaluation contexts 123–126, 128

EXACT 133

Excel 1–86

analytics in 3–4

charts 198–221

connecting to sources 4–24

data analysis in 4

data transformations in 32–79

Get & Transform functionality 2, 4–24

importing data 2–32

Power BI and 221–232

publishing from, to Power BI 224–229

Queries & Connections window 36

workbooks

importing from 27

Excel 2016 ribbon 2

Excel Data Models. See data models

Excel formulas

creating 149–152

Excel tables

manually entering data into 97–98

Expanded Column Selector 60

explicit measures 119–121, 190

Extract command 47–48

Extract Transform and Load (ETL) 32

F

fact-based decision making 199

FALSE 129

Field Settings

in PivotTables 181–184

files

appending queries to 55–57

combining 53–55

connecting to and importing from 2, 19–20

file size restrictions 109

FILTER 135–136, 148

Filter Context 124–125

filter functions 134–137

Filter Rows dialog 78

FIND 133

folders

connecting to 21–24

SharePoint 23–24

foreign keys 104

FORMAT 145, 193

Format functions 79

formula bar 38

Formula Bar 89

functions. See also specific functions

aggregate 126–127

counting 127

cube 150–152, 212

custom, invoking 71–72

date and time 130–131

Excel 149–152

filter 134–137

information 134

iterators 128

logical 129–130

other 141

parent and child 140, 158–159

statistical, math, and trig 139–140

text 132–133

time intelligence 138–139

funnel charts 210

G

Get Data function 4

Get External Data functions 142

Get & Transform functionality 2, 91

connecting to sources 4–24

Group By dialog 74–75

H

HAVING clause 148

Hide From Client Tools 110, 187–188

hierarchies 90

business 156–157

creating 152–159

date 153–156

managing 157

parent-child 158–159

resolving issues with 158–159

histograms 215–216

HOUR 132

I

IF 129

IFERROR 129

IF statements 72–73

implicit measures 120–121

Import Data dialog 15, 34–35, 51

incomplete data 80–81

index columns 73

information functions 134

Invoke Custom Function dialog 72

iterators 128

J

Join Kinds 58–59

JSON (JavaScript Object Notation)

connecting to 21

K

key performance indicators (KPIs) 90

actual-to-target values 162

actual value 160

creating 160–163

measures to support 160

target value 161–162

L

LEFT 133

LEN 133

line charts 203–204

linked tables 98

logical functions 129–130

logic centralization 90

logic operators 123

LOOKUPVALUE 134

M

Manage Parameters dialog 77, 78

many-to-many relationships 102

map charts 208–209

math functions 139–140

MAX 126

MAXX 128

measures 118–122, 147

calculated 192–193

explicit 119–121, 190

formatting 191–193

implicit 120–121

names for 119

query scoped 147

to support KPIs 160

memory optimizations 109

Merge function 48–49, 48–52, 57–61

Microsoft Access. See Access

Microsoft Excel. See Excel

Microsoft SQL Server. See SQL Server

MID 133

MIN 126

MINUTE 132

MINX 128

M language 30, 33, 41

MONTH 132

Move PivotTable command 179

N

naming conventions 110, 119

Native Database Query 6, 7

Native Query dialog 52

NOT 129

NOW 131

null values

replacing 62

numeric filters 75

O

Office 365 109

Office Data Connection Files 14–15

OneDrive for Business 230

online services

connecting to 27

operators

DAX 123

OR 129, 148

Oracle databases

connecting to 17–18

ORDER BY clause 143

Outline Form

for PivotTables 178

P

parameters 76–79

parent-child relationships 158–159

parent functions 140

parenthesis operator 123

Pareto charts 216

permissions 70

Perspectives 90, 110–111

pie charts 204

PivotCharts 3, 198–221

data filtering 220–221

formatting 217–220

introduction to 198

selecting 199–202

types 199–217

area charts 206–207

bar charts 205

box and whisker plots 216–217

bubble charts 207

column charts 202–203

combo charts 205–206

doughnut 204

funnel charts 210

histograms 215–216

line charts 203–204

map charts 208–209

pie 204

radar charts 209–210

scatter plots 207–208

stock charts 208

sunburst charts 213–214

treemap charts 210–213

waterfall 217

pivot data 83

PivotTable Fields pane 174–175

PivotTables 3, 89, 91, 99, 171–198

changing source for 180

changing views 180–181

Connection Properties 180

consuming data via 149

converting to cube functions 212

creating 172–174

data filtering 193–198

data model optimization 185–192

formatting 172–179

general commands 179–181

layout and styling 176–179

measures 191–193

values 181–185

grouping and summarizing data 198

introduction to 171

naming 176

overview 172–175

populating 175–176

refreshing 179–180

Report Layout Forms 178

shortcomings of 149

slicers 197–198, 212

treemap 211

with implicit and explicit measures 120–121

Power BI 4

account 222

data manipulation in 232

desktop 222

Embedded 223–224

import Excel data from 224–231

import from 229–231

interacting with 221–232

Mobile 223

overview 222–224

Publisher for Excel 231

publishing from Excel to 224–229

Service 222–223

Power Maps 3

Power Pivot 3–4

Diagram View 99–100

interface 88–89

loading data to data models from 91–97

Power PivotTables 91

Power Query 2, 33

Blank Query 30

Internationalization 19–20

M language 33

Power View 3

pre-aggregated data 74–75

primary keys 104

Privacy Levels 30–32, 70

projection 145

Q

queries

across tables 104

appending to files 55–57

Blank Query 30

DAX 141–149

deleting 63, 70

duplicating 70

management of 69–70

merging 57–61

moving to groups 50–51

referenced 70

Query Dependencies 63

Query Editor 30, 188

Advanced Editor 45, 49

data transformations with 32–79

data types 41–42

default query load settings 11

overview 37–38

query folding 51–53

Query Options 105

query scoped measures 147

Query Settings 40

R

radar charts 209–210

Recent Sources 70

referenced queries 70

referential integrity 104

refreshable tables 98

Refresh All command 179

Refresh command 179

RELATED 148, 187

related columns 101

related tables 100

relationship columns 105

relationships 90

creating 100

automatic 104–105

manual 106–108

defined 99

direction 101

managing 99–108

missing 107

multiple, between tables 103–104

overview of 100–102

parent-child 158–159

requirements for 102

unsupported 102–103

REPLACE 133

Report Layout Forms 178

reports

data received as 81–83

model optimization for 108–115

ribbon 88

RIGHT 133

ROLLUP 146

Row Context 123–124, 126

row limit 3, 89

rows

filtering 77–78

formatting, in PivotTables 183–184

removing duplicate 63

S

SAMEPERIODLASTYEAR 138

scatter plots 207–208

SEARCH 133

SECOND 132

self-joins 103

SharePoint folders

connecting to 23–24

slicers 197–198, 212

Sort By columns 111–112

Split function 66

SQL Server

connecting to 5–11

importing data from 93–97

user credentials 8

Windows-based credentials 7

SQL Server Analysis Services (SSAS) 13–17, 149

SQL Server Reporting Services (SSRS) 116

START AT clause 143

statistical functions 139–140

stock charts 208

SUBSTITUTE 133

SUM 126

summarization methods 113

SUMMARIZE 144–149

Summarize By Property 190

SUMMARIZECOLUMNS 147

SUMX 128

sunburst charts 213–214

SWITCH statement 129–130

synonyms 110

T

tab-delimited files 19–20

Table Expand function 60

Table Import Wizard 94, 142

tables 90. See also Excel tables; See also PivotTables

date 114–115

hiding 110–111

linked 98

querying across 104

refreshable 98

related 100

relationships 99–108

renaming 188–189

Tabular Form

for PivotTables 178

target value calculation 161–162

text concatenation operator 123

Text/CSV data sources

connecting to 19–20

text filters 75–76

text functions 132–133

TIME 131

time functions 130–132

time intelligence functions 138–139

timelines 198

time transformations 44

TIMEVALUE 132

TODAY 131

TOTALMTD 138

TOTALQTD 138

TOTALYTD 138

transformations. See data transformations

treemap charts 210–213

trig functions 139–140

TRIM 133

TRUE 129

T-SQL statements 142

U

unpivot data 81–83

UPPERCASE transform M code 68–69

USERELATIONSHIP function 136

V

VALUE 133

Value Fields Settings dialog box 181–183

View Native Query 52

visualizations. See data visualizations

VLOOKUP() function 3, 99

W

waterfall charts 217

WEEKDAY 132

WEEKNUM 132

workbook size optimizer 109

X

XML (eXtensible Markup Language) data sources

connecting to 20

xVelocity 3

xVelocity Engine 89

Y

YEAR 132

Z