动态扩展，舍我其谁

很多小伙伴应该都见过Power Query处理数据的案例,但很多小伙伴其实并不了解Power Query的强大,今天咱们就介绍几个使用函数非常复杂但对Power Query来说很简单的应用：

 

1,货品分箱

如下图所示，要根据B列的订单数和装箱标准，快速按照各种产品的订单数分解到每箱(左图到右图效果)。

这个问题对函数来说还是比较烧脑的，而且由于是数组公式,效率不高。

而对Power Query来说还是很简单的，数据源加载到Power Query后直接添加自定义列：

命名为“分箱”,直接录入自定义公式：

List.Transform(List.Split({1..[订单数]},[装箱标准]),List.Count)

然后把[分箱]列扩展到新行，删除[装箱标准]列。

最后把结果加载到工作表即可，简单粗暴。

2,在数据分析阶段，很多人应该看见过这种类似的数据源。

因为分析原因，我们需要把每个人的相关日期细化成右侧的每日列表：

这题函数解法也比较复杂，而Power Query做会更合适。

添加自定义列，命名为“日期列表”，自定义公式：

（左右拖动查看完整公式）

List.Transform({Number.From([开始日期])..Number.From([结束日期])},Date.From)

然后类似第1个案例的步骤，把[日期列表]列扩展到新行，删除[开始日期]与[结束日期]列后加载到表即可。

(就题论题，这个解法主要是为了说明PowerQuery对数据类型的要求远远高于函数，这个需求对会m的小伙伴来说直接用 List.Dates当然会更适合。

这里的List.Transform+Date.From，是把list的数据类型转回日期，这样才能用于Power Query内后续日期整理和计算。

PowerQuery对数值和日期类型严格区别，如果处理结果只需要加载到excel里就直接改格式为日期即可。

 

3,一个单元格里的数字拆分成列表，对PowerQuery也是容易的事：

对这类效果来说，Power Query的list比函数的数组构造思路简单太多……

添加自定义列，公式为：

Expression.Evaluate('{'&Text.Replace([编号段],'-','..')&'}')

这里只是个相对简单规则的案例，如果是日期或者文本型数字类的扩展，Power Query由于对数据类型的要求极为严格，写法会复杂不少，但还是比函数简单太多。

 

4,按固定字符数添加间隔符

这个需求对很多新手来说使用Power Query鼠标点点就能解决，但是这也是Power Query新手最容易出错的问题之一

最大编码数如果从18位数字更新为21位，图形化操作的结果就会异常(因为过程中产生的拆分的过渡列，而这个列数是固定的，不会因为数据源变化而变化)，就和vba只会录制宏并不能解决全部问题一样。

所以如果你的Power Query是需要刷新数据的，最好还是学下m函数。

这种问题m函数的解法很多，对于精通m函数的大佬们这题能用递归/自定义函数乃至正则等等方法解决，,这里只列两种相对简单的解法：

1,对List类和Text类函数有一定了解的小伙伴能写出这种思路，但由此也会发现Text类函数的局限性，

自定义列，公式为：

Text.Combine(List.Transform(List.Split(Text.ToList([编码]),4),Text.Combine),'')

这里的4修改为指定正整数N，就是按N个字符间隔的效果。1除外，因为m里Text.ToList就是按照单个字符拆分为list(然而其局限就是只能按单个字符)。 

2,被很多人忽略的一类函数：拆分器函数解法，拆分器函数是专门解决这类问题的函数，然而m的函数比工作表函数更多，因此这部分函数经常被忽视。

而多数拆分器函数的能力就是按位置/字符数/(多个)间隔符等等情况，把原字符串拆分为动态的list效果。

自定义列，公式为：

Text.Combine(Splitter.SplitTextByRepeatedLengths(4)([编码]),'')

Splitter.SplitTextByRepeatedLengths这个拆分器函数，是专门把字符串按照指定的字符数拆分为list的。

这也是m函数的一大特色，部分函数名很长，但基本看名称就知道其功能了。然而m之所以普及率不高，就是因为多达700+个m函数(pbi desktop里更多)很多人对其中很多函数不甚了解。

今天介绍的只是Power Query的一些简单应用，熟悉m函数的话Power Query能进行很多更加强大的数据清洗和处理功能，这个以后再聊，谢谢。

图文作者：流浪铁匠