xxxiv
Contents
Utilizing DAX to Create Calculated Columns ......................................................... 822
Identifying DAX functions safe for calculated columns ................................ 822
Building DAX-driven calculated columns ..................................................... 824
Month sorting in Power Pivot–driven PivotTables ................................ 826
Referencing fields from other tables............................................................ 827
Nesting functions ...................................................................................... 829
Understanding Calculated Measures ..................................................................... 829
Editing and deleting calculated measures .................................................... 832
Using Cube Functions to Free Your Data ............................................................... 833
Chapter 38: Introducing Power Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 835
Understanding Power Query Basics ...................................................................... 835
Understanding query steps ......................................................................... 842
Viewing the Advanced Query Editor ............................................................ 844
Refreshing Power Query data ...................................................................... 844
Managing existing queries ......................................................................... 845
Understanding column-level actions ........................................................... 846
Understanding table actions....................................................................... 849
Getting Data from External Sources ..................................................................... 851
Importing data from files ........................................................................... 852
Getting data from Excel workbooks .................................................... 852
Getting data from CSV and text files ................................................... 853
Importing data from database systems ........................................................ 855
Importing data from relational and OLAP databases ............................ 855
Importing data from Azure databases ................................................. 855
Importing data using ODBC connections to nonstandard databases ....... 855
Getting Data from Other Data Systems ................................................................. 855
Managing Data Source Settings ........................................................................... 856
Editing data source settings ....................................................................... 857
Chapter 39: Transforming Data with Power Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 859
Performing Common Transformation Tasks ........................................................... 859
Removing duplicate records ........................................................................ 859
Filling in blank fields ................................................................................ 862
Filling in empty strings ............................................................................. 862
Concatenating columns .............................................................................. 863
Changing case ........................................................................................... 865
Finding and replacing specific text ............................................................. 865
Trimming and cleaning text ....................................................................... 866
Extracting the left, right, and middle values ............................................... 868
Extracting first and last characters ............................................................. 869
Extracting middle characters...................................................................... 870
Splitting columns using character markers .................................................. 870
Unpivoting columns ................................................................................... 873
Unpivoting other columns .......................................................................... 874
Pivoting columns ...................................................................................... 875
