xxxv
Contents
Creating Custom Columns ................................................................................... 877
Concatenating with a custom column .......................................................... 878
Understanding data type conversions ......................................................... 879
Spicing up custom columns with functions ................................................. 881
Adding conditional logic to custom columns ................................................ 883
Grouping and Aggregating Data ........................................................................... 884Chapter 40: Making Queries Work Together . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 887
Reusing Query Steps ........................................................................................... 887
Understanding the Append Feature ..................................................................... 891
Creating the needed base queries ................................................................ 892
Appending the data ................................................................................... 893
Understanding the Merge Feature ........................................................................ 896
Understanding Power Query joins ............................................................... 896
Merging queries ......................................................................................... 897Chapter 41: Enhancing Power Query Productivity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 903
Implementing Some Power Query Productivity Tips............................................... 903
Getting quick information about your queries ............................................. 903
Organizing queries in groups ...................................................................... 904
Selecting columns in your queries faster ..................................................... 906
Renaming query steps ............................................................................... 906
Quickly creating reference tables ................................................................ 907
Copying queries to save time ...................................................................... 908
Setting a default load behavior................................................................... 908
Preventing automatic data type changes ..................................................... 909
Avoiding Power Query Performance Issues ........................................................... 910
Using views instead of tables ..................................................................... 910
Letting your back-end database servers do some crunching .......................... 911
Upgrading to 64-bit Excel ........................................................................... 911
Disabling privacy settings to improve performance ...................................... 912
Disabling relationship detection ................................................................. 912Part VI: Automating Excel 915
Chapter 42: Introducing Visual Basic for Applications. . . . . . . . . . . . . . . . . . . . . . . . . . . 917
Introducing VBA Macros ..................................................................................... 917
Displaying the Developer Tab .............................................................................. 918
Learning about Macro Security ........................................................................... 919
Saving Workbooks That Contain Macros ............................................................... 920
Looking at the Two Types of VBA Macros .............................................................. 921
VBA Sub procedures ................................................................................... 921
VBA functions ........................................................................................... 922
Creating VBA Macros .......................................................................................... 924