excel高级函数_PowerQuery 进阶之 M 函数学习

首页 > excel高级函数_PowerQuery 进阶之 M 函数学习

excel高级函数_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 thentry otherwise三、M函数学习手册M函数学习网站在添加自定义列的对话框下方有一行蓝色的字「了解 Power Query 公式」，这其实是一个网页链接打开以后可以知道微软官方 Power Query M 函数文档。