Excel商務智能：Power Query和Power Pivot數據清洗、建模與分析實戰

第1章Excel：你的職場生產力工具/ 1
1.1 你所不知道的Excel分析“利器” / 1
1.2 從Excel到Power BI，只需要一步/ 2

第2章認識Power Query編輯器/ 4
2.1 初識Power Query / 4
2.2 編輯器管理界面介紹/ 5
2.3 創建查詢的方法/ 7
2.4 數據源路徑的修改與設置/ 9
2.5 數據上載與刷新/ 11

第3章Power Query的基本操作實例/ 14
3.1 入門基礎知識/ 14
3.1.1 數據類型的設置/ 14
3.1.2 標題的升降設置/ 16
3.1.3 “轉換”與“添加列”選項卡中的功能/ 16
3.2 刪除行或列操作/ 18
3.2.1 選擇列與刪除列/ 18
3.2.2 刪除行與保留行/ 20
3.2.3 通過篩選器刪除行/ 24
3.3 添加列操作/ 25
3.3.1 簡單快速地添加條件列/ 25
3.3.2 為行添加自定義序號/ 27
3.3.3 添加自定義列/ 29
3.4 拆分列與合併列操作/ 30
3.4.1 實例1：按分隔符拆分列/ 30
3.4.2 實例2：按字符數拆分列/ 33
3.4.3 實例3：按位置拆分列/ 34
3.4.4 實例4：其他拆分列的方法/ 35
3.4.5 合併列常用的方法/ 36
3.5 透視列與逆透視列操作/ 38
3.5.1 一維表和二維表/ 38
3.5.2 實例1：一維表轉二維表/ 39
3.5.3 實例2：二維表轉一維表/ 41
3.5.4 實例3：含有多重行/列表頭的數據清洗/ 42
3.6 提取文本值中指定字符的操作/ 45
3.6.1 實例1：按指定的長度提取文本值中指定的字符/ 45
3.6.2 實例2：按分隔符的位置提取文本值中指定的字符/ 47
3.7 數學運算和分組統計/ 49
3.7.1 聚合運算的操作/ 49
3.7.2 實例1：活用“選擇性粘貼”功能處理考試成績/ 50
3.7.3 實例2：使用分組統計功能快速計算各部門的數據/ 53
3.8 追加查詢與合併查詢/ 54
3.8.1 實例1：使用追加查詢批量合併多個Excel工作表數據/ 54
3.8.2 認識合併查詢的6種類型/ 56
3.8.3 實例2：使用合併查詢完成各種數據匹配/ 61

第4章M函數和M公式基礎入門/ 65
4.1 M函數和M公式介紹/ 65
4.1.1 M函數和M公式/ 65
4.1.2 主要的M函數類型/ 66
4.1.3 常用的數據類型/ 67
4.1.4 運算符/ 68
4.1.5 如何查看函數幫助/ 69
4.2 三大數據結構/ 72
4.2.1 列表/ 72
4.2.2 記錄/ 74
4.2.3 表/ 74
4.2.4 數據結構的組合和深化/ 75
4.2.5 數據結構的擴展/ 77
4.3 數據結構之間的相互轉換/ 79
4.3.1 List和Record之間的轉換/ 79
4.3.2 Table和List之間的轉換/ 80
4.3.3 Table和Record之間的轉換/ 83
4.4 M公式中常用的語句/ 84
4.4.1 let…in…語句/ 84
4.4.2 條件分支語句/ 85
4.4.3 容錯語句try…otherwise… / 88
4.4.4 each _與(x)=＞的關係/ 89
4.4.5 為公式添加註釋/ 92

第5章常用的M函數實戰詳解/ 94
5.1 各種數據類型之間的相互轉換/ 94
5.1.1 將值轉換為文本/ 94
5.1.2 將值轉換為數值/ 95
5.1.3 將值轉換為日期/ 96
5.2 List和Table的批量轉換實戰/ 96
5.2.1 批量轉換函數List.Transform的實際應用/ 96
5.2.2 批量轉換函數Table.TransformColumns的實際應用/ 97
5.3 獲取和刪除各種數據實戰/ 99
5.3.1 使用Table.Skip函數和Table.SelectRows函數篩選行/ 99
5.3.2 獲取和刪除指定文本值中的指定字符/ 101
5.3.3 獲取和刪除列表中的元素/ 102
5.4 各種數據結構的拆分、合併、截取和替換實戰/ 104
5.4.1 實例1：表的拆分與合併應用/ 104
5.4.2 實例2：列表的拆分與合併應用/ 106
5.4.3 實例3：拆分和提取文本值中的數值並求和/ 108
5.4.4 對文本值進行截取的函數/ 112
5.4.5 實例4：批量替換和有條件地批量替換文本值/ 113
5.4.6 實例5：使用List.Zip函數批量更換標題及製作工資條/ 117
5.5 判斷文本值和列表中是否包含指定的內容/ 120
5.5.1 實例1：對任意組合的條件值求和/ 120
5.5.2 實例2：根據標準答案計算多選題的得分/ 123
5.6 分組函數Table.Group及其應用/ 125
5.6.1 Table.Group函數和常規分組計算/ 125
5.6.2 實例：條件分組計算和數據清洗整理獲獎數據/ 129
5.7 參數與自定義函數/ 132
5.7.1 參數的設置方法/ 132
5.7.2 實例：創建和調用自定義函數將一列拆分為多列/ 134

