Power Query：基於Excel 和 Power BI的M函數詳解及應用（簡體書）

Power Query的界面操作只能發揮其全部能力的20%，剩餘80%的能力都要通過M函數來實現。M函數是Power Query專用的函數，使用M函數可以幫助我們自由靈活地完成數據導入、整合、加工處理等任務工作。本書首先通過介紹基本操作讓讀者體驗Power Query的魅力，讓讀者用圖形操作界面也能玩轉數據的整理、清洗和轉換。其後介紹M函數的分類，以及學習的方法，還有最重要的“上下文”概念。本書的第5章會詳細介紹部分M函數的語法、參數及用法，再結合實際案例講解多個M函數的嵌套。本書適用于從事出納、會計、統計、倉管、數據分析等頻繁與數據打交道的工作的人；不想學高級函數、VBA編程等複雜的數據處理功能，但工作中又有需要的人。

李小濤
Excel重度使用者，現任某企業數據分析崗位，同時也從事Excel培訓工作致力於不斷挖掘Excel在工作中的應用，讓身邊每位辦公人士都能高效使用，擅長Excel數據分析，VBA建模，Power BI可視化。

前言

Power Query是一個在Excel中免費使用的“數據提取”和“數據預處理”自動化插件，也是Power BI的一個組件。通過簡化數據發現、訪問和合作的操作，從而增強了商業智能自助服務體驗。
Power Query的神奇功能
Power Query是在Excel平臺控制及轉換數據的最佳工具（沒有之一）。不論你是何種Excel用戶，你可能經常做如下的事務。
數據獲取：從不同源、不同結構中用不同形式獲取數據並按統一格式進行橫向合併，縱向（追加）合併、條件合併等。
數據轉換：將原始數據轉換成期望的結構或格式。
數據處理：為了後續分析的需要進行數據預處理，例如：加入新列、加入新行、處理某些單元格值。
Power Query都可以輕鬆、簡單、高效地完成上述事務。
如果會使用Power Query中強大的M函數，使用幾行簡單的代碼就可以快速實現數據獲取、轉換、處理的工作。
本書主要內容
本書不是M函數的詳盡文檔，主要關注使用率較高的函數，那些不大常用的內容（也就是那些更深奧的內容）就交給讀者去摸索吧。
第1章介紹 Power Query 的數據導入方法、界面操作以及數據的上載方法。
第2章介紹Power Query 裡的幾個常用功能。幫助讀者熟練掌握使用圖形操作命令完成對數據的清洗，為後面學習 M 函數打好基礎。
第3章介紹Power Query的核心M函數。
第4章介紹Power Query中的分支語句和“上下文”概念。
第5章介紹Power Query中部分函數，再結合案例，由淺入深帶大家學習M函數。
學習Power Query我用了一個月時間，相信你們也可以。
感謝
感謝趙興峰老師的推薦，讓我有機會寫這本書。
感謝電子工業出本社編輯王靜老師的信任和石倩老師的認真審稿。
感謝苗旭在寫作經驗上對我的幫助。
感謝吳過、卡卡貓、聆楓者、劉玉超、流浪的鼓勵。
感謝暢心、施陽、朱仕平、曾賢志等大佬的知識分享。

目錄

