PowerQuery中两种批量处理文件夹下多个文件的方法

PowerQuery可以轻松批量处理文件夹下的多个文件。

【不推荐】直接利用UI完成文件合并处理

最直观的方法是直接在UI界面就合并所有文件:


直接合并所有文件

这样处理的好处是简便快捷,然而坏处却也不少:

首先是强迫症患者无法接受Powerquery自动生成的查询和函数:


自动生成的查询和函数组

其次是自动合并的结果可能会出现四个问题:


自动生成的查询的问题

1.是可能出现莫名其妙的错误而导致截图中2所示的文件变少了;
2.文件变少了(示例中其实有7个区,但到B1区就因为错误截止了),原因在于该文件夹下第二个文件结构和其他文件不太一样。不过这个问题通过删除结构不一样的文件后可以解决;
3.最重要的问题是如果表格结构混乱,要把结构调整好,在这种情况下即使是不可能的也是极其困难的。
4.即使把表格结构调整好了,一不注意还会产生另一个疏漏——把表格结构调整好之,将第一行提升为表格标题后,忘记把其他表格的标题行删除从而导致错误出现。不过,也可以对第一个文件的表格和其他文件的表格进行特殊处理,从而忽略掉其他表格标题行,但这又会增加处理的步骤。

第三,如果文件很多,数据量超级大,会带来性能问题:因为每一次操作都是对该文件夹下所有文件的数据进行处理。

如果没有强迫症情结,每个数据文件结构也很规整,数据量也不大,需要调试的代码少,可以直接用UI来处理。

【推荐】将单个文件的步骤转化成函数,再应用到其他文件

所以现在我采用另外一种方法来规避直接在UI操作产生的问题。
1.首先按常规处理单个文件的方法,对一个文件进行处理,对数据进行清洗和规范化操作。
2.如何把处理步骤批量应用到其他文件上呢?思路就是把上一步的处理步骤变成一个函数。
3.在步骤1所产生的查询上右键单击,选择“创建函数”:


创建函数

这时会弹出提示说没有参数,是否要创建。不管它,点“创建”。将函数命名(我用的是fnBatch)。下一步我们手动修改函数的参数。


未找到参数的警告

这时,我们就把步骤1的查询变成了一个没有参数的函数。这个函数还没法用,我们必须为其指定参数。
4.为fnBatch()函数手动指定参数。这里我们要思考下,步骤1产生的查询,有很多步骤,那我们究竟要在哪一步设置参数,使其能根据不同的参数值而获得不同的结果呢?这个判定相当重要。这时我们要回到我们最初的目标上来——我们要把单个文件的处理规则批量应用到其他文件上。那么在PowerQuery中,如何才能引入其他文件呢?那就是文件路径。所以我们要把fnBatch()中的文件路径参数化。在左侧查询列表选中fnBatch(),点击菜单栏中的“高级编辑器”,这时会弹出警告,不用理它,点“确定”。
编辑函数的警告

我用的参数名是“file”。可以使用任意符合要求的参数名。
设置参数.png

设置好参数后是下面这个样子:


参数设置完毕

设置好参数后,关闭“高级编辑器”。这时fnBatch()就可以调用了:
可调用的函数

5.调用函数。最直观的调用方法是把其他几个文件的全路径复制粘贴到fnBatch()的参数调用框里,这样将为每个文件生成一个查询。换句话说,有1000个文件,要调用1000次,生成1000个查询。显然这并不是我们想要的。我们想要的是批量调用。
批量调用的第一步是把该文件夹下所有文件都引入到PowerQuery中来,那自然是要用到本文开始时所用的Folder.Files()。
新建一个文件夹源,将所有文件引入PowerQuery,删除其他无关列,只保留FolderPath列和Name两列。目的是获取到每一个文件的全路径。下一步我们将通过合并两列的方式获取全路径。


保留Name和FolderPath两列

由于参数是文件夹全路径,而文件夹全路径可以通过将FolderPath列和Name用“&”连接获取到,因此,这时我们可以通过新增一列来直接调用fnBatch()函数:

  = Table.AddColumn(筛选的行, "自定义", each fnBatch([Folder Path]&[Name]))

调用后,在新增的列中每一行都是一个Table,里边就是左侧文件的处理结果。


调用fnbatch函数的结果

最后,点击列名“自定义”旁边的展开符号,自动将所有文件的处理结果合并到一个表格中,处理完毕。

【总结】后一种方法看似步骤比较繁多,但是因为它能避免一系列自动操作带来的问题,所以值得为之付出。并且如果操作熟练后,一些步骤可以合并或省略。

推荐阅读更多精彩内容