A
Adding dates 269
Add items 92
Addition 175
AdventureWorks 81
Aggregating while grouping 144
All files in a folder 41
AND() equivalent 245
Any data type 119
Appending any type 122
Appending operations 33
Append query 35
Excel vs Power Query icons 37
At the left-most delimiter 77
Authentication options
for web data 127
Averaging 145
Azure 81
B
Banding levels 246
Base 0 versus base 1 176
Begins with xls 58
Benefit versus time 3
Binaries 209
Blank query 20
Budget requests via e-mail 137
C
Calendar
appointments from Exchange 137
tables 263
Calling a function 237
Case sensitive formulas 176
Changing type 15
Chrome developer tools 131
CLEAN() equivalent 72
Close & load 16
Collapsing fields 93
Combining worksheets 53
Comments 220
Concatenation issues 177
Conditional logic 191
Configuration window 24
Connection-only query 97
Connections, managing 84
Consecutive numbers 198
Consolidating tables 53
Content column 42
Corrupting your model 115
Counting 145
Csv.Document 48
CSV, importing from 13
Currency.From 179
Custom.Column1 49
Custom columns 173
Custom functions 233
D
Dangers 115
Databases 81
Data math 177
Data model dangers 115
Data monkey 1
Data refresh error 113
Data types 119
best practices 124
Date.From 178
Date.FromText 178
Date functions 267
Date modified 79
Date serial numbers 120
Dates from text 47
Date.ToText 178
Date types 28
DD/MM/YYYY 28
Debugging 239
Decimal.From 179
Define once 6
Distinct count 145
Division 175
Don’t aggregate 160
Duplicate spaces 73
Duplication of data 60
Duration.From 179
Duration.FromText 179
Dynamic headers 101
Dynamic named ranges 19
Dynamic parameter tables 257
E
Each row 220
End of month 47
Ends with 57
Errors 209
are exciting 75
filtering 56
row-level 209
step-level 209
ETL 5
Example files 10
Excel.CurrentWorkbook() 53, 62
Excel.Workbook([Content]) 62
Exchange data 137
best practices 141
Expand icon 59
for lists 201
Expand solution to many files 50
Explicit vs implicit conversion 177
Exponents use power not ^ 175
Extracting date parts 268
Extract, transform, load 5
F
File from folder 79
File properties, preserving 46
Files
listing from a folder 41
sample workbooks for book 10
Filling
horizontally 153
vertically 152
Filtering errors 56
FIND() equivalent 185
Fire hose first inch 36
First row as headers 26
fnGetParameter() 259
fnSWITCH() 254
Folder, import all files 41
Formula.Firewall error 271
Formulas 173
case sensitive 176
discovering functions 175
From web 127
Functions 210
Future-proofing 41
fx button 225
G
Garbage columns 76
Gear icon 24
Google analytics 94
Grouping queries 276
Grouping via a query 143
H
Header rows, extra 23
HTML navigating 131
I
Icon to load all 42
IF() equivalent 193
IFERROR() equivalent 192
Implicit vs explicit conversion 177
Importing 13
Excel data 16
from dynamic ranges 19
from named ranges 18
web data 127
Inconsistent alignment 71
INDEX() 19
Int64.From 179
IT alert dashboard 137
K
KPIs 89
L
Learning map 7
LEFT() equivalent 180
LEN() equivalent 183
Line breaks in M 218
Linking steps in M 217
List files in folder 41
List from table columns 199
List objects 196
Lists of lists 200
List Tools tab in ribbon 196
List to table 198
Live versus saved 62
Loading
connection only 107
from Exchange 137
to data model 109
to tables 107
Locale 29
Logical expressions 191
M
M
each row 220
getting started with 173
line breaks 218
special characters 220
#"syntax" 217
tying steps together 217
understanding 215
Mail, loading 137
Managing connections 84
Many-to-many joins 100
Max 145
Median 145
Meeting requests 137
Merge columns 76
Merging steps 15
Merging tables 97
Middle of data set 223
MID() equivalent 187
Min 145
MM/DD/YYYY 28
Modulo column 159
Most recently modified 79
Move down 27
Multiplication 175
N
Name conflicts 18
Named ranges, defining 18
Navigating HTML 131
Navigator 85
Nondelimited text 71
Nonprinting characters 71
Null values 39
Number.From 179
Number.FromText 179
Number.ToText 178
O
Objects 195
Functions 210
Lists 196
Record 201
Tables 195
Values 208
ODBC 81
OK is greyed out 98
One-to-one joins 99
Operating on each row 220
Optimization 96
OR() equivalent 243
Organizing queries 275
P
Parameter tables 257
People, from Exchange 137
Percentage of total 149
PivotTable
inserting 43
refreshing 38
Pointer queries 97
Portability of Exchange queries 141
Power for exponents 175
Power Update 171
pqVLOOKUP 247
Preserving file properties 46
Preview a field 48
Preview window 140
Previous order date 229
Previous row 229
Print area as dynamic range 56
Promoted headers 14
Q
Query folding 95
Query name 107
R
Reach of tools 4
Rearranging steps 27
Recently modified file 79
Record objects 201
from table row 205
to table 203
Recursion of folders 46
Referring to previous step 225
Refresh all 45
Refreshing 165
every x minutes 166
methods for 16
on workbook open 166
via Power Update 171
via properties 165
with VBA 166
Regional settings 29
Relationship detection 86
Remove errors 56
Remove other columns 86
Removing rows 26
Removing steps 24
Renaming column 14
Repeating groups 63
Repeating headers 71
Repivoting data 65
Replace $ with nothing 33
Retroactive data 66
Retrofitting for translation 102
Returning text dates 269
RIGHT() equivalent 182
Rounding 149
Row-level errors 209
S
Sample files 10
Saved versus live 62
Sequential nature 46
Single column error 24
Source, changing 78
Spaces, duplicate 73
Split by column 26
Split by position 73
SQL ninja 96
SQL Server 81
SQL Server Analysis Services 88
SSAS 88
SSMS 96
Stacked tables 158
Staging/loading queries 274
Step-level errors 209
Subtraction 175
Summing 145
Survey data via e-mail 137
SWITCH() equivalent 252
T
Table.Max 147
Table name in Power Pivot 112
Tables
objects 195
official Excel table 17
versus ranges 21
versus views 96
Tasks, from Exchange 137
Text
functions 179
non-delimited 71
to dates 47
txt instead of CSV 25
Text.From 177
Time.From 179
Time.FromText 179
Time.ToText 178
Top seller 147
To table 198
Training workshops 7
Translation table 102
Transposing data 153
stacked tables 158
TRIM() equivalent 72
Try ... otherwise 192
U
Unpivoting data 63
locking columns 68
with subcategories 151
Unpivot other columns 67
Use original column name as prefix 49
V
Values objects 208
Views versus tables 96
VLOOKUP()
for merging 97
Power Query equivalent 245
W
Web data 127
Web-hosted files 129
Where to find 8
Wikipedia dangers 134
Workbook queries pane, showing 16
Workbooks, combining 57
Worksheets, combining 53
Y
Years, converting to 86
Continue the Journey
Sharpen your
Power Pivot, Power Map,
Power Map, and Excel
skills with books from
leading authors.
inside back cover - page intentionally left blank
