Power Query從入門到精通

第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

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