PowerQuery 进阶之 M 函数学习

之前我们学习 Power Query 都是用鼠标就完成了很多复杂的操作。虽然 PowerQuery 已经将大部分常用功能内置成到功能区。基本能完成我们大部分的报表自动化功能。

但是总有些复杂的或者个性化的问题是开发团队没有预先想到的，这时我们就需要学习 M 语言。

一、M 语言在哪里？

M语言的函数公式有三个地方可以进行查看和编写。

自定义列

我们上一篇文中已经开始接触到 M语言，新建自定义列时使用了 M 语言的语法和一些 M 函数。我们在自定义列的对话框中需要编写 M语言公式。

除了上一篇文中讲到的将两列相加，以为我们还可以使用以下语法对两列进行常规的运算

可以看到常规的四则运算符在 Excel 与 PQ 中是共用的，但是公式书写有所区别。PQ 中以列名称作为参数进行运算，而且列名以中括号 [ ] 包围。

公式编辑栏

当我们完成新建自定义列，以后可以在 PQ 的公式编辑栏里面看到该步骤对应的 M函数。

新建列使用了 Table.AddColumn 函数，而我们在自定义列对话框中设置设置的列名和输入的公式，其实就对应了该函数的第一（新列名）和第三个（列生成方式）参数。

Table.AddColumn 的语法如下：

Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional columnType as nullable type) as table

高级编辑器

接触过 VBA 自动化的都知道，Excel 提供了宏录制功能。而宏录制下来的代码就是 VBA 代码。

M 函数也有类似的功能。在 PQ 界面上的所有操作都被录制成 M函数记录成代码在视图的高级编辑器中。

「视图」>「高级编辑器」

二、M语言基础及常用函数

PowerQuery 的 M 函数和 Excel 函数公式有很大的不同，以下 3 点是大部人都很容易混淆的。

• M 函数大小写敏感，Excel 对大小写不敏感。
• PQ 行号以 0 为基数，Excel 行号以 1 为基数。
• PQ 对数据类型严格要求，Excel 中数据转换是隐式的。

第一个不同点在 PQ 还没有函数智能填充功能（IntelliSense）时尤其让人头疼，最新版的 PQ查询编辑器提供了函数自动填充功能，实现了和 Excel 一样方便的函数输入体验。

PQ 和 Excel 中最大的不同是，你看到的每一个单元格里面不仅仅只能存储一个特定类型的值，而是可以储存二进制文件、表、记录、列表等 PQ 对象。也正是这种特性，让 PQ 的功能更强大。当然也因此带来了一定的学习难度。

• 表在 PQ 中显示为 Table，这种数据类型中包含了一整个表的内容
• 表名称和一行内容称为Record，用中括号[]表示,比如= [Name="Ken Puls", Country="Canada"]
• 表的一列称为 List，用大括号表示{}，比如 = {"Puls,Ken","Escobar,Miguel"}

M 函数和 Excel 函数比起来用法和书写也有很大的区别。M 函数一般会包含两个部分，第一部分通常表示函数所在的类别，第二部分通常表示函数的功能。

比如我们之前学过的 Excel.Workbook(),同样的函数还有 Csv.Document()、Web.Page()等。

常用M函数

List 类函数

List.Sum 对列表求和

List.Max 对列表求最大值

List.Min 对列表求最小值

List.Count 对列表计数，和 Excel 的 count 函数一致

Text 类函数：

Text.Start 类似 Excel 中的LEFT，提取字符串前 N 个字符

Text.End 类似 Excel 的RIGHT ，提取字符串后 N 个字符

Text.Contain 类似 Excel 中的 FIND 函数，查找指定字符串

Text.Select 用来提取指定的字符或者字符列表

Text.Remove 删除指定字符或者字符列表

类型转化函数

Date.ToText 将日期格式数据转换成文本

Time.ToText 将时间格式数据转换成文本

Number.ToText 将数值格式数据转换成文本

条件判断

if else then

try otherwise

三、M函数学习手册

M函数学习网站

在添加自定义列的对话框下方有一行蓝色的字「了解 Power Query 公式」，这其实是一个网页链接打开以后可以知道微软官方 Power Query M 函数 文档。

打开链接以后我们可以看到所有的 M 函数列表，并且对每个函数的语法和参数都进行了很好的解释说明。这是我们学习 M 函数的一个好地方。

M函数列表

另一种查看M函数列表的方法是新建一个空查询，在公式编辑栏中输入 =#shared，按下回车。

这时就能看到所有的 M 函数列表了、点击想要查看的函数，在下方就可以看到关于该函数的语法和参数注释了。

End.爱数据网专栏作者：JaryYuan专栏名称：Power BI 自动化与可视化专栏简介：Excel、Power BI、Python等学习交流园地。Excel黑科技挖掘，Python小技巧。致力于办公自动化、职场效率提升、数据分析及可视化设计个人公众号：JaryYuan