內容簡介

本書是Power Query領域的經典之作，旨在讓讀者用正確而高效的方法精通Power Query。本書是兩位作者多年積累的業務和IT工作經驗總結，同時也是全球Power Query頂級社群的大咖經驗提煉。本書從業務背景出發，在業務和IT之間做到了良好的平衡，系統化地給出了Power Query的思維框架和模式，同時按照多個企業級業務場景一一展示其實用性。

無論你是Power Query的新手還是經驗豐富的ETL專家，都可以從各自的角度領略Power Query 和M語言的魅力。對於業務用戶，本書給出了日常辦公所涉及的大量複雜數據問題解法的直接參考，並提供了大量案例，可以直接應用。對於專業用戶，本書給出了搭建大型專業數據專案的框架性流程指導及優化建議，並提供了相關案例，也可以直接複用。

作者介紹

肯·普爾斯（Ken Puls），是加拿大特許專業會計師，微軟 Excel MVP。他經營著 Excelguru 諮詢公司，並通過 Skillwave培訓平臺教用戶如何使用 Excel 和 Power BI。

米格爾·埃斯科瓦爾（Miguel Escobar），在編寫本書的時候是微軟 Power BI MVP，並在巴拿馬經營著一家名為 Powered Solutions 的諮詢公司。在本書英文版即將出版時，米格爾收到一份令人興奮的邀請函，即加入微軟 Power Query 團隊擔任專案經理。

第0章　導言：一場新的革命 1
0.1　數據分析師的常見場景 1
0.2　“魔法”的好處和危險 2
0.3　未來的改變 3
0.4　為什麼說Power Query 有“魔力” 5
0.4.1　提取 5
0.4.2　轉換 5
0.4.3 載入 6
0.5　 Power Query 和產品體驗的整合 7
0.5.1　Power Query 的組件 7
0.5.2　產品體驗的整合 8
0.6　Power Query 的更新週期 8
0.6.1　Power Query 線上版 9
0.6.2　Microsoft 365 9
0.6.3　Excel 2016/2019/2021 9
0.6.4　Excel 2010 & 2013 9
0.6.5　Power BI 桌上出版 10
0.7　如何使用本書 10
0.7.1　Microsoft 365 Excel 10
0.7.2　Power BI 桌上出版 11
0.7.3　以前的Excel版本 11
0.7.4　按一下“獲取數據”按鈕 11
0.7.5　特殊元素 12

第1章　基礎知識 13
1.1　開始之前 13
1.1.1　調整Excel默認設置 13
1.1.2　調整Power BI 桌上出版默認設置 14
1.2　提取 14
1.2.1　選擇數據 14
1.2.2　身份驗證 15
1.2.3　預覽窗口 15
1.2.4　查詢處理 16
1.3　轉換 17
1.3.1　編輯器 17
1.3.2　默認轉換 18
1.3.3　源 18
1.3.4　將第一行用作標題 19
1.3.5　更改的類型 19
1.3.6　調整和修改轉換 20
1.4　載入 22
1.4.1　設置數據類型 22
1.4.2　重命名查詢 23
1.4.3　在Excel中載入查詢 24
1.4.4　在Power BI 中載入查詢 24
1.5　刷新查詢 25
1.6　編輯查詢 26
1.6.1　在Power BI 中啟動查詢編輯器 26
1.6.2　在Excel中啟動查詢編輯器 27
1.6.3　檢查步驟 27
1.6.4　重新配置步驟 27
1.7　Power Query 的價值 29

第2章　查詢結構設計 31
2.1　使用多查詢體系結構 31
2.1.1　對ETL進行分層 31
2.1.2　單個查詢的好處 31
2.1.3　拆分查詢的好處 32
2.1.4　關於“暫存”查詢的　　　性能 32
2.2　查詢的引用 34
2.2.1　創建基礎查詢 34
2.2.2　創建查詢的引用 34
2.2.3　查詢相依樹狀結構的視覺化 37
2.2.4　使用Monkey工具查看依賴關係 38
2.3　選擇查詢載入目的地 38
2.3.1　在Power BI 中選擇載入目的地 38
2.3.2　在Excel中選擇載入目的地 39
2.3.3　更改載入目的地 41
2.4　保持查詢的條理性 43
2.4.1　查詢資料夾 43
2.4.2　將查詢分配到資料夾 44
2.4.3　排列查詢和資料夾 44
2.4.4　查詢子資料夾 45
2.5　拆分現有查詢 45
2.6　關於查詢體系結構的最後思考 47

