连续日期展开问题

题1:


如图,将左表的日期范围,展开为连续的日期。

在此之前,我们已经介绍过不少数字类型的展开问题,比如"1-99"展开,我们只需要先将首尾拆分,然后构建list展开即可。
{Number.From(Text.BeforeDelimiter([范围],"-"))..Number.From(Text.AfterDelimiter([范围],"-"))}
逻辑很简单,但是涉及到一些数据类型的转换,一定要细心。
再回来看这个问题,把日期展开,但是日期格式并不能直接构建list,比如{#date(2017,7,1)..#date(2017,7,15)},这个是错误的。
我们知道在excel中,日期即为数字,在Power Query中两者虽然不能直接运算,但也是可以通过函数转换数据类型的。
于是就先要拆分文本,然后把文本转成日期,再把日期转为数字,用数字构建list,最后再把数字还原为日期。

let
    源 = Excel.CurrentWorkbook(){[Name="表2"]}[Content],
    日期 = Table.AddColumn(源, "日期", each List.Transform({Number.From(Date.From(Text.BeforeDelimiter([日期范围],"-")))..Number.From(Date.From(Text.AfterDelimiter([日期范围],"-")))},Date.From)),
    展开 = Table.ExpandListColumn(日期, "日期")
in
    展开

 

题2:

上面是热身,来加大难度。

这回不仅包含日期,还包含时间,而我们知道带时间的日期转为数字后是小数,但是小数又不能构建list,比如{1.5..99},这也是不行的。
但是看下我们要的结果,[开始时间]除了每一个[人员]的第一行是小数,其余的都是整数。[结束时间]除了最后一行是小数,其余的虽然不是整数但是可以通过整数转换为时间后减1秒得到。
所以我们就通过向上向下舍入取整,先去掉首尾,构建整数的list,然后用&连接单独的首尾,得到最终的list。

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    类型 = Table.TransformColumnTypes(源,{{"人员", type text}, {"开始时间", type datetime}, {"结束时间", type datetime}}),
    构建 = Table.AddColumn(类型, "a", each [a={Number.RoundUp(Number.From([开始时间]))..Number.RoundDown(Number.From([结束时间]))},b=List.Transform(List.Zip({{[开始时间=[开始时间]]}&List.Transform(a,each [开始时间=DateTime.From(_)]),List.Transform(a,each [结束时间=DateTime.From(_)-#duration(0,0,0,1)])&{[结束时间=[结束时间]]}}),Record.Combine)][b]),
    删除列 = Table.RemoveColumns(构建,{"开始时间", "结束时间"}),
    展开list = Table.ExpandListColumn(删除列, "a"),
    展开record = Table.ExpandRecordColumn(展开list, "a", {"开始时间", "结束时间"})
in
    展开record

此方法重点在于第三步构建嵌套record的list,其余步骤都是鼠标点点就出来的。当然你也可以分两步走,分别添加两列来构建list。方法不唯一,看你能不能写出更加优化的解法?

附件

8 Replies to “连续日期展开问题”

  1. 日期时间的展开,取得数据后转为数值,乘24×60,取整,构建数列,再除24×60 再转为日期时间,是否可行

  2. let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    已添加自定义 = Table.AddColumn(源, "aa", each Table.FromColumns({ List.Transform({Number.IntegerDivide(Number.From([开始时间]),1)..Number.IntegerDivide(Number.From([结束时间]),1)},
    (x)=>List.Max({[开始时间],DateTime.From(x)})),
    List.Transform({Number.IntegerDivide(Number.From([开始时间]),1)+1..Number.IntegerDivide(Number.From([结束时间]),1)+1},
    (x)=> List.Min({[结束时间],DateTime.From(x-0.00001)}))}
    ,{"开始时间","结束时间"})),
    删除的列 = Table.RemoveColumns(已添加自定义,{"开始时间", "结束时间"}),
    #"展开的“aa”" = Table.ExpandTableColumn(删除的列, "aa", {"开始时间", "结束时间"}),
    更改的类型 = Table.TransformColumnTypes(#"展开的“aa”",{{"开始时间", type datetime}, {"结束时间", type datetime}})
    in
    更改的类型

    哈,第二题我也可以做出来了

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注