Power Query 小攻略:图书在架率表格全自动更新

各位好啊,今天这篇文章主要给大家介绍一个矩阵表格全自动更新的攻略。我们最后可以实现全自动更新的表格如下图:

file

该全自动更新可以实现:随着源数据的增加,表格可以一键刷新自动添加日期及对应日期的图书在架情况。
源数据如图,“1”为可借,“0”为不可借。

file

这个案例计算在架率并不是难点,我们在此就不再赘述,计算在架率公式如下:

file

案例难点在于,如何让这个既包含日期又包含文本的表格自动更新,如果是表头固定的中国式报表我们可以采用直接导入一张写好表头的表格,但是对于这种需要日期自动更新的情况我们应该如何操作呢,以下是实现一键刷新就能更新表头的详细步骤。

我们可以在Power Query中实现该功能,
首先是复制一张源数据表格,选中日期列,删除列,删除其他列,得到只有日期一列。

file

然后去重,让每个日期都只出现一次。

file

然后就到了问题的关键,我们应该如何在这一列里加一行写上“在架率”三个字呢?

PQ里没有添加行的按钮,只可以添加列,那我们就可以先把这一列日期变成一行多列,然后再添加列,最后将其变成一列。

实现行列互相转化的就是“转置”。

file

这样一列多行,就变成了一行多列。

file

然后再添加自定义列,“在架率”。

file

添加好之后,再进行一次转置,就得到了一列表头,这列表头里的日期是可以根据源数据里日期的增加而增加对应日期的。

我们可以先拉一个矩阵看一下效果,由于表头和事实表之间没有建关系,直接使用在架率和表头得到的结果是不对的,我们需要再新建一个度量值以实现和表头的对应。

file

新建的度量值如下:
如果表头为“在架率”,则显示总体在架率情况,如果不等于“在架率”,则显示对应日期的在架情况。

file

效果如下:

file

这个表格里的每天的每本书籍的在架情况还是显示为百分比,如果要换成文章开头的“√”和“×”显示,就需要中间再写一步,这一步我们用到了UNICHAR,其中10004为“√”,10008为“×”。感兴趣的朋友可以自行百度UNICHAR,我们本篇文章就不过多进行介绍啦。

借阅状态 =
IF(SUM('图书上架情况'[是否可惜])=0,UNICHAR(10008),UNICHAR(10004))

这样以上的Value公式就可以改为:

file

添加字段条件格式,即可实现:

file

最后我们来试一下更新源数据一键刷新的功能。增加数据至2019/4/9,刷新,即可得下表。

file

以上,
再见。



* PowerPivot工坊原创文章,转载请注明出处!


如果您想深入学习微软Power BI,欢迎登录网易云课堂试听学习我们的“从Excel到Power BI数据分析可视化”系列课程。或者关注我们的公众号(PowerPivot工坊)后猛戳”在线学习”。


长按下方二维码关注“Power Pivot工坊”获取更多微软Power BI、PowerPivot相关文章、资讯,欢迎小伙伴儿们转发分享~

Power Pivot工坊