第1章
Power Query的廬山真面目 1
1.1 初識Power Query 1
1.2 “新建查詢”命令 2
1.3 “查詢編輯器”界面介紹 3
1.4 在“查詢編輯器”內導入數據 8
1.5 用“從表格”命令導入多張數據表 9
1.6 將查詢表上載至Excel 12
1.7 刷新數據 13
第2章
Power Query的基礎操作 15
2.1 添加自定義列 15
2.2 “追加查詢”功能 17
2.2.1 “追加查詢”功能詳解 17
2.2.2 “追加查詢”案例――匯總4張工作表 21
2.2.3 “追加查詢”案例――文件夾匯總 24
2.3 “合併查詢”功能 31
2.3.1 “合併查詢”功能詳解 32
2.3.2 “合併查詢”案例――單條件匹配 35
2.3.3 “合併查詢”案例――多條件匹配 37
2.3.4 “合併查詢”案例――一對多查詢 39
2.3.5 “合併查詢”案例――對比兩列數據的差異 41
2.4 “透視列”與“逆透視列”功能 43
2.4.1 數據的維度 43
2.4.2 “透視列”案例――將一維表轉換為二維表 44
2.4.3 “透視列”案例――文本透視 45
2.4.4 “逆透視列”功能詳解 46
2.4.5 “逆透視列”案例――將二維表轉換為一維表 46
2.5 “分組依據”功能 47
2.5.1 “分組依據”功能詳解 47
2.5.2 “分組依據”案例 48
第3章
M函數入門 51
3.1 Power Query的三大容器 51
3.1.1 List（列表） 51
3.1.2 List擴展 53
3.1.3 List類函數 54
3.1.4 List合併 57
3.1.5 Record（記錄） 58
3.1.6 Record擴展 59
3.1.7 Record類函數 60
3.1.8 Record合併 60
3.1.9 Table（表） 62
3.1.10 Table擴展 64
3.1.11 Table類函數 64
3.1.12 Table合併 67
3.2 深化容器中的元素 68
3.2.1 深化List元素 68
3.2.2 深化Record元素 69
3.2.3 深化Table元素 70
3.2.4 深化多層容器 73
3.3 數據類型 73
3.3.1 自動檢測數據 74
3.3.2 數據類型設置 75
3.4 M函數入門 77
3.4.1 M函數的類別 77
3.4.2 Text類函數 79
3.4.3 Number類函數 80
3.4.4 Time類函數 82
3.4.5 Date類函數 82
3.4.6 DateTime類函數 84
3.5 學習M函數的方法 85
3.5.1 查看所有函數 85
3.5.2 讀懂函數的參數 87
第4章
Power Query中的重要概念 89
4.1 分支語句 89
4.1.1 條件語句：if…then…else… 89
4.1.2 調用“條件列” 92
4.1.3 容錯語句：try…otherwise… 95
4.2 打開M函數的鑰匙――“上下文” 97
4.2.1 Table.AddColumn：表添加列 98
4.2.2 Table.SelectRows：對表進行篩選 99
4.2.3 Table.Distinct：對表刪除重複項 102
4.2.4 Table.RowCount/Table.ColumnCount：計算表行/列數 104
4.2.5 Power Query中的“上下文” 104
4.2.6 自定義參數 106
4.2.7 實戰案例1――國際排名 109
4.2.8 實戰案例2――中國式排名 111
4.2.9 實戰案例3――中國式班級排名 114
4.2.10 實戰案例4――篩選家庭成員信息 115
第5章
Power Query 實戰 118
5.1 M函數的初階運用 118
5.1.1 List.Sum：列表求和 118
5.1.2 List.Max/List.Min：返回列表中的最大/小值 119
5.1.3 Number.From：數字轉換 122
5.1.4 Table.Group：分組統計 124
5.1.5 實戰案例1――計算連續正負數的個數並求和 132
5.1.6 Table.Max /Table.Min：篩選表中最大值/最小值的行 135
5.1.7 實戰案例2――篩選最近一次的記錄 136
5.1.8 實戰案例3――計算每人連續遲到的最大次數 139
5.1.9 Table.Skip：跳過表前幾行 143
5.1.10 Text.Start/ Text.End：從左取值/從右取值 146
5.1.11 Text.Combine：文本合併 148
5.1.12 實戰案例4――合併同部門的姓名 150
5.1.13 實戰案例5――將單詞和翻譯分列顯示 151
5.1.14 Text.From：文本轉換 153
5.1.15 List.Transform：遍歷列表 154
5.1.16 實戰案例5――文件夾匯總 156
5.2 M函數的進階運用 158
5.2.1 Text.Split：文本分割 158
5.2.2 Date.FromText：日期來自文本 160
5.2.3 Date.ToText：日期轉到文本 161
5.2.4 List.RemoveNulls：刪除列表中的null值 162
5.2.5 實戰案例6――在數據中提取日期 163
5.2.6 實戰案例7――求數據中的金額總和 166
5.2.7 List.PositionOf：在列表中查找索引值 171
5.2.8 Record.ToList/Record.FieldValues：返回記錄中的值 174
5.2.9 Table.ColumnNames：返回表的列標題 175
5.2.10 實戰案例8――返回滿足條件的所有區間 176
5.2.11 Number.RoundDown：向下舍入 179
5.2.12 List.Range：取列表中的值 180
5.2.13 Table.FromColumns：表來自列 180
5.2.14 實戰案例9――單列轉多列 182
5.3 M函數的高階運用 185
5.3.1 Table.ToColumns：按列轉換表 185
5.3.2 Table.ToRows：按行轉換表 186
5.33 Table.FromRows：表來自行 187
5.3.4 實戰案例10――添加匯總行和匯總列 189
5.3.5 List.Zip：列表壓縮 192
5.3.6 實戰案例11――計算每個學生獲得第一名的次數 193
5.3.7 Number.Sign：數字符號 197
5.3.8 Text.Contains：判斷字符串中是否包含某值 198
5.3.9 實戰案例12――判斷服裝是齊碼還是斷碼 200
5.3.10 實戰案例13――將多列數據轉換成兩列數據 203
5.3.11 實戰案例14――取特定位置的值 207
5.3.12 List.Select：篩選列表 209
5.3.13 實戰案例15――取包含特定值的信息 211
5.3.14 List.TransformMany：笛卡兒組合函數 213
5.3.15 實戰案例16――製作工資條 218