精通 Power Query Master Your Data with Power Query in Excel and Power BI: Leveraging Power Query to Get & Transform Your Task Flow

【作者】肯·普尔斯（Ken Puls） 是加拿大特许专业会计师，微软 Excel MVP。他经营着 Excelguru 咨询公司，并通过 Skillwave培训平台教用户如何使用 Excel 和 Power BI。

 

【作者】米格尔·埃斯科瓦尔 （Miguel Escobar），在编写本书的时候是微软 Power BI MVP，并在巴拿马经营着一家名为 Powered Solutions 的咨询公司。在本书英文版即将出版时，米格尔收到一份令人兴奋的邀请函，即加入微软 Power Query 团队担任项目经理。

 

【译者】BI 佐罗团队，是基于对数据分析的爱好而形成的爱好者社区，活跃成员包括微软MVP、LVMH集团数据分析师、企业CFO等。2015年以来，经过多年发展，团队已经孵化出不同行业的数据咨询类企业，尤其在零售、快消、餐饮、汽车等行业聚集和沉淀了专业的研究成果，且已为50余家企业提供了数字化转型“开箱即用”的落地方案。团队通过“PowerBI战友联盟”的微信公众号、“BI佐罗”的小红书、B站、抖音等平台，持续为社区带来知识分享，也欢迎广大爱好者一起交流讨论。

第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