第6章Power Query綜合實戰/ 140
6.1 數據獲取綜合實戰/ 140
6.1.1 實例1：獲取並合併Excel工作簿中的多個工作表的數據/ 140
6.1.2 實例2：獲取並合併多個文件夾下的Excel工作簿中的數據/ 144
6.1.3 實例3：獲取網頁中的表格數據/ 147
6.1.4 實例4：獲取CSV或TXT文件數據/ 151
6.1.5 實例5：實時獲取數據庫中的數據/ 153
6.2 數據轉換綜合實戰/ 155
6.2.1 實例1：將復雜的二維調薪表轉換為一維明細表/ 155
6.2.2 實例2：高效快速地清洗零亂的考勤數據/ 158
6.2.3 實例3：同時拆分組合的供應商中文名稱和英文名稱/ 164
6.2.4 實例4：批量提取Excel工作簿中不規則的防疫數據/ 167

第7章認識Power Pivot與DAX / 170
7.1 Power Pivot介紹/ 170
7.1.1 認識Power Pivot / 170
7.1.2 從數據透視表的不重複計算說起/ 171
7.1.3 在Excel中加載Power Pivot / 173
7.1.4 認識Power Pivot的管理界面/ 174
7.2 Power Pivot的數據獲取方式/ 175
7.2.1 從表格/區域和Power Query導入數據/ 176
7.2.2 從Excel文件導入數據/ 177
7.2.3 從文本文件導入數據/ 178
7.2.4 從剪切板導入數據/ 179
7.2.5 從數據庫導入數據/ 180
7.3 認識數據分析表達式DAX / 182
7.3.1 常用的DAX函數類型/ 182
7.3.2 DAX中的數據類型與運算符/ 184
7.3.3 創建DAX表達式時表和列的引用方式/ 185

第8章Power Pivot和DAX基礎知識/ 187
8.1 理解計算列與度量值/ 187
8.1.1 依附於數據表的計算列/ 187
8.1.2 能適應各種環境的度量值/ 189
8.1.3 度量值與數據透視表的計算字段/ 191
8.1.4 如何選擇度量值與計算列/ 193
8.1.5 管理度量值/ 193
8.2 數據模型與表間關係/ 195
8.2.1 理解Power Pivot的數據模型/ 195
8.2.2 多表操作時表間關係的建立和管理/ 196
8.2.3 表、列和度量值的隱藏/ 199
8.2.4 LOOKUPVALUE函數介紹/ 200
8.2.5 RELATED函數與RELATEDTABLE函數介紹/ 201
8.3 DAX的基礎函數/ 203
8.3.1 以SUMX為代表的結尾帶有X的聚合函數/ 203
8.3.2 篩選函數FILTER和邏輯運算符/ 205
8.3.3 DAX中最重要的CALCULATE函數/ 207
8.3.4 CALCULATE函數的篩選器的選擇/ 209
8.3.5 VALUES函數和DISTINCT函數/ 211
8.3.6 初識ALL函數和ALLEXCEPT函數/ 213
8.4 初識計值上下文/ 214
8.4.1 初識篩選上下文/ 215
8.4.2 創建篩選上下文/ 216
8.4.3 初識行上下文/ 219
8.4.4 行上下文轉換/ 220
8.5 CALCULATE函數的調節器/ 224
8.5.1 刪除篩選器的ALL函數/ 224
8.5.2 追加篩選的KEEPFILTERS函數/ 227
8.5.3 激活關係的USERELATIONSHIP函數/ 228

第9章DAX進階知識和常見應用/ 231
9.1 Power Pivot和數據透視表/ 231
9.1.1 實例1：在數據透視表中使用自定義排序：按列排序/ 231
9.1.2 實例2：在數據透視表中創建KPI規則——設置“條件格式” / 233
9.2 在DAX中使用VAR變量/ 236
9.2.1 關於VAR變量/ 236
9.2.2 使用變量時應該避免的錯誤/ 237
9.3 常見的DAX函數和實際案例應用/ 238
9.3.1 實例1：使用CONCATENATEX函數進行文本值透視/ 238
9.3.2 實例2：使用ALLSELECTED函數動態地計算各類占比/ 241
9.3.3 實例3：使用RANKX函數動態地計算各類排名/ 244
9.3.4 實例4：自定義數據透視表標題行完成複雜的報表/ 248
9.4 DAX作為查詢工具的實際應用/ 251
9.4.1 數據查詢和EVALUATE / 251
9.4.2 實例1：使用ADDCOLUMNS函數建立查詢表/ 254
9.4.3 實例2：使用SELECTCOLUMNS函數建立查詢表/ 256
9.4.4 實例3：使用SUMMARIZE和SUMMARIZECOLUMNS函數分組匯總數據/ 257
9.5 Power Pivot數據模型與多維數據集函數/ 259
9.5.1 認識CUBE類函數/ 260
9.5.2 實例1：使用CUBEVALUE函數提取Power Pivot數據模型中的數據/ 262
9.5.3 實例2：使用“切合器+CUBEVALUE函數”動態提取Power Pivot數據模型中的數據/ 266

第10章時間智能計算/ 269
10.1 認識時間智能函數和日期表/ 269
10.1.1 時間智能函數與日期函數/ 269
10.1.2 日期表的創建與標記/ 270
10.1.3 與時間智能函數相關的常用計算指標/ 272
10.2 常見的時間智能計算/ 273
10.2.1 實例1：年初、季初與月初至今計算/ 273
10.2.2 實例2：各類同比與環比的計算/ 275
10.2.3 實例3：動態移動平均分析模型/ 280
第11章Power Pivot綜合實戰/ 283
11.1 實例1：TOP-N門店銷售和利潤貢獻度分析模型/ 283
11.2 實例2：折扣比例分組（分區間）分析模型/ 286
11.3 實例3：動態ABC分類分析模型（帕累托分析模型） / 288
11.4 實例4：RFM客戶價值分析模型/ 291
11.5 實例5：員工在職、入職、離職和離職率的計算模型/ 296