第3章　數據類型與錯誤 49
3.1　數據類型與格式 49
3.1.1　數據格式 49
3.1.2　數據類型 49
3.1.3　如何設置數據格式 52
3.1.4　設置數據類型的順序 53
3.1.5　數據類型的重要性 54
3.2　常見的錯誤類型 55
3.3　步驟級錯誤 56
3.3.1　數據源錯誤 56
3.3.2　沒有找到某列 58
3.4　值錯誤 59
3.4.1　發現錯誤 60
3.4.2　無效的數據類型轉換 61
3.4.3　不相容的數據類型 63
3.5　檢查查詢錯誤 64
3.5.1　發現錯誤的來源 64
3.5.2　修復最初查詢 65
3.5.3　刪除錯誤查詢 66
3.6　關於數據類型與錯誤的最後思考 66

第4章　在Excel和Power BI間遷移查詢 67
4.1　在工具之間遷移查詢 67
4.1.1　Excel到Excel 68
4.1.2　Excel到Power BI 70
4.1.3　Power BI 到Excel 71
4.1.4　Power BI 到Power BI 71
4.2　導入查詢 72
4.2.1　僅外部數據源 72
4.2.2　數據模型的導入 74
4.2.3　導入時複製數據 75
4.2.4　導入時保持連接 80
4.3　在工具之間遷移查詢的思考 82

第5章　從平面文件導入數據 84
5.1　瞭解系統如何導入數據 84
5.1.1　設置系統預設值 84
5.1.2　程式如何解析平面數據 85
5.2　導入帶分隔符號的文件 87
5.2.1　源數據文件 87
5.2.2　提取數據 88
5.2.3　錯誤的解析 88
5.2.4　使用區域設置 89
5.3　導入無分隔符號的文字檔 92
5.3.1　連接到檔 92
5.3.2　清理無分隔符號檔 93
5.3.3　按位置拆分列 94
5.3.4　利用查詢中的錯誤 96
5.3.5　刪除“垃圾列” 98
5.3.6　合併列 99
5.3.7　通過分隔符號拆分列 99
5.3.8　修剪重複的空格 100
5.3.9　Power Query 的閃耀時刻 101

第6章　從Excel導入數據 104
6.1　來自當前工作簿的數據 104
6.1.1　連接到表 105
6.1.2　連接到區域 106
6.1.3　連接到命名區域 108
6.1.4　連接到動態區域 110
6.1.5　連接到工作表 112
6.2　來自其他工作簿的數據 112
6.2.1　連接到檔 113
6.2.2　連接到表 114
6.2.3　連接到命名區域 115
6.2.4　連接到工作表 115
6.3　關於連接到Excel數據的最後思考 118

第7章　常用數據轉換 121
7.1　逆透視 121
7.1.1　準備數據 122
7.1.2　逆透視其他列 123
7.1.3　重新透視 124
7.1.4　應對變化 125
7.1.5　逆透視之間的區別 125
7.2　數據透視 126
7.3　拆分列 128
7.3.1　將列拆分為多列 129
7.3.2　將列拆分為多行 130
7.3.3　拆分後逆透視與拆分到行 131
7.4　篩選和排序 132
7.4.1　按特定值篩選 133
7.4.2　按上下文篩選 135
7.4.3　數據排序 137
7.5　數據分組 138

第8章　縱向追加數據 141
8.1　基本追加 141
8.1.1　追加兩個表 142
8.1.2　追加額外的表 145
8.2　追加列標題不同的數據 147
8.3　在當前檔中追加表和區域 148
8.3.1　合併表 149
8.3.2　合併區域或工作表 153
8.3.3　Excel.Current Workbook 155
8.4　關於新增查詢的最後思考 155

