动态合并指定表格数据

9

单是一种气度,孤是一种绝望


Power Query现已集成到微软的很多产品中,包括Excel、Analysi Services,当然还有Power BI,它可以帮助我们便捷地快速地做一些ETL的工作,它主要用到的是M函数,但我们其实没有必要去专门学习这些函数,大多数情况基本上通过可视化界面上的操作都可以完成。想要学习的话推荐阅读《M Is for (Data) Monkey》这本书,同时也推荐一下朋友的论坛—Power BI和M语言的骨灰级玩家施阳的论坛

https://pqfans.com/

开始进入今天的正题,自动合并表格数据,比如很多时候我们会看到一张这样的Excel,当我们想到合并所有数据来做分析时,就会比较麻烦,那其实我们是可以通过Power Query来自动合并这些工作表中的数据(另外再提一下,工作表和表是不一样的,比如图中就是已经转成了表,可以使用快捷键ctrl+t),主要用到以下函数Excel.CurrentWorkBook,感兴趣的可以查看官方文档这里不做过多解释

在Power BI中导入工作簿,导入时会发现如下图所示的样子,就是因为把工作表里的数据转换了表的原因,这里为了区分并没有对表进行重命名,规范来说需要对表也进行重命名

这里我们随便选择一个表,然后点击转换数据进入到pq界面

可以看到右边查询设置里展开了一张表,这里我们删掉其他操作,只保留源,会看到工作簿中的所有表都在这里,仔细看会发现Data列后面的图标是不一样,是一个可展开的图标,接下来点击这个图标

会发现出现很多奇怪的列名

取消操作,点击一月下的Table,会发现展开后列名就是刚看到的column1之类,虽然可以通过提升列标题来解决,但还是建议尽量将表格中的数据转换成表

我们类型只筛选Table,然后再次展开表数据

这时会发现数据已经全部合并到一起了,Excel中如果有数据更新,只需要点击刷新,数据就会同步更新

可是,这样可能会有问题,比如我们在Excel中新增了一列小七,这是我们先前没有的,那么即使我们现在在Power BI中刷新数据,这一列也不会出现

这时,我们就需要查看下代码,这个函数我们不用关心,大概可以猜出来是只选定了这些列,然后对这些列进行了重命名,所以最笨的办法就是我们在这里加入小七这一列,

但显然这种方式不够智能,或者说还不够懒,因为有新增列还需要再手动来更新,那有没有办法动态去获取列名呢,既然这么问了肯定是有。这里会用到几个新的函数,所以具体实现不再展开,感兴趣的可以查看相关函数用法,或者直接套用这种写法(这里的表[Data]取的是上一步操作的名称,需要根据实现来改名称,其它不用变)

= List.Distinct( List.Combine( List.Transform( 表[Data], each Table.ColumnNames(_) ) ) )


套用到原始代码里如下,替换原先的列名称部分即可,这时,无论我们在原始工作簿中增加表,还是在表中增加列,在Power BI刷新都可以看到最新数据了



想要本文附件的朋友也可以来我来笔记区自助下载

https://www.wolai.com/oAsLiFTagtsFsuh7Qt3U8T


本篇文章来源于微信公众号: PowerBI木小桼

类似文章

发表回复

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