Power Query M函數應用技巧與實戰

 

目錄

 

 

 

 

配套資源

 

 

第一篇原理篇

 

第1章三大容器的原理

 

1.1Excel版本異同

 

1.2三大容器和深化

 

1.2.1深入理解三大容器

 

1.2.2深入理解深化

 

1.2.3三大容器的創建

 

1.3超級表

 

1.4PQ使用快捷方式

 

1.4.1快速訪問工具欄

 

1.4.2快捷鍵Alt+數字

 

1.4.3PQ快速訪問工具欄

 

1.4.4自定義選項卡

 

1.5PQ界面設置

 

1.5.1查詢設置

 

1.5.2顯示編輯欄

 

1.5.3顯示列質量

 

1.5.4上載設置

 

1.5.5檢測數據類型

 

1.5.6查詢名

 

1.5.7查詢和連接

 

1.5.8進入PQ編輯器

 

1.6約定

 

1.7練習

 

第2章M語言結構

 

2.1M語言簡介

 

2.2M函數語法

 

2.3M函數應用

 

2.4函數智能提示

 

2.5變量

 

2.5.1命名規則

 

2.5.2導航

 

2.5.3步驟和查詢引用

 

2.6PQ編輯區域使用技巧

 

2.6.1在fx編輯欄內換行

 

2.6.2縮放編輯區域

 

第3章M函數基礎

 

3.1查詢所有M函數 

 

3.2聚合函數

 

3.2.1List.Sum()

 

3.2.2List.Average()

 

3.2.3List.Min()

 

3.2.4List.MinN()

 

3.2.5List.Count()

 

3.2.6List.NonNullCount()

 

3.2.7List.Product()

 

3.3空值null

 

3.4關鍵字

 

3.5運算符和標點符號 

 

3.5.1連續的列表

 

3.5.2加減乘除不簡單 

 

3.6if條件語句

 

3.7布爾值

 

3.8邏輯運算符

 

3.9顯示的誤區

 

3.10is和=的用法 

 

3.11try容錯語句

 

3.12#的用法 

 

 

3.13總結

 

第4章M函數傳參原理

 

4.1解構函數

 

4.2List.Transform()

 

4.3表達式

 

4.4each的用法

 

4.5List.Select()

 

4.6each嵌套

 

4.7function的簡化用法

 

4.8PQ技巧

 

4.8.1保存習慣

 

4.8.2語法錯誤解析

 

第二篇實戰篇

 

第5章求和案例學函數

 

5.1動態月份的求和案例

 

5.2Table.AddColumn()

 

5.2.1界面操作

 

5.2.2理解record

 

5.2.3理解第3個參數

 

5.2.4each _的簡寫

 

5.3Record.ToList()

 

5.4Record.FieldValues()

 

5.5List.Skip()

 

5.6案例總結

 

5.7List.First()

 

5.8List.FirstN()

 

5.9求累計金額案例

 

5.10Table.AddIndexColumn()

 

5.11PQ技巧

 

5.11.1界面操作

 

5.11.2快速獲取數據

 

第6章文件導入函數

 

6.1Excel.CurrentWorkbook()

 

6.1.1函數原理

 

6.1.2{［標題=值］}的深化方法

 

6.1.3多超級表導入

 

6.2導入外部文件

 

6.3File.Contents()

 

6.4Excel.Workbook()

 

6.4.1語法結構

 

6.4.2工作簿信息

 

6.4.3導入文件的細節

 

6.4.4導入錯誤問題

 

6.4.5導入的文件是其本身

 

6.5Csv.Document()

 

6.6Text.FromBinary()

 

6.7導入文件夾

 

6.7.1Folder.Files()

 

6.7.2Folder.Contents()

 

6.7.3導入文件夾錯誤問題

 

6.8刷新

 

6.9隱私設置

 

6.10動態路徑

 

6.10.1絕對路徑

 

6.10.2相對路徑

 

6.10.3多文件路徑案例1

 

6.10.4多文件路徑案例2

 

6.10.5超鏈接合集案例

 

6.11PQ技巧

 

6.11.1獲取文件路徑

 

6.11.2復制查詢

 

6.11.3新建組

 

6.11.4表設計

 

6.11.5標題行

 

6.11.6導入空行空列

 

6.11.7定義名稱區域的應用

 

第7章篩選案例學函數

 

7.1Table.SelectRows()

 

7.2Number.IsOdd()

 

7.3List.Contains()

 

7.4List.ContainsAll()

 

7.5笛卡兒積

 

7.6動態篩選姓名案例

 

7.7Text.Contains()

 

7.8PQ快捷鍵

 

第8章同類參數學函數1

 

8.1Table.SelectColumns()

 

8.2動態選擇列案例

 

8.3Table.RemoveColumns()

 

8.4Table.RecorderColumns()

 

8.5Table.RenameColumns()

 

8.6動態命名案例

 

8.7Table.ColumnNames()

 

8.8動態月份列案例

 

8.9record的靈活用法

 

8.10添加多列案例

 

