Power Query 從入門到精通

徐鵬
微軟最有價值專家MVP、微軟公共論壇版主、51CTO特級講師，原微軟中國有限公司工程師，
曾為中廣核、華為、比亞迪、南航、台積電等中大型企業提供培訓及顧問諮詢服務。
先後就職於宏碁（中國）有限公司、微軟（中國）有限公司。
為全球500強企業提供各類業務的優化服務及顧問諮詢服務，
精通數據分析方法論及Windows系統的自動化運維。

第1章走入Power Query的世界
1.1Excel處理數據的缺點2
1.2Excel的Power數據處理組件3
1.3Power Query組件5
1.3.1Excel的Power Query組件8
1.3.2Power BI的Power Query數據導入界面10
1.4Power Query編輯器11
1.4.1Excel的Power Query編輯器
界面12
1.4.2Power BI的Power Query編輯器
界面14
1.4.3Excel在Power Query數據處理中的優勢17
1.4.4Power BI在Power Query數據處理上的優勢18
1.5Power Query的底層語言——M語言21
1.6本章總結24
第2章Power Query的數據集成
2.1Power Query的CSV/TXT 數據集成26
2.1.1Excel 導入CSV數據26
2.1.2Power BI導入CSV數據30
2.2Power Query的Excel數據集成32
2.2.1Excel中的Excel數據導入與
集成32
2.2.2Power BI中的Excel數據導入與
集成34
2.3Excel 導入當前表格中的數據35
2.4XML格式數據集成37
2.4.1Excel 實現XML 數據的集成37
2.4.2Power BI 導入XML格式數據40
2.5JSON格式數據集成42
2.5.1Excel實現JSON數據集成43
2.5.2Power BI實現JSON數據集成45
2.6文本格式數據集成46
2.6.1Excel以文本格式導入數據47
2.6.2Power BI以文本格式導入數據48
2.7Power Query的SQL Server數據集成50
2.7.1Excel獲取SQL Server全部
數據53
2.7.2Excel獲取T-SQL 篩選後的
數據54
2.7.3Power BI以導入方式獲取SQL
數據56
2.7.4Power BI採用Direct Query獲取SQL數據58
2.8Power Query導入Web數據61
2.8.1Excel 實現Web的數據獲取62
2.8.2Power BI實現Web的數據獲取65
2.9Power Query 導入Web API數據70
2.9.1Excel 實現Web API數據獲取71
2.9.2Power BI實現Web API數據獲取72
2.10Power Query導入MySQL數據74
2.10.1Excel 實現MySQL 數據獲取75
2.10.2Power BI 實現MySQL數據
獲取77
2.11Power Query導入文件夾數據79
2.11.1Excel提取文件夾所有文件
數據79
2.11.2Power BI提取文件夾的所有
數據81
2.12Power BI中的數據流服務84
2.12.1Power BI Pro構建數據流服務84
2.12.2Power BI Desktop引用數據流
服務87
2.13數據源訪問的權限管理88
2.14本章總結90
第3章Power Query和M語言
3.1什麼是M語言92
3.2M語言支持的基本數據類型96
3.3M語言支持的組合數據類型99
3.4Power Query的M語言結構101
3.5Power Query中M語言的智能提示105
3.6Power Query的M語言變量107
3.7Power Query的M語言參數109
3.8Power Query的M語言流程處理114
3.9Power Query的M語言的錯誤處理116
3.10Power Query的M語言嵌套119
3.11Power Query的M語言操作符120
3.12M語言的註釋130
3.13本章總結131
第4章Power Query實現數據的清洗和重構
4.1數據清洗遵從的原則133
4.2數據清洗後的操作134
4.3數據清洗和重構具體操作134
4.4Power Query實現數據類型的轉換135
4.4.1Excel中數據類型轉換135
4.4.2Power BI數據類型轉換137
4.5Power Query實現列的刪除138
4.6Power Query 實現行的刪除139
4.7Power Query保留行操作146
4.8Power Query的數據篩選150
4.9Power Query添加數據列156
4.10Power Query按列分列164
4.11Power Query按列分行172
4.12Power Query的數據轉置175
4.13Power Query數據替換176
4.14Power Query數據列交換179
4.15Power Query的數據排序179
4.16Power Query時間表的構建181
4.17本章總結186
第5章Power Query實現數據合併操作
5.1Power Query數據多重合併操作188
5.2Power Query數據追加合併操作189
5.3Power Query數據橫向合併192
5.4數據合併連接關係說明195
5.5Power Query處理缺失值203
5.6Power Query實現數據的分組207
5.7Power Query實現數據的透視217
5.8Power Query實現數據的逆透視219
5.9本章總結220
第6章Power Query查詢連接的分享與刷新
6.1Power Query數據連接的分享與重用222
6.2Excel中的Power Query連接與復制222
6.3Power Query連接的導出與導入226
6.4Power Query 的數據刷新229
6.4.1Excel的刷新功能230
6.4.2Power BI的刷新功能238
6.5本章總結247
第7章Power Query的函數
7.1Power Query函數的獲取與使用249
7.2Power Query文件系列訪問函數259
7.2.1CSV文件解析260
7.2.2Excel 文件內表解析261
7.2.3引用第三方Excel數據263
7.2.4XML文件解析263
7.2.5JSON文件解析264
7.3數據庫訪問函數265
7.3.1Access 數據庫訪問265
7.3.2SQL Server 數據庫訪問266
7.3.3MySQL數據庫訪問266
7.4Web訪問函數267
7.4.1Web.Contents函數267
7.4.2Web.Page 函數267
7.4.3Web.BrowserContents 函數268
7.4.4Html.Table 函數268
7.5Power Query文本處理函數269
7.5.1Text.Length計算字符串長度269
7.5.2Text.Insert插入字符串270
7.5.3Text.From 將其他數據類型轉換為
文本類型270
7.5.4Text.Format 設置文本輸出格式271
7.5.5Text.Tolist 將文本轉換為列表271
7.5.6Text.Start截取字符串中前面的
字符272
7.5.7Text.Select刪除不需要的字
符串272
7.5.8Text.Middle 截取中間部分字符273
7.5.9Text.End截取從設定位置到結尾的字符273
7.5.10Text.Range 獲取字符串範圍
數據274
7.5.11Text.Replace 替換特定數據274
7.5.12Text.ReplaceRange替換區域
數據274
7.5.13Text.Repeat重複文本275
7.5.14Text.Combine合併文本275
7.5.15Text.Trim刪除前後特定字符276
7.5.16Text.Padstart 佔位符填充277
7.5.17Text.Split字符串分隔278
7.5.18Text.BeforeDelimiter獲取分隔符前的數據278
7.5.19Text.AfterDelimiter獲取分隔符後的數據279
7.5.20Text.BetweenDelimiters獲取分隔符中間值280
7.6Power Query列表處理函數280
7.6.1List.Accumulate列表累加器
計算281
7.6.2List.Range獲取列表區域281
7.6.3List.Average計算列表平均數282
7.6.4List.Sum對列表求和282
7.6.5List.Combine合併列表283
7.6.6List.Count統計列表元素283
7.6.7List.Dates 創建日期列表284
7.6.8List.LastN獲取列表最後N個
元素284
7.6.9List.MaxN 獲取最大數據285
7.6.10List.Numbers 生成數值列表286
7.6.11List.Product列表元素乘積287
7.6.12List.Random生成隨機數列表287
7.6.13List.RemoveFirstN刪除列表前面N個數值288
7.6.14List.RemoveItems 刪除列表項288
7.6.15List.RemoveRange刪除列表區間
數值289
7.6.16List.Repeat重複列表數據290
7.6.17List.ReplaceRange 替換列表
區間290
7.6.18List.Select篩選列表291
7.6.19List.Skip實現列表行跳躍291
7.6.20List.Sort列表排序292
7.6.21List.Split列表分割292
7.6.22List.Union對列表非重複數據進行合併293
7.6.23List.Generate生成列表293
7.6.24List.Zip 列表提取與組合294
7.7Power Query記錄處理函數295
7.7.1Record.AddField 添加記錄字段
功能295
7.7.2Record.Combine 記錄連接295
7.7.3Record.FromList將列表轉換
記錄296
7.7.4Record.RemoveFields刪除字段296
7.7.5Record.SelectFields 選擇記錄297
7.7.6Record.Tolist將記錄轉換為
列表297
7.8Power Query表處理函數298
7.8.1Table.AddColumn在表中添加併計算列298
7.8.2Table.AddIndexColumn為表添加索引列299
7.8.3Table.AddJoinColumn添加嵌
套表299
7.8.4Table.AlternateRows行跳躍300
7.8.5Table.Combine合併數據表301
7.8.6Table.DemoteHeaders實現表標題
降級301
7.8.7Table.Distinct獲取表唯一值302
7.8.8Table.ExpandListColumn擴展
列表303
7.8.9Table.ExpandRecordColumn 擴展記錄列304
7.8.10Table.ExpandTableColumn擴展表數據304
7.8.11Table.FindText 查找內容305
7.8.12Table.FirstN 獲取前面的表
數據305
7.8.13Table.Group對錶數據聚合307
7.8.14Table.Join進行表連接307
7.8.15Table.LastN獲取表最後幾行
數據308
7.8.16Table.MaxN求表中最大的N個
數據309
7.8.17Table.MinN求表中最小的N個
數據310
7.8.18Table.PromoteHeaders將第一行提升為標題行311
7.8.19Table.Range選擇區域行312
7.8.20Table.RemoveColumns 刪除列313
7.8.21Table.RemoveFirstN刪除表前面
的行313
7.8.22Table.RemoveLastN刪除表後面
的行314
7.8.23Table.Repeat實現表行重複315
7.8.24Table.ReplaceRows 替換數
據行316
7.8.25Table.ReplaceValue替換數
據值317
7.8.26Table.SelectRows 篩選數據318
7.8.27Table.Skip實現表的行跳躍318
7.8.28Table.Sort對錶排序319
7.8.29Table.Transpose互換行列320
7.9Power Query URL處理函數321
7.9.1Uri.BuildQueryString構建URL訪問地址參數321
7.9.2Uri.Combine合併URL訪問
地址321
7.10Power Query數據合併函數322
7.10.1Combiner.CombineTextByDelimiter 以分隔符方式合併字符322
7.10.2Combiner.CombineTextByEachDe-limiter按順序分隔符合併文本323
7.11Power Query數據分割函數324
7.11.1Splitter.SplitTextByDelimiter按分隔符拆分數據324
7.11.2Splitter.SplitTextByEachDelimiter按分隔符列表拆分數據324
7.11.3Splitter.SplitTextByLengths按照長度拆分數據325
7.11.4Splitter.SplitTextByRepeatedLength按字符長度重複拆分325
7.11.5SplitTextByPositions按位置
拆分326
7.12Power Query日期時間函數327
7.12.1Date.Day獲取日期部分327
7.12.2Date.DayOfWeek求取日期位於一周中的第幾天327
7.12.3Date.DayOfYear求取日期位於一年中的第幾天328
7.12.4Date.DaysInMonth求取日期所在月份的天數328
7.12.5Date.FromText將文本生成
日期328
7.12.6Date.AddDays日期的加減329
7.12.7Date.AddMonths月度加減
運算329
7.12.8Date.AddYears年度加減運算330
7.12.9Date.ToText將日期轉換為
文本330
7.12.10Date.ToRecord將日期轉換為
記錄331
7.12.11DateTime.LocalNow獲取當前
時間331
7.13本章總結331
第8章Power Query的自定義函數
8.1從零開始構建自定義函數334
8.1.1無參數自定義函數構建335
8.1.2有參數自定義函數構建336
8.2基於數據結果建立函數337
8.3執行並獲取自定義函數結果340
8.4自定義函數提取文件夾內所有請假
數據341
8.5自定義函數獲取基金即時淨值348
8.6本章總結353
第9章Power Query與Python
9.1Power Query調用Python的前置
條件355
9.2Python環境的安裝與部署355
9.3Power BI 啟用Python 支持358
9.3.1Power Query執行Python驗證358
9.3.2Power Query結合Python生成中文詞云361
9.4本章總結365
第10章Power Query數據綜合應用案例
10.1身份證信息的初步導入368
10.2身份證號碼數據初步清洗369
10.3身份證數據二次清洗373
10.4非中文姓名數據清洗375
10.5本章總結377
附錄Power Query 簡單案例處理378