精通 Power Query

第0 章　導(dǎo)言：一場(chǎng)新的革命    1
0.1　數(shù)據(jù)分析師的常見(jiàn)場(chǎng)景   1
0.2　“魔法”的好處和危險(xiǎn)   2
0.3　未來(lái)的改變    3
0.4　 為什么說(shuō)Power Query 有“魔力”    5
0.4.1　提取    5
0.4.2　轉(zhuǎn)換    5
0.4.3 加載    6
0.5　 Power Query 和產(chǎn)品體驗(yàn)的整合    7
0.5.1　Power Query 的組件   7
0.5.2　產(chǎn)品體驗(yà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　如何使用本書(shū)   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 章　基礎(chǔ)知識(shí)      13
1.1　開(kāi)始之前    13
1.1.1　調(diào)整Excel默認(rèn)設(shè)置   13
1.1.2　 調(diào)整Power BI 桌面版默認(rèn)設(shè)置   14
1.2　提取    14
1.2.1　選擇數(shù)據(jù)   14
1.2.2　身份驗(yàn)證   15
1.2.3　預(yù)覽窗口   15
1.2.4　查詢處理   16
1.3　轉(zhuǎn)換    17
1.3.1　編輯器   17
1.3.2　默認(rèn)轉(zhuǎn)換   18
1.3.3　源    18
1.3.4　將第 一行用作標(biāo)題   19
1.3.5　更改的類(lèi)型   19
1.3.6　調(diào)整和修改轉(zhuǎn)換   20
1.4　加載    22
1.4.1　設(shè)置數(shù)據(jù)類(lèi)型   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 中啟動(dòng)查詢編輯器   26
1.6.2　在Excel中啟動(dòng)查詢編輯器   27
1.6.3　檢查步驟   27
1.6.4　重新配置步驟   27
1.7　Power Query 的價(jià)值   29
第 2 章　查詢結(jié)構(gòu)設(shè)計(jì)      31
2.1　使用多查詢體系結(jié)構(gòu)   31
2.1.1　對(duì)ETL進(jìn)行分層   31
2.1.2　單個(gè)查詢的好處   31
2.1.3　拆分查詢的好處   32
2.1.4　關(guān)于“暫存”查詢的　　　性能    32
2.2　查詢的引用    34
2.2.1　創(chuàng)建基礎(chǔ)查詢   34
2.2.2　創(chuàng)建查詢的引用   34
2.2.3　查詢依賴關(guān)系樹(shù)的可視化   37
2.2.4　使用Monkey工具查看依賴關(guān)系   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　關(guān)于查詢體系結(jié)構(gòu)的最后思考    47
第3 章　數(shù)據(jù)類(lèi)型與錯(cuò)誤    49
3.1　數(shù)據(jù)類(lèi)型與格式   49
3.1.1　數(shù)據(jù)格式   49
3.1.2　數(shù)據(jù)類(lèi)型   49
3.1.3　如何設(shè)置數(shù)據(jù)格式   52
3.1.4　設(shè)置數(shù)據(jù)類(lèi)型的順序 53
3.1.5　數(shù)據(jù)類(lèi)型的重要性   54
3.2　常見(jiàn)的錯(cuò)誤類(lèi)型   55
3.3　步驟級(jí)錯(cuò)誤    56
3.3.1　數(shù)據(jù)源錯(cuò)誤   56
3.3.2　沒(méi)有找到某列   58
3.4　值錯(cuò)誤    59
3.4.1　發(fā)現(xiàn)錯(cuò)誤   60
3.4.2　無(wú)效的數(shù)據(jù)類(lèi)型轉(zhuǎn)換 61
3.4.3　不兼容的數(shù)據(jù)類(lèi)型   63
3.5　檢查查詢錯(cuò)誤   64
3.5.1　發(fā)現(xiàn)錯(cuò)誤的來(lái)源   64
3.5.2　修復(fù)最初查詢   65
3.5.3　刪除錯(cuò)誤查詢   66
3.6　關(guān)于數(shù)據(jù)類(lèi)型與錯(cuò)誤的最后思考    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　導(dǎo)入查詢    72
4.2.1　僅外部數(shù)據(jù)源   72
4.2.2　數(shù)據(jù)模型的導(dǎo)入   74
4.2.3　導(dǎo)入時(shí)復(fù)制數(shù)據(jù)   75
4.2.4　導(dǎo)入時(shí)保持連接   80
4.3　在工具之間遷移查詢的思考   82
第5 章　從平面文件導(dǎo)入數(shù)據(jù)    84
5.1　了解系統(tǒng)如何導(dǎo)入數(shù)據(jù)   84
5.1.1　設(shè)置系統(tǒng)默認(rèn)值   84
5.1.2　程序如何解析平面數(shù)據(jù) 85
5.2　導(dǎo)入帶分隔符的文件   87
5.2.1　源數(shù)據(jù)文件   87
5.2.2　提取數(shù)據(jù)   88
5.2.3　錯(cuò)誤的解析   88
5.2.4　使用區(qū)域設(shè)置   89
5.3　導(dǎo)入無(wú)分隔符的文本文件   92
5.3.1　連接到文件   92
5.3.2　清理無(wú)分隔符文件   93
5.3.3　按位置拆分列   94
5.3.4　利用查詢中的錯(cuò)誤   96
5.3.5　刪除“垃圾列”   98
5.3.6　合并列   99
5.3.7　通過(guò)分隔符拆分列   99
5.3.8　修剪重復(fù)的空格   100
5.3.9　Power Query 的閃耀時(shí)刻   101
第6 章　從Excel導(dǎo)入數(shù)據(jù)      104
6.1　來(lái)自當(dāng)前工作簿的數(shù)據(jù)   104
6.1.1　連接到表   105
6.1.2　連接到區(qū)域   106
6.1.3　連接到命名區(qū)域   108
6.1.4　連接到動(dòng)態(tài)區(qū)域   110
6.1.5　連接到工作表   112
6.2　來(lái)自其他工作簿的數(shù)據(jù)   112
6.2.1　連接到文件   113
6.2.2　連接到表   114
6.2.3　連接到命名區(qū)域   115
6.2.4　連接到工作表   115
6.3　關(guān)于連接到Excel數(shù)據(jù)的最后思考    118
第7 章　常用數(shù)據(jù)轉(zhuǎn)換    121
7.1　逆透視    121
7.1.1　準(zhǔn)備數(shù)據(jù)   122
7.1.2　逆透視其他列   123
7.1.3　重新透視   124
7.1.4　應(yīng)對(duì)變化   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　追加兩個(gè)表   142
8.1.2　追加額外的表   145
8.2　追加列標(biāo)題不同的數(shù)據(jù)   147
8.3　在當(dāng)前文件中追加表和區(qū)域    148
8.3.1　合并表   149
8.3.2　合并區(qū)域或工作表 153
8.3.3　Excel.Current Workbook   155
8.4　關(guān)于追加查詢的最后思考   155
第9 章　批量合并文件    156
9.1　示例文件背景介紹   156
9.2　過(guò)程概述    157
9.2.1　合并文件的標(biāo)準(zhǔn)流程   157
9.2.2　合并文件的通用架構(gòu)   157
9.3　步驟0：連接到文件夾  159
9.3.1　連接到本地/ 網(wǎng)絡(luò)文件夾   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　標(biāo)準(zhǔn)步驟   163
9.4.2　應(yīng)用于示例場(chǎng)景   163
9.5　步驟2：合并文件  165
9.5.1　標(biāo)準(zhǔn)步驟   165
9.5.2　應(yīng)用于示例場(chǎng)景   165
9.6　步驟3：轉(zhuǎn)換示例文件  167
9.6.1　使用轉(zhuǎn)換示例文件的原因   168
9.6.2　使用轉(zhuǎn)換示例文件的方法   168
9.7　步驟4：通過(guò)主查詢進(jìn)行數(shù)據(jù)清理    171
9.7.1　修復(fù)主查詢中的錯(cuò)誤 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　只用最后幾個(gè)文件以提升速度   176
第 10 章　橫向合并數(shù)據(jù)    179
10.1　合并基礎(chǔ)知識(shí)   179
10.1.1　創(chuàng)建“暫存”查詢   179
10.1.2　執(zhí)行合并   180
10.2　連接類(lèi)型    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　意外問(wèn)題   194
10.4　近似匹配    195
10.4.1　方法   195
10.4.2　示例   196
10.5　模糊匹配    198
10.5.1　基本模糊匹配   199
10.5.2　轉(zhuǎn)換表   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)頁(yè)   205
11.2.1　連接到網(wǎng)頁(yè)   206
11.2.2　自然表和建議表   206
11.2.3　使用示例添加表   207
11.3　連接到?jīng)]有表的頁(yè)面   209
11.4　從Web 獲取數(shù)據(jù)的注意事項(xiàng)    212
11.4.1　收集數(shù)據(jù)的經(jīng)驗(yàn)   213
11.4.2　數(shù)據(jù)完整性   213
11.4.3　解決方案穩(wěn)定性   213
第 12 章　關(guān)系數(shù)據(jù)源      214
12.1　連接到數(shù)據(jù)庫(kù)   214
12.1.1　連接到數(shù)據(jù)庫(kù)   214
12.1.2　管理憑據(jù)   216
12.1.3　無(wú)法連接   217
12.1.4　使用導(dǎo)航器   217
12.1.5　探索數(shù)據(jù)   218
12.2　查詢折疊    221
12.2.1　理解查詢折疊   221
12.2.2　支持查詢折疊的技術(shù)   223
12.2.3　常見(jiàn)問(wèn)題   224
12.3　數(shù)據(jù)隱私級(jí)別   225
12.3.1　聲明數(shù)據(jù)隱私級(jí)別   226
12.3.2　管理數(shù)據(jù)隱私級(jí)別   227
12.3.3　隱私與性能   227
12.3.4　禁用隱私引擎   228
12.4　優(yōu)化    230
第 13 章　轉(zhuǎn)換表格數(shù)據(jù)    233
13.1　透視    233
13.1.1　單列多行   233
13.1.2　多層行標(biāo)題   237
13.1.3　多層列標(biāo)題   239
13.2　逆透視    242
13.2.1　多層行標(biāo)題   242
13.2.2　性能優(yōu)化   248
13.2.3　重構(gòu)   249
13.2.4　保留“null”值   250
13.3　分組    253
13.3.1　占總計(jì)的百分比   253
13.3.2　數(shù)據(jù)排序   254
13.3.3　分組編號(hào)   257
第 14 章　條件邏輯      261
14.1　基礎(chǔ)條件邏輯   261
14.1.1　數(shù)據(jù)集背景   261
14.1.2　連接到數(shù)據(jù)   262
14.1.3　通過(guò)用戶界面創(chuàng)建條件邏輯   262
14.2　手動(dòng)創(chuàng)建IF 判斷   265
14.3　IFERROR函數(shù)   268
14.4　多條件判斷   270
14.5　與上下行進(jìn)行比較   273
14.6　示例中的列   276
第 15 章　值系統(tǒng)      282
15.1　值類(lèi)型    282
15.2　表    283
15.3　列表    284
15.3.1　語(yǔ)法   284
15.3.2　從頭開(kāi)始創(chuàng)建列表   284
15.3.3　將列表轉(zhuǎn)換為表   286
15.3.4　從表列創(chuàng)建列表   287
15.3.5　創(chuàng)建列表的列表   288
15.4　記錄    290
15.4.1　語(yǔ)法   290
15.4.2　從頭開(kāi)始創(chuàng)建記錄 291
15.4.3　將記錄轉(zhuǎn)換為表   291
15.4.4　從頭開(kāi)始創(chuàng)建多個(gè)記錄   292
15.4.5　將多個(gè)記錄轉(zhuǎn)換為表   293
15.4.6　按索引訪問(wèn)表記錄   294
15.4.7　按條件訪問(wèn)表記錄   295
15.4.8　從每個(gè)表行創(chuàng)建記錄   298
15.5　值    300
15.6　二進(jìn)制文件   300
15.7　錯(cuò)誤    301
15.7.1　行級(jí)錯(cuò)誤   301
15.7.2　步驟級(jí)錯(cuò)誤   301
15.8　函數(shù)    302
15.9　關(guān)鍵詞    304
15.9.1　二進(jìn)制（#binary）   305
15.9.2　日期時(shí)間（ #datetime）   306
15.9.3　時(shí)間（#time）   307
15.9.4　持續(xù)時(shí)間（ #duration）   307
15.9.5　類(lèi)型（type）   308
15.9.6　表（#table）   310
第 16 章　理解M語(yǔ)言      314
16.1　M查詢結(jié)構(gòu)   314
16.1.1　查詢結(jié)構(gòu)   315
16.1.2　查詢定義與標(biāo)識(shí)符   316
16.1.3　關(guān)于通用標(biāo)識(shí)符   318
16.1.4　代碼注釋   319
16.1.5　整體效果   320
16.2　理解查詢計(jì)算   320
16.2.1　什么是延遲計(jì)算   321
16.2.2　查詢計(jì)劃   322
16.3　迭代器（逐行計(jì)算）   324
16.3.1　循環(huán)函數(shù)   324
16.3.2　關(guān)鍵詞each和_   324
16.4　其他技術(shù)    328
16.4.1　獲取第 一個(gè)值   328
16.4.2　錯(cuò)誤保護(hù)   330
16.4.3　固定類(lèi)型動(dòng)態(tài)列表 331
16.4.4　自適應(yīng)類(lèi)型動(dòng)態(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)建轉(zhuǎn)換示例   341
17.1.4　創(chuàng)建轉(zhuǎn)換函數(shù)   342
17.1.5　調(diào)用轉(zhuǎn)換函數(shù)   342
17.1.6　更新轉(zhuǎn)換函數(shù)   342
17.1.7　觀察到的規(guī)律   343
17.2　使用參數(shù)構(gòu)建自定義函數(shù)    344
17.2.1　創(chuàng)建文件路徑參數(shù)   345
17.2.2　創(chuàng)建Timesheet轉(zhuǎn)換   346
17.2.3　創(chuàng)建Timesheet函數(shù)   347
17.2.4　更新Timesheet查詢   347
17.3　手動(dòng)構(gòu)建自定義函數(shù)   349
17.3.1　構(gòu)建一個(gè)單一使用場(chǎng)景   350
17.3.2　將查詢轉(zhuǎn)換為函數(shù)   350
17.3.3　從另一個(gè)查詢調(diào)用函數(shù)   352
17.3.4　調(diào)試自定義函數(shù)   353
17.3.5　恢復(fù)函數(shù)功能   355
17.4　動(dòng)態(tài)參數(shù)表   355
17.4.1　動(dòng)態(tài)文件路徑問(wèn)題   356
17.4.2　實(shí)現(xiàn)動(dòng)態(tài)參數(shù)表   357
17.4.3　創(chuàng)建參數(shù)表   357
17.4.4　實(shí)現(xiàn)fnGetParameter函數(shù)功能   358
17.4.5　調(diào)用函數(shù)   359
17.5　參數(shù)表的意義   361
第 18 章　處理日期時(shí)間    362
18.1　邊界日期    362
18.1.1　計(jì)算邊界日期   363
18.1.2　處理財(cái)政年度日期   364
18.1.3　處理364 日型   365
18.2　日期表    367
18.2.1　原子日期表   367
18.2.2　增強(qiáng)日期表   368
18.2.3　財(cái)政日期列   369
18.2.4　全局日期列   369
18.2.5　自定義日期表   371
18.2.6　示例說(shuō)明   373
18.3　日期時(shí)間填充   374
18.3.1　日期級(jí)別填充   374
18.3.2　小時(shí)級(jí)別填充   376
18.3.3　帶間隔的填充   377
18.4　按日期分?jǐn)?  378
18.4.1　起止日內(nèi)按日分?jǐn)?379
18.4.2　起止日內(nèi)按月分?jǐn)?381
18.4.3　在開(kāi)始日期后按月分?jǐn)?  384
18.4.4　關(guān)于分?jǐn)?  386
第 19 章 　查詢優(yōu)化      388
19.1　優(yōu)化設(shè)置    388
19.1.1　全局 數(shù)據(jù)加載   388
19.1.2　全局 Power Query編輯器   388
19.1.3　全局 安全性   389
19.1.4　全局 隱私   389
19.1.5　當(dāng)前工作簿 數(shù)據(jù)加載   389
19.1.6　當(dāng)前工作簿 其他選項(xiàng)   390
19.2　使用緩存    391
19.2.1　強(qiáng)制計(jì)算   391
19.2.2　緩存結(jié)果   393
19.3　處理響應(yīng)滯后   396
19.3.1　優(yōu)化策略   397
19.3.2　體驗(yàn)響應(yīng)滯后   397
19.3.3　重構(gòu)解決方案   399
19.3.4　調(diào)整預(yù)覽數(shù)據(jù)   401
19.4　處理公式防火墻   401
19.4.1　隱私級(jí)別不兼容   402
19.4.2　數(shù)據(jù)源訪問(wèn)   402
19.4.3　重建數(shù)據(jù)組合   402
19.4.4　連接式重構(gòu)   404
19.4.5　展開(kāi)式重構(gòu)   406
19.4.6　傳值重構(gòu)   408
19.4.7　關(guān)于公式防火墻   410
第 20 章　自動(dòng)刷新      412
20.1　Excel自動(dòng)刷新選項(xiàng)   412
20.2　Excel計(jì)劃刷新   412
20.2.1　后臺(tái)刷新   412
20.2.2　每x分鐘刷新一次   413
20.2.3　打開(kāi)文件時(shí)刷新數(shù)據(jù)   413
20.2.4　啟用快速數(shù)據(jù)加載 413
20.3　用宏實(shí)現(xiàn)自動(dòng)刷新   414
20.3.1　刷新單個(gè)連接   414
20.3.2　按特定順序刷新   416
20.3.3　刷新所有查詢   418
20.3.4　同步刷新的問(wèn)題   418
20.4　Power BI 中的計(jì)劃刷新   418