最近遇到个从系统中导出的报表,将不同时间段的天成本分别作了汇总,即多个时间段,对应多个天成本,如下图所示:
其中多个时间段用逗号分隔,每一个时间段斜杠分割起止,而天成本用斜杠分割。
为了便于后其他的分析,需要对其进行拆分对应处理。比如,要算总成本时,即需要拆分对应相乘后再求和。
而这个问题在Power Query里处理也并不难,首先按分步骤的方式实现,后面再将多个步骤合成一个,体现其中的处理思路,同时也作为let...in...用法的一个例子。
Step-01 分拆时间段
这个很简单,用函数Text.Split即可,如图所示:
Step-02 对时间段计算时长
由于上一步骤拆出来的是多个(当然也可能只有1个)时间段的列表,所以是一个列表批量操作的问题,自然会用到List.Transform函数(不了解的朋友可以参考文章《用Power Query轻松批量抓取A股数据,及列表转换函数(List.Transform)的使用》)。
而对于每一个时间段,需要提取斜杠分隔符前后的内容(Text.AfterDelimiter和Text.BeforeDelimiter),转换为日期(Date.From)后进行相减得到时长,通过Duration.TotalDays函数得到时长天数后加1(按实际处理需要)即可。
具体如下:
List.Transform([sjd],
(x)=> Duration.TotalDays(
Date.From(Text.AfterDelimiter(x,"/"))
- Date.From(Text.BeforeDelimiter(x,"/"))
)+1
)
Step-03 拆分天成本
这个也很简单,直接用Text.Split函数即可。
Step-04 配对、乘积并求和
这一步稍微复杂,因为涉及到时长和天成本两个列表对应位置的内容求乘积,所以,我们可以先考虑将两个列表的内容一一对应组合到一起,这个我在文章《看了这个例子,一辈子记住这个有趣的函数,以后给内容配对就有思路了》讲过List.Zip这个函数。
而通过List.Zip将连个列表的内容合到一起后,又可以通过List.Transform函数对其中的每一对进行乘积,最后通过List.Sum函数求和即得结果,如下所示:
List.Sum(
List.Transform(
List.Zip({[ts],[tcb]}),
(x)=>x{0}*Number.From(x{1})
)
)
上面通过拆解的方式实现了内容的拆分、配对乘积并求和,而实际上,这几个过程,实际就是PQ的几个步骤串起来,而对于PQ的步骤要串起来,实际就是通过let...in...的关键字来完成的,我们点开“高级编辑器”就可以看到。也可以参考下面关于PQ工作原理的视频:
对于本例,我们只要给其中的每一个过程起个相应的步骤名称,就可以在后续的过程里进行引用,于是,可以直接在添加自定义列的公式里,直接通过let...in...的方式一次性将前面的4个步骤(公式)组合到一起,如下所示(图中公式不全,请参考图后面代码):
let sjd = Text.Split([时间段],","),
ts = List.Transform(
sjd,
(x)=>Duration.TotalDays(
Date.From(Text.AfterDelimiter(x,"/"))
-Date.From(Text.BeforeDelimiter(x,"/"))
)+1
),
tcb = Text.Split([天成本],"/"),
zje = List.Sum(
List.Transform(
List.Zip({ts,tcb}),
(x)=>x{0}*Number.From(x{1})
)
)
in zje
通过这个例子,大家可以体会一下如何将一个相对复杂的问题分解为一个个小步骤,然后再按需要组合成一步完成的思路和方法——而问题的分解,是解决复杂问题的基础和关键。
【近期热门合集/文章】
联系客服