精通 Power Query

第0 章　導言：一場新的革命    1
0.1　數(shù)據(jù)分析師的常見場景   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 和產(chǎn)品體驗的整合    7
0.5.1　Power Query 的組件   7
0.5.2　產(chǎn)品體驗的整合   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　單擊“獲取數(shù)據(jù)”按鈕 11
0.7.5　特殊元素   12
第 1 章　基礎知識      13
1.1　開始之前    13
1.1.1　調(diào)整Excel默認設置   13
1.1.2　 調(diào)整Power BI 桌面版默認設置   14
1.2　提取    14
1.2.1　選擇數(shù)據(jù)   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　調(diào)整和修改轉換   20
1.4　加載    22
1.4.1　設置數(shù)據(jù)類型   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　創(chuàng)建基礎查詢   34
2.2.2　創(chuàng)建查詢的引用   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　拆分現(xiàn)有查詢   45
2.6　關于查詢體系結構的最后思考    47
第3 章　數(shù)據(jù)類型與錯誤    49
3.1　數(shù)據(jù)類型與格式   49
3.1.1　數(shù)據(jù)格式   49
3.1.2　數(shù)據(jù)類型   49
3.1.3　如何設置數(shù)據(jù)格式   52
3.1.4　設置數(shù)據(jù)類型的順序 53
3.1.5　數(shù)據(jù)類型的重要性   54
3.2　常見的錯誤類型   55
3.3　步驟級錯誤    56
3.3.1　數(shù)據(jù)源錯誤   56
3.3.2　沒有找到某列   58
3.4　值錯誤    59
3.4.1　發(fā)現(xiàn)錯誤   60
3.4.2　無效的數(shù)據(jù)類型轉換 61
3.4.3　不兼容的數(shù)據(jù)類型   63
3.5　檢查查詢錯誤   64
3.5.1　發(fā)現(xiàn)錯誤的來源   64
3.5.2　修復最初查詢   65
3.5.3　刪除錯誤查詢   66
3.6　關于數(shù)據(jù)類型與錯誤的最后思考    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　僅外部數(shù)據(jù)源   72
4.2.2　數(shù)據(jù)模型的導入   74
4.2.3　導入時復制數(shù)據(jù)   75
4.2.4　導入時保持連接   80
4.3　在工具之間遷移查詢的思考   82
第5 章　從平面文件導入數(shù)據(jù)    84
5.1　了解系統(tǒng)如何導入數(shù)據(jù)   84
5.1.1　設置系統(tǒng)默認值   84
5.1.2　程序如何解析平面數(shù)據(jù) 85
5.2　導入帶分隔符的文件   87
5.2.1　源數(shù)據(jù)文件   87
5.2.2　提取數(shù)據(jù)   88
5.2.3　錯誤的解析   88
5.2.4　使用區(qū)域設置   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導入數(shù)據(jù)      104
6.1　來自當前工作簿的數(shù)據(jù)   104
6.1.1　連接到表   105
6.1.2　連接到區(qū)域   106
6.1.3　連接到命名區(qū)域   108
6.1.4　連接到動態(tài)區(qū)域   110
6.1.5　連接到工作表   112
6.2　來自其他工作簿的數(shù)據(jù)   112
6.2.1　連接到文件   113
6.2.2　連接到表   114
6.2.3　連接到命名區(qū)域   115
6.2.4　連接到工作表   115
6.3　關于連接到Excel數(shù)據(jù)的最后思考    118
第7 章　常用數(shù)據(jù)轉換    121
7.1　逆透視    121
7.1.1　準備數(shù)據(jù)   122
7.1.2　逆透視其他列   123
7.1.3　重新透視   124
7.1.4　應對變化   125
7.1.5　逆透視之間的區(qū)別 125
7.2　數(shù)據(jù)透視    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　數(shù)據(jù)排序   137
7.5　數(shù)據(jù)分組    138
第8 章　縱向追加數(shù)據(jù)    141
8.1　基本追加    141
8.1.1　追加兩個表   142
8.1.2　追加額外的表   145
8.2　追加列標題不同的數(shù)據(jù)   147
8.3　在當前文件中追加表和區(qū)域    148
8.3.1　合并表   149
8.3.2　合并區(qū)域或工作表 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　連接到本地/ 網(wǎng)絡文件夾   159
9.3.2　連接到SharePoint文件夾   160
9.3.3　連接到OneDrive for Business   162
9.3.4　連接到其他文件系統(tǒng)   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：通過主查詢進行數(shù)據(jù)清理    171
9.7.1　修復主查詢中的錯誤 171
9.7.2　保存文件屬性   172
9.7.3　添加更多的步驟   173
9.8　更新解決方案   174
9.8.1　使用數(shù)據(jù)   175
9.8.2　添加新文件   175
9.8.3　只用最后幾個文件以提升速度   176
第 10 章　橫向合并數(shù)據(jù)    179
10.1　合并基礎知識   179
10.1.1　創(chuàng)建“暫存”查詢   179
10.1.2　執(zhí)行合并   180
10.2　連接類型    182
10.2.1　左外部連接   184
10.2.2　右外部連接   186
10.2.3　完全外部連接   187
10.2.4　內(nèi)部連接   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的數(shù)據(jù)源    204
11.1　連接到Web 數(shù)據(jù)文件   204
11.2　連接到HTML網(wǎng)頁   205
11.2.1　連接到網(wǎng)頁   206
11.2.2　自然表和建議表   206
11.2.3　使用示例添加表   207
11.3　連接到?jīng)]有表的頁面   209
11.4　從Web 獲取數(shù)據(jù)的注意事項    212
11.4.1　收集數(shù)據(jù)的經(jīng)驗   213
11.4.2　數(shù)據(jù)完整性   213
11.4.3　解決方案穩(wěn)定性   213
第 12 章　關系數(shù)據(jù)源      214
12.1　連接到數(shù)據(jù)庫   214
12.1.1　連接到數(shù)據(jù)庫   214
12.1.2　管理憑據(jù)   216
12.1.3　無法連接   217
12.1.4　使用導航器   217
12.1.5　探索數(shù)據(jù)   218
12.2　查詢折疊    221
12.2.1　理解查詢折疊   221
12.2.2　支持查詢折疊的技術   223
12.2.3　常見問題   224
12.3　數(shù)據(jù)隱私級別   225
12.3.1　聲明數(shù)據(jù)隱私級別   226
12.3.2　管理數(shù)據(jù)隱私級別   227
12.3.3　隱私與性能   227
12.3.4　禁用隱私引擎   228
12.4　優(yōu)化    230
第 13 章　轉換表格數(shù)據(jù)    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　性能優(yōu)化   248
13.2.3　重構   249
13.2.4　保留“null”值   250
13.3　分組    253
13.3.1　占總計的百分比   253
13.3.2　數(shù)據(jù)排序   254
13.3.3　分組編號   257
第 14 章　條件邏輯      261
14.1　基礎條件邏輯   261
14.1.1　數(shù)據(jù)集背景   261
14.1.2　連接到數(shù)據(jù)   262
14.1.3　通過用戶界面創(chuàng)建條件邏輯   262
14.2　手動創(chuàng)建IF 判斷   265
14.3　IFERROR函數(shù)   268
14.4　多條件判斷   270
14.5　與上下行進行比較   273
14.6　示例中的列   276
第 15 章　值系統(tǒng)      282
15.1　值類型    282
15.2　表    283
15.3　列表    284
15.3.1　語法   284
15.3.2　從頭開始創(chuàng)建列表   284
15.3.3　將列表轉換為表   286
15.3.4　從表列創(chuàng)建列表   287
15.3.5　創(chuàng)建列表的列表   288
15.4　記錄    290
15.4.1　語法   290
15.4.2　從頭開始創(chuàng)建記錄 291
15.4.3　將記錄轉換為表   291
15.4.4　從頭開始創(chuàng)建多個記錄   292
15.4.5　將多個記錄轉換為表   293
15.4.6　按索引訪問表記錄   294
15.4.7　按條件訪問表記錄   295
15.4.8　從每個表行創(chuàng)建記錄   298
15.5　值    300
15.6　二進制文件   300
15.7　錯誤    301
15.7.1　行級錯誤   301
15.7.2　步驟級錯誤   301
15.8　函數(shù)    302
15.9　關鍵詞    304
15.9.1　二進制（#binary）   305
15.9.2　日期時間（ #datetime）   306
15.9.3　時間（#time）   307
15.9.4　持續(xù)時間（ #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　循環(huán)函數(shù)   324
16.3.2　關鍵詞each和_   324
16.4　其他技術    328
16.4.1　獲取第 一個值   328
16.4.2　錯誤保護   330
16.4.3　固定類型動態(tài)列表 331
16.4.4　自適應類型動態(tài)列表   334
第 17 章　參數(shù)和自定義函數(shù)    338
17.1　重新創(chuàng)建合并文件   338
17.1.1　創(chuàng)建示例文件   339
17.1.2　創(chuàng)建示例文件參數(shù)   340
17.1.3　創(chuàng)建轉換示例   341
17.1.4　創(chuàng)建轉換函數(shù)   342
17.1.5　調(diào)用轉換函數(shù)   342
17.1.6　更新轉換函數(shù)   342
17.1.7　觀察到的規(guī)律   343
17.2　使用參數(shù)構建自定義函數(shù)    344
17.2.1　創(chuàng)建文件路徑參數(shù)   345
17.2.2　創(chuàng)建Timesheet轉換   346
17.2.3　創(chuàng)建Timesheet函數(shù)   347
17.2.4　更新Timesheet查詢   347
17.3　手動構建自定義函數(shù)   349
17.3.1　構建一個單一使用場景   350
17.3.2　將查詢轉換為函數(shù)   350
17.3.3　從另一個查詢調(diào)用函數(shù)   352
17.3.4　調(diào)試自定義函數(shù)   353
17.3.5　恢復函數(shù)功能   355
17.4　動態(tài)參數(shù)表   355
17.4.1　動態(tài)文件路徑問題   356
17.4.2　實現(xiàn)動態(tài)參數(shù)表   357
17.4.3　創(chuàng)建參數(shù)表   357
17.4.4　實現(xiàn)fnGetParameter函數(shù)功能   358
17.4.5　調(diào)用函數(shù)   359
17.5　參數(shù)表的意義   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　起止日內(nèi)按日分攤 379
18.4.2　起止日內(nèi)按月分攤 381
18.4.3　在開始日期后按月分攤   384
18.4.4　關于分攤   386
第 19 章 　查詢優(yōu)化      388
19.1　優(yōu)化設置    388
19.1.1　全局 數(shù)據(jù)加載   388
19.1.2　全局 Power Query編輯器   388
19.1.3　全局 安全性   389
19.1.4　全局 隱私   389
19.1.5　當前工作簿 數(shù)據(jù)加載   389
19.1.6　當前工作簿 其他選項   390
19.2　使用緩存    391
19.2.1　強制計算   391
19.2.2　緩存結果   393
19.3　處理響應滯后   396
19.3.1　優(yōu)化策略   397
19.3.2　體驗響應滯后   397
19.3.3　重構解決方案   399
19.3.4　調(diào)整預覽數(shù)據(jù)   401
19.4　處理公式防火墻   401
19.4.1　隱私級別不兼容   402
19.4.2　數(shù)據(jù)源訪問   402
19.4.3　重建數(shù)據(jù)組合   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　打開文件時刷新數(shù)據(jù)   413
20.2.4　啟用快速數(shù)據(jù)加載 413
20.3　用宏實現(xiàn)自動刷新   414
20.3.1　刷新單個連接   414
20.3.2　按特定順序刷新   416
20.3.3　刷新所有查詢   418
20.3.4　同步刷新的問題   418
20.4　Power BI 中的計劃刷新   418