M语言之编写M公式的两大“法宝”

“乾坤挪移”大法

除了【添加列】选项卡下的功能以外，【自定义列】还可以实现很多命令按钮无法直接实现的功能。但是初学者经常会遇到这样的困惑：面对海量M函数，不知道什么情况下“找谁帮忙”。不要紧，有专门解决此困惑的“乾坤挪移”大法，分两步走：先获取公式，从选项卡下找到相关的命令进行操作，再移为己用，将由此操作步骤自动生成的M公式“挪移”到【自定义列】中。

比如需要新生成一列，这一列里每一个数都是“数量”列的总计。可是计算总计该用哪个M函数，其中的参数又该如何使用，这些都是未知的，这时，此“乾坤挪移”大法就可以“发威”了。

获取公式：如图 4-9 所示，选取“数量”列，单击【转换】选项卡下的【统计信息】下拉按钮，在下拉选项中选择【求和】，然后复制在【编辑栏】里自动生成的M公式（复制的内容不包括最前面的等号）。

要点提示：求和，统计最小值、最大值、平均值等

转换成一个值：【Power Query编辑器】→选取数值对象→【转换】→【统计信息】→【求和】或【最小值】或【最大值】或【平均值】

计算被选取多列中每行的值：【Power Query编辑器】→选取多列数值对象→【添加列】→【统计信息】→【求和】或【最小值】或【最大值】或【平均值】

移为己用：删除“计算的总和”这一步骤，单击【添加列】选项卡下的【自定义列】按钮，将刚才复制的M公式粘贴到【自定义列】对话框中自定义列公式的等号后，输入新列名“数量总计”后单击【确定】按钮，求和的新列就生成了，如图 4-10 所示。

再回过头来看这个用于求和的M函数“List.Sum”的参数。“[数量]”很好理解，就是对“数量”列数据的引用，用这个M函数的目的就是对“数量”列进行求和。只是在“[数量]”前面还多了一个“已添加自定义”，这又是什么呢？原来，在使用某些M函数时，不仅需要确定引用的列，还要确定引用的查询表，而“已添加自定义”则是整个查询操作中的一个步骤，意思是，这个M函数引用的是“已添加自定义”这一步骤所生成的查询表里的“数量”列。

如果在【应用的步骤】里将“已添加自定义”这一步骤删除，会产生什么样的影响呢？本着“折腾出真知”的原则，可以尝试一下。

“已添加自定义”是一个中间步骤，删除时“异空间”会弹出一个对话框来询问是否确定要删除。

有些中间步骤的删除确实会影响后续步骤，不过这里不用担心，单击【删除】按钮即可。将这一步骤删除以后再来看这个M公式，会发现唯一的变化就是其中的参数自动变成了前一个步骤的“添加打折列”，公式运行的结果没有受到半点影响，如图 4-11 所示。

最后，将“已添加自定义 1”这个步骤的名称修改成更具描述性的“添加数量总计列”，才更符合规范。

“依葫芦画瓢”法

“List.Sum”是一个比较简单的M函数，再来看一个稍微复杂一点的M函数“Table.TransformColumns”，它的功能是对指定列进行各种转换操作。【转换】选项卡下的很多操作所生成的M公式里都有它的身影。

例如，要为“品名”列里的内容添加统一前缀“新品”，单击【转换】选项卡下的【格式】下拉按钮，在下拉选项中选择【添加前缀】，在弹出的对话框里输入“新品”，然后单击【确定】按钮。这时从【编辑栏】里就可以看到图 4-12 所示的M公式。

这个M公式里的第一个参数很好理解，就是“添加数量总计列”这一步骤所生成的查询表。第二个参数就比较有意思了，居然有两对大括号，这是在为同一参数里的子参数划定“势力范围”。

M函数“Table.TransformColumns”只有两个参数，然而一个查询表里有那么多列，每列的转换又有那么多种类，光靠第二个参数实在说不清楚，所以就加了大括号，而且还是两层，大括号里的就是子参数。里层的大括号里是对每列进行转换的具体处理手段，有三个子参数：第一个子参数是外加一对半角引号的“品名”列列名，表示转换对象是“品名”列；第二个子参数就是具体的操作，这里是添加前缀，使用了连接符“&”，将前缀“新品”和这一列中的原始内容进行连接；第三个子参数是为转换后的列设置数据类型，“text”是文本类型。此M公式中包含了“each”和“_”的结构，“each”可以理解成“每一个”，而“_”是一种省略写法，在这里表示“品名”列里每一个具体的值。

如此看来，里层大括号中的内容可以理解成，对“品名”这一列里的每一个值前面连接一个“新品”，生成的结果为文本类型。

图 4-13 展示了M函数“Table.TransformColumns”的各参数。

在理解了这个M函数各参数的含义以后，如果需要改变或增加其功能，只要在已有结构的基础上，按照语法规则“依葫芦画瓢”对其进行修改即可。比如，想要用一个M公式同时实现对品名添加前缀和将“单价”列里的数据全部加 20%两个功能，就可以如图 4-14 所示，将公式修改如下：