Power Query动态扩展,有用的知识又增加了

很多小伙伴应该都见过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能进行很多更加强大的数据清洗和处理功能,这个以后再聊,谢谢。

图文作者:流浪铁匠

(0)

相关推荐