8.11Table.ExpandRecordColumn()

 

8.12Table.ExpandTableColumn()

 

8.13Table.ExpandListColumn()

 

8.14擴展號碼案例

 

8.15［［標題］］的用法

 

8.16參數的常量化

 

8.17let in的靈活用法

 

8.18PQ技巧

 

8.18.1應用的步驟

 

8.18.2PQ刷新失效問題

 

第9章同類參數學函數2

 

9.1List.AllTrue()/List.AnyTrue()

 

9.2List.MatchesAll()/List.MatchesAny()

 

9.3Text.Split()/Text.SplitAny()

 

9.4篩選文件名案例

 

9.5List.Split()

 

9.6Table.Split()/Table.SplitAt()

 

9.7Text.PositionOf()

 

9.8Text.PositionOfAny()

 

9.9List.Positions()

 

9.10List.PositionOf()

 

9.11多IF案例

 

9.12List.PositionOfAny()

 

9.13總結

 

第10章報表案例學函數

 

10.1清洗報表案例

 

10.2Table.Skip()

 

10.3Table.FirstN()

 

10.4Table.PromoteHeaders()

 

10.5Table.Sort()

 

10.6花名冊查詢模板案例

 

10.7獲取最新文件案例

 

10.8PQ技巧

 

10.8.1無法進入PQ編輯器

 

10.8.2引用查詢

 

第11章Date和Number類函數

 

11.1日期的創建

 

11.2Date.From()

 

11.3Date.FromText()

 

11.4Date.Year()

 

11.5Date.ToText()

 

11.6DateTime.LocalNow()

 

11.7日期類函數

 

11.7.1含有Add

 

11.7.2提取信息

 

11.7.3開始和結束

 

11.7.4條件判斷

 

11.7.5From和To

 

11.7.6總結

 

11.8篩選日期區間案例

 

11.9Number.From()

 

11.10Number.FromText()

 

11.11Number.ToText()

 

11.12Text.From()

 

11.13Number.IntegerDivide()

 

11.14Number.Mod()

 

11.15Number.Round()

 

11.16List.Numbers()

 

11.17List.Dates()

 

11.18動態日期模板案例

 

11.19DateTime.ToText()

 

11.20計算年齡和工齡案例

 

11.21計算工作時長案例

 

11.22計算時間戳

 

11.23顯示刷新時間案例

 

11.24日期細節

 

11.25單元格格式

 

第12章合並案例學函數

 

12.1Text.Combine()

 

12.2文本連接案例

 

12.3Record.Combine()

 

12.4List.Combine()

 

12.5Table.Combine()

 

12.6展示所有標題案例

 

12.7合並文件保留文件信息

 

12.8轉換文件查詢

 

12.9總結

 

第13章從From到To

 

13.1Table.ToColumns()

 

13.2Table.FromColumns()

 

13.3Table.ToRows()

 

13.4Table.FromRows()

 

13.5Table.Transpose()

 

13.6多維轉一維案例

 

 

13.7一列轉多列案例

 

13.8縱向求和案例

 

13.9Table.ToRecords()

 

13.10Table.FromRecords()

 

13.11Record.FromTable()

 

13.12Record.ToTable()

 

13.13求最低報價案例

 

13.14Table.TransformRows()

 

13.15Table.ToList ()

 

13.16Table.FromList()

 

13.17Record.FromList()

 

13.18List.Zip()

 

13.19動態命名案例

 

13.20Table.FromValue()

 

13.21Table.Column()

 

13.22Record.Field()

 

13.23動態篩選列名案例

 

13.24總結

 

13.25練習

 

第14章轉換案例學函數

 

14.1Table.TransformColumnTypes()

 

14.2按照順序轉換類型案例

 

14.3Table.TransformColumns()

 

14.4添加前綴案例

 

14.5轉換類型案例

 

14.6匯率轉換案例

 

14.7Text.Upper()類

 

14.8Text.Trim()

 

14.9Text.Clean()

 

14.10Expression.Evaluate()

 

14.11特定列轉換類型案例

 

14.12Table.TransformColumnNames()

 

14.13練習

 

第15章替換案例學函數

 

15.1Table.ReplaceValue()

 

15.1.1界面操作

 

15.1.2語法結構

 

15.2動態求和案例

 

15.3技能等級替換案例

 

15.4模糊替換案例

 

15.5Table.ReplaceErrorValues()

 

15.6Table.SelectRowsWithErrors()

 

15.7錯誤值處理

 

15.8快速顯示錯誤值

 

15.9時間參數案例

 

15.10練習

 

第16章透視和分組

 

16.1透視

 

16.1.1一維表和二維表

 

16.1.2Table.Pivot()

 

16.1.3語法要點

 

16.1.4調查問捲案例

 

16.1.5不要聚合

 

16.2逆透視

 

16.2.1僅逆透視選定列

 

16.2.2逆透視其他列

 

16.2.3逆透視列

 

16.2.4要點： null

 

16.2.5要點： 重復值

 

16.3多行標題案例

 