第9章　批量合併檔 156
9.1　示例檔背景介紹 156
9.2　過程概述 157
9.2.1　合併檔的標準流程 157
9.2.2　合併檔的通用架構 157
9.3　步驟0：連接到資料夾 159
9.3.1　連接到本地/ 網路資料夾 159
9.3.2　連接到SharePoint資料夾 160
9.3.3　連接到OneDrive for Business 162
9.3.4　連接到其他檔案系統 162
9.4　步驟1：篩選檔 162
9.4.1　標準步驟 163
9.4.2　應用於示例場景 163
9.5　步驟2：合併檔 165
9.5.1　標準步驟 165
9.5.2　應用於示例場景 165
9.6　步驟3：轉換示例檔 167
9.6.1　使用轉換示例檔的原因 168
9.6.2　使用轉換示例檔的方法 168
9.7　步驟4：通過主查詢進行數據清理 171
9.7.1　修復主查詢中的錯誤 171
9.7.2　保存檔案屬性 172
9.7.3　添加更多的步驟 173
9.8　更新解決方案 174
9.8.1　使用數據 175
9.8.2　添加新文件 175
9.8.3　只用最後幾個檔以提升速度 176

第10章　橫向合併數據 179
10.1　合併基礎知識 179
10.1.1　創建“暫存”查詢 179
10.1.2　執行合併 180
10.2　連接類型 182
10.2.1　左外部連接 184
10.2.2　右外部連接 186
10.2.3　完全外部連接 187
10.2.4　內部連接 188
10.2.5　左反連接 189
10.2.6　右反連接 190
10.2.7　完全反連接 190
10.3　笛卡兒積（交叉連接） 191
10.3.1　方法 191
10.3.2　示例 192
10.3.3　意外問題 194
10.4　近似匹配 195
10.4.1　方法 195
10.4.2　示例 196
10.5　模糊匹配 198
10.5.1　基本模糊匹配 199
10.5.2　轉換表 200
10.5.3　減小相似性閾值 201
10.5.4　保持模糊匹配的策略 203

第11章　基於Web的數據源 204
11.1　連接到Web 數據檔 204
11.2　連接到HTML網頁 205
11.2.1　連接到網頁 206
11.2.2　自然表和建議表 206
11.2.3　使用示例添加表 207
11.3　連接到沒有表的頁面 209
11.4　從Web 獲取數據的注意事項 212
11.4.1　收集數據的經驗 213
11.4.2　數據完整性 213
11.4.3　解決方案穩定性 213

第12章　關係數據源 214
12.1　連接到數據庫 214
12.1.1　連接到數據庫 214
12.1.2　管理憑據 216
12.1.3　無法連接 217
12.1.4　使用導航器 217
12.1.5　探索數據 218
12.2　查詢折疊 221
12.2.1　理解查詢折疊 221
12.2.2　支援查詢折疊的技術 223
12.2.3　常見問題 224
12.3　數據隱私級別 225
12.3.1　聲明數據隱私級別 226
12.3.2　管理數據隱私級別 227
12.3.3　隱私與性能 227
12.3.4　禁用隱私引擎 228
12.4　優化 230

第13章　轉換表格數據 233
13.1　透視 233
13.1.1　單列多行 233
13.1.2　多層行標題 237
13.1.3　多層列標題 239
13.2　逆透視 242
13.2.1　多層行標題 242
13.2.2　性能優化 248
13.2.3　重構 249
13.2.4　保留“null”值 250
13.3　分組 253
13.3.1　占總計的百分比 253
13.3.2　數據排序 254
13.3.3　分組編號 257

第14章　條件邏輯 261
14.1　基礎條件邏輯 261
14.1.1　數據集背景 261
14.1.2　連接到數據 262
14.1.3　通過使用者介面創建條件邏輯 262
14.2　手動創建IF 判斷 265
14.3　IFERROR函數 268
14.4　多條件判斷 270
14.5　與上下行進行比較 273
14.6　示例中的列 276

第15章　值系統 282
15.1　數值型別 282
15.2　表 283
15.3　列表 284
15.3.1　語法 284
15.3.2　從頭開始創建列表 284
15.3.3　將列表轉換為表 286
15.3.4　從表列創建列表 287
15.3.5　創建列表的列表 288
15.4　記錄 290
15.4.1　語法 290
15.4.2　從頭開始創建記錄 291
15.4.3　將記錄轉換為表 291
15.4.4　從頭開始創建多個記錄 292
15.4.5　將多個記錄轉換為表 293
15.4.6　按索引訪問表記錄 294
15.4.7　按條件訪問表記錄 295
15.4.8　從每個表行創建記錄 298
15.5　值 300
15.6　二進位檔案 300
15.7　錯誤 301
15.7.1　行級錯誤 301
15.7.2　步驟級錯誤 301
15.8　函數 302
15.9　關鍵字 304
15.9.1　二進位（#binary） 305
15.9.2　日期時間（ #datetime） 306
15.9.3　時間（#time） 307
15.9.4　持續時間（ #duration） 307
15.9.5　類型（type） 308
15.9.6　表（#table） 310

