Power Query支持数据模型,可以将多种数据添加到数据模型,然后建立关联关系并进行后续分析。有些时候会使用类似于数据仓库星形模型的方式进行关联,这里常用的两类表为维度表和事实表。维度表是维度属性的集合,是分析问题的一个窗口;事实表通常是数据仓库结构中的中央表,它包含联系事实与维度表的数字度量值和键。更详细的内容请参阅相关资料。
维度表从概念上理解似乎有些抽象,接下来举个例子。Excel工作表销售数据如下图所示。
创建数据透视表并添加切片器,如下图所示,切片器中可以按时间维度的不同层级(日,月,季度,年)进行切片,完成数据筛选。
如果组数据分析时需要对数据进行更多层次的聚合,那么使用数据模型中的数据表和时间维度表关联是个不错的选择。时间维度表其实并不复杂,典型的时间维度表如下图所示。
对于Excel的重度使用者来说,用公式创建这样的时间维度表也不需要太高深的水平,基本上日期函数+字符串函数就够用了。这次咱们剑走偏锋,使用Power Query来创建时间维度表,在实战中学习更多的PQ知识点。
创建时间维度表整体可以分为两步:
- 创建日期序列:对于上图中的日期序列,当然可以从工作表导入到PQ,既然是用PQ实现,那么我们全部在PQ编辑器中实现。
- 创建后续其他更多的列
步骤1:依次单击【数据】选项卡=>【新建查询】=>【从其他源】=>【空白查询】
步骤 2:在PQ对话框的公式编辑框中输入= #date(2018,1,1)
并按回车键。
在窗口中可以看到输入的日期,如下图所示。
打开【高级编辑器】可以查看PQ公式,如下图所示。
为了便于后续的引用修改PQ公式如下。
let
FirstDate = #date(2018,1,1)
in
FirstDate
时间维度表中日期范围通常都是多个日期,那么如何输入呢?已经有了起始日期,接下来编辑PQ公式输入截止日期。
步骤3:打开【高级编辑器】修改PQ公式,添加截止日期。
let
FirstDate = #date(2018,1,1),
LastDate = #date(2019,12,31)
in
LastDate
结果如下图所示。
步骤4:打开【高级编辑器】修改PQ公式,生成日期序列列表。
let
FirstDate = #date(2018,1,1),
LastDate = #date(2019,12,31),
CalDates = {Number.From(FirstDate) .. Number.From(LastDate)}
in
CalDates
{起始数值 .. 终止数值}
用于创建一个数值列表,起始值和终止值之间一定两个点号分隔,{Number.From(日期)
将日期转换为数值。
结果如下图所示,不难看出第一列为数值,并不是日期,但是PQ中列表无法进行类型转换,所以需要先转换为表。
步骤5:单击【转换】选项卡的【到表】按钮将列表转换为表。
步骤6:在弹出的对话框中,保持默认选项,单击【确定】按钮完成转换。
步骤7:转换的结果如下图所示,依次单击【数据类型】=>【日期】,将第一列的转换为日期格式。
终于见到了我们的日期序列。
未完待续 … …
相关文章链接如下:
PQ制作时间维度表(1)
PQ制作时间维度表(2)
PQ制作时间维度表(3)
PQ制作时间维度表(4)
PQ制作时间维度表(5)
PQ制作时间维度表(6)
PQ制作时间维度表(7)