16.4Table.Group()

 

16.4.1語法

 

16.4.2合並姓名案例

 

16.4.3轉換打印案例

 

16.4.4橫向轉換案例

 

16.4.5添加座號案例

 

16.4.6添加組號案例

 

16.4.7求銷售額占比案例

 

16.5多字段聚合案例

 

16.6Table.Group()局部分組

 

16.7單詞分組案例

 

16.8款項計算案例

 

16.9Table.Group()的特別用法

 

16.10出現次數案例

 

16.11Buffer類

 

16.12排名案例

 

16.13Table.AddRankColumn()

 

第三篇函數篇

 

第17章Table類函數1

 

17.1Table.ReverseRows()

 

17.2List.Reverse()

 

17.3Text.Reverse()

 

17.4Table.Distinct()

 

17.5List.Distinct()

 

17.6List.IsDistinct()

 

17.7判斷是否有重復行案例

 

17.8排序的不穩定性

 

17.9Table.DuplicateColumn()

 

17.10Table.FindText()

 

17.11Table.FirstValue()

 

17.12Table.HasColumns()

 

17.13Table.RowCount()/Table.ColumnCount()

 

17.14Table.FillDown()/Table.FillUp()

 

第18章Table類函數2

 

18.1Table.SplitColumn()

 

18.2橫向分列案例

 

18.3Table.CombineColumns()

 

18.4Table.NestedJoin()

 

18.5Table.Join()

 

18.6Table.FuzzyNestedJoin()

 

18.7Table.RemoveMatchingRows()

 

18.8兩表找不同案例

 

18.9保留重復項

 

18.10兩表找相同案例

 

18.11排除時間案例

 

18.12?和??的用法

 

18.13...的用法

 

18.14PQ技巧

 

18.14.1拆分為行

 

18.14.2刪除重復項

 

18.15練習

 

第19章Text類函數1

 

19.1Text.At()

 

19.2Text.Start()/Text.End()

 

19.3Text.Length()

 

19.4Text.Middle()

 

19.5Text.Range()

 

19.6Text.RemoveRange()

 

19.7Text.ReplaceRange()

 

19.8List.Range()

 

19.9Text.Insert()

 

19.10Text.Replace()

 

19.11Text.PadStart()/Text.PadEnd()

 

19.12提取身份證號信息案例

 

19.13文本轉日期和時間案例

 

19.14雙引號

 

19.15雙開PQ編輯器

 

第20章Text類函數2

 

20.1Character.FromNumber()

 

20.2Character.ToNumber()

 

20.3Text.ToList()

 

20.4半角全角案例

 

20.5清除不可見字符案例

 

20.6Text.Repeat()

 

20.7List.Repeat()

 

20.8Table.Repeat()

 

20.9Text.Select()

 

 

20.10Text.Remove()

 

20.11提取中文案例

 

20.12Text.BeforeDelimiter()

 

20.13Splitter類

 

20.14Combiner類

 

20.15Text.Format()

 

20.16整數值序列

 

第21章List類函數

 

21.1List.Difference() 

 

21.2List.Union() 

 

21.3List.Intersect()

 

21.4列排序案例

 

21.5List.RemoveNulls()

 

21.6List.RemoveItems()

 

21.7List.RemoveMatchingItems()

 

21.8List.IsEmpty()/Table.IsEmpty()

 

21.9刪除空行案例

 

21.10刪除空列案例 

 

21.11List.Alternate()

 

21.12List.Accumulate()

 

21.12.1語法

 

21.12.2一對多文本替換

 

21.12.3多對多文本替換

 

21.12.4奇偶分組

 

21.12.5批量添加列

 

21.12.6總結

 

21.13List.Generate()

 

21.13.1語法

 

21.13.2刪除首字母案例

 

21.14List.TransformMany()

 

21.15Record類

 

21.16練習

 

第四篇擴展篇

 

第22章自定義函數

 

22.1自定義函數簡介

 

22.2內部調用

 

22.3外部調用方法一

 

22.4外部調用方法二

 

22.5as的用法

 

22.6篩選前後n列案例

 

22.7刪除空行空列案例

 

22.8增加空行案例

 

22.9壓縮連續數字案例

 

22.10費用表合並案例

 

22.11逆填充案例

 

22.12總計行案例

 

22.13批量聚合案例

 

22.14神奇的函數名

 

22.15練習

 

第23章Power Query不孤單

 

23.1PQ與VBA

 

23.2PQ與Python

 

23.3PQ與數據透視表

 

23.4數據思維的養成

 

23.5null的用法總結

 

23.6邏輯運算符的優先級

 

23.7復制工作簿查詢

 

23.8Power BI技巧

 

23.9數據保密性

 

23.10設計收集表格案例

 

23.11迭代保留過程案例

 

23.12設計報表技巧

 

第24章獲取其他源

 

24.1獲取網頁表格案例

 

24.2獲取網頁文本案例

 

24.3獲取網頁數據小結

 

24.4獲取Outlook數據

 

24.5獲取PDF數據