第16章　理解M語言 314
16.1　M查詢結構 314
16.1.1　查詢結構 315
16.1.2　查詢定義與識別字 316
16.1.3　關於通用識別字 318
16.1.4　代碼注釋 319
16.1.5　整體效果 320
16.2　理解查詢計算 320
16.2.1　什麼是延遲計算 321
16.2.2　查詢計畫 322
16.3　反覆運算器（逐行計算） 324
16.3.1　迴圈函數 324
16.3.2　關鍵字each和_ 324
16.4　其他技術 328
16.4.1　獲取第一個值 328
16.4.2　錯誤保護 330
16.4.3　固定類型動態清單 331
16.4.4　自我調整類型動態清單 334

第17章　參數和自訂函數 338
17.1　重新創建合併檔 338
17.1.1　創建示例文件 339
17.1.2　創建示例文件參數 340
17.1.3　創建轉換示例 341
17.1.4　創建轉換函數 342
17.1.5　調用轉換函數 342
17.1.6　更新轉換函數 342
17.1.7　觀察到的規律 343
17.2　使用參數構建自訂函數 344
17.2.1　創建檔路徑參數 345
17.2.2　創建Timesheet轉換 346
17.2.3　創建Timesheet函數 347
17.2.4　更新Timesheet查詢 347
17.3　手動構建自訂函數 349
17.3.1　構建一個單一使用場景 350
17.3.2　將查詢轉換為函數 350
17.3.3　從另一個查詢調用函數 352
17.3.4　調試自訂函數 353
17.3.5　恢復函數功能 355
17.4　動態參數表 355
17.4.1　動態檔路徑問題 356
17.4.2　實現動態參數表 357
17.4.3　創建參數表 357
17.4.4　實現fnGetParameter函數功能 358
17.4.5　調用函數 359
17.5　參數表的意義 361

第18章　處理日期時間 362
18.1　邊界日期 362
18.1.1　計算邊界日期 363
18.1.2　處理財政年度日期 364
18.1.3　處理364 日型 365
18.2　日期表 367
18.2.1　原子日期表 367
18.2.2　增強日期表 368
18.2.3　財政日期列 369
18.2.4　全域日期列 369
18.2.5　自訂日期表 371
18.2.6　示例說明 373
18.3　日期時間填充 374
18.3.1　日期級別填充 374
18.3.2　小時級別填充 376
18.3.3　帶間隔的填充 377
18.4　按日期分攤 378
18.4.1　起止日內按日分攤 379
18.4.2　起止日內按月分攤 381
18.4.3　在開始日期後按月分攤 384
18.4.4　關於分攤 386

第19章　查詢優化 388
19.1　優化設置 388
19.1.1　全域−數據載入 388
19.1.2　全域−Power Query編輯器 388
19.1.3　全域−安全性 389
19.1.4　全域−隱私 389
19.1.5　當前工作簿−數據載入 389
19.1.6　當前工作簿−其他選項 390
19.2　使用緩存 391
19.2.1　強制計算 391
19.2.2　緩存結果 393
19.3　處理回應滯後 396
19.3.1　優化策略 397
19.3.2　體驗回應滯後 397
19.3.3　重構解決方案 399
19.3.4　調整預覽數據 401
19.4　處理公式防火牆 401
19.4.1　隱私級別不相容 402
19.4.2　數據源訪問 402
19.4.3　重建數據組合 402
19.4.4　連接式重構 404
19.4.5　展開式重構 406
19.4.6　傳值重構 408
19.4.7　關於公式防火牆 410

第20章　自動刷新 412
20.1　Excel自動刷新選項 412
20.2　Excel計畫刷新 412
20.2.1　後臺刷新 412
20.2.2　每x分鐘刷新一次 413
20.2.3　打開文件時刷新數據 413
20.2.4　啟用快速數據載入 413
20.3　用宏實現自動刷新 414
20.3.1　刷新單個連接 414
20.3.2　按特定順序刷新 416
20.3.3　刷新所有查詢 418
20.3.4　同步刷新的問題 418
20.4　Power BI 中的計畫刷新 418

看更多