PowerBI 全自动合并多个 Excel 文件，支持“大数据”

作为自助分析人员，常常面临的场景有：

【场景A】想要数，却痛苦场景

• 可以使用前端系统操作或导出数据
• 无权限直接从后端数据库读数

【场景B】不想要数，还得痛苦场景

• 系统定期导出数据给你
• 你要不要都得要

【场景C】要数，分发收集场景

• 用同一个模板，如 Excel
• 分发给不同的部门或区域填数后收集汇总

这里就出现一个很重要的问题，如何从多个同一规律下的文件中读取数据并合并。

这个内容已经在我的《PBI基础》中讲解得非常透彻。本文再拿出其中的合并数据话题书面化。

文件夹结构

首先，所有待合并的文件都应该放置在同一个文件夹下，例如：

我们要做的事情就是不断向其中增加文件即可。

威力有多大

来看看整个 Excel 所有文件的大小是多少：

可以看到以上内容合计：183MB。

而最终的 Power BI 文件有多大呢，来看看：

可以看到以上内容合计：60MB。

实际上，普通数据在 Excel 里已经是压缩存放，例如：

一个 25 M 的 Excel 文件其实是从 200 M 压缩而得到的。

从这个角度来看，PowerBI 不但合并了数据，还进行了更加强力的压缩。

本文所述的文件整体数据量达到：300W行。这显然是 Excel 处理不了的量级。

PowerBI 模板函数

准确讲，这是 PowerBI 的 PowerQuery 模板函数，意思是，提供一个通用的功能来对数据进行处理，他的原理是这样的：

依然所有的文件的结构都是一样的，那么对文件的处理就可以先做一个模板，所有的文件都按照一个模板来处理即可，这个过程在 PowerBI 中甚至是可以自动完成的，如下：

如果可以确保所有文件的格式都是一样的，那么就可以直接转换，如下：

PowerBI的思路正是：

让用户先选择一个样本，再次重复：请确保每个文件的格式完全一样。

虽然我重复了 N 次后再重复了 N 次，而就是有人听不懂，啥叫格式完全一样，这样的同志，一定不理解啥叫全等三角形。 常犯的错误包括：

• 列头名称不同
• 列头顺序不同
• Sheet页的名称不同
• …

这些都叫结构不同 所以，你再问我为什么失败，我就问你，你为什么不听话。

点击【确定】即可。

可以看出，PowerBI自动帮助我们生成了一坨结构，如下：

其中，帮助程序查询，就是为了产生一个：模板函数。在这里的名字叫转换文件。从这里可以更容易地看到：

每一个文件名称，都对应一个转换好的表。

至此已经OK。

优化 PowerBI 模板函数

由于 Power BI 模板函数，是通过一次转换后，而从转换中提出的，如果你试图修改（准确讲是直接修改）模板函数，则会收到提示：

意思是，不应该修改这个函数，而应该取修改【转换示例文件】的这个结果文件。这样，模板函数就会保持更新。

总结

本文所述案例来自于：

这个模板实现了超越 Excel 无法做到的三点：

• 数据量级理论无上限，300W数据轻松运行。
• 表关系更加复杂，比 Excel 处理单表或 VLOOKUP 不再是一个量级。
• 业务逻辑更加丰富复杂，比 Excel 办公函数来说，完全不是一个Level。