PowerQuery 如何获取起止日期内包含的月份？

披头

发布于 2020-05-13 20:59:47

2.2K0

发布于 2020-05-13 20:59:47

文章被收录于专栏：datartisan

今天在群里看到一个问题,如何获取起始日期和结束日期之间包含的所有月份，业务逻辑见下图：

模拟数据如下：

问题关键在于生成两个区间内的数据，Power Query 最适合干这种活 {1..6} 就可以生成1 2 3 4 5 6 等一系列的数。将起始月份作为大括号的第一个参数，将结束月份作为大括号的第二个参数，即可得到中间的月份，还要注意，两个参数都要求是数字。

首先转换数据类型为日期

= Table.TransformColumnTypes(源,{{"begin_time", type date}, {"end_time", type date}})

添加自定义列

= Table.AddColumn(更改的类型, "月份展开", each {Date.Year([begin_time])*100+Date.Month([begin_time])..Date.Year([end_time])*100+Date.Month([end_time])})

展开自定义列

= Table.ExpandListColumn(已添加自定义, "月份展开")

本以为到这里就可以结束了，实际操作遇到了问题，详见下图：

居然出现了201488这样的月份，仔细想一下，201488确实位于数字201411和201501之间，这可怎么办呢？数字确实存在88这样的，但是他又确实不是月份，好像陷入了一个死循环，又想了一会儿，突然灵光一现，既然这些数字有的是月份，有的不是月份，那就看看月份的规律，把月份挑出来就好了，月份不就是1 2 3 4 5 6 7 8 9 10 11 12么，咱把这个数字按照100取模，得到余数，然后筛选出1 2 3 4 5 6 7 8 9 10 11 12就好了

取模

= Table.AddColumn(#"展开的“自定义”", "取模", each Number.Mod([月份展开], 100), type number)

过滤余数

= Table.SelectRows(插入的取模, each ([取模] = 1 or [取模] = 2 or [取模] = 3 or [取模] = 4 or [取模] = 5 or [取模] = 6 or [取模] = 7 or [取模] = 8 or [取模] = 9 or [取模] = 10 or [取模] = 11 or [取模] = 12))

删除多余列

= Table.RemoveColumns(筛选的行,{"取模"})

至此，就得到了下图数据，加载到excel就完事了

完整M代码

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    更改的类型 = Table.TransformColumnTypes(源,{{"begin_time", type date}, {"end_time", type date}}),
    已添加自定义 = Table.AddColumn(更改的类型, "月份展开", each {Date.Year([begin_time])*100+Date.Month([begin_time])..Date.Year([end_time])*100+Date.Month([end_time])}),
    #"展开的“自定义”" = Table.ExpandListColumn(已添加自定义, "月份展开"),
    插入的取模 = Table.AddColumn(#"展开的“自定义”", "取模", each Number.Mod([月份展开], 100), type number),
    筛选的行 = Table.SelectRows(插入的取模, each ([取模] = 1 or [取模] = 2 or [取模] = 3 or [取模] = 4 or [取模] = 5 or [取模] = 6 or [取模] = 7 or [取模] = 8 or [取模] = 9 or [取模] = 10 or [取模] = 11 or [取模] = 12)),
    删除的列 = Table.RemoveColumns(筛选的行,{"取模"})
in
    删除的列

如上是Power Query的解法，不知道有没有小伙伴可以用ACCESS实现。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2020-05-08，如有侵权请联系 cloudcommunity@tencent.com 删除

excel

本文分享自乐享数据8090 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

excel

登录后参与评论

0 条评论

热度