PowerQuery的一大经典且高频应用,就是批量汇总多个Excel工作簿,很多人刚开始认识Power Query也是通过这个功能,如果你还不熟悉,也可以先翻翻以前的文章。
虽然比较简单,但遇到不少人会在批量合并的过程中,出现各种问题,不知道怎么解决,其实PowerQuery这个简单的操作步骤中,也有不少技巧,掌握之后可以帮你更灵活高效的汇总数据。
以批量汇总文件夹的Excel工作簿为例,
在这个文件夹中,有北京、广州、杭州三个Excel工作簿,其中每个工作簿又包含1月、2月、3月三个sheet。
先按常规步骤操作一遍,
↑ 获取数据>文件夹 ↑ 合并并转换数据这是大家最常用的操作方式,来看看这样做的结果是什么。
虽然完成了一键批量合并,非常快捷,但是左边查询栏多出很多不需要的查询,看着很乱,可是如果你想删除,是不是怎么也删除不掉?
这些查询PowerQuery执行合并操作时,默认操作过程留下的中间文件,当你点击"合并并转换数据"时,PowerQuery先根据其中一个文件作为示例,生成一个自定义函数,然后调用自定义函数,完成合并。
自动合并,除了会留下一堆杂乱的查询无法删除,还有个问题是,如果合并结果出错(出错的概率很高),需要修改示例文件或者自定义函数的代码,但是对于初学者是比较困难的,很多人不知道如何修改。
所以不建议使用默认的合并操作,在导入之后的预览窗口,推荐你使用“转换数据”,如下图所示:
之后的操作步骤如下:
↑ 选中[Content],删除其他列
你也可以根据需要保留部分列。
↑ 新建自定义列导入到PowerQuery中的数据默认都是类型为binary类型,需要用函数将它解析出来,对于Excel工作簿文件,输入:
=Excel.Workbook([Content],true)
↑ 展开自定义列 ↑ 展开Data列 ↑ 完成合并你再看左边的查询栏,是不是非常干净,只有一个查询。
这种算是手工合并数据,但相比自动合并,也就是输入一个简短的M函数,多点了几次鼠标而已,熟练操作后,整个过程不会超过一分钟。
通过上面的描述和操作过程,涉及到两个常用的PowerQuery合并技巧:
1, 为了避免出现杂乱的查询文件,使用“转换数据”,手动合并;
2. 新建自定义列时,Excel.Workbook的第二个参数不要省略,当参数为true时,会自动将Excel的第一行用作标题,可以省去一个步骤。
并且在手动合并的过程中,灵活运用,可以方便的进行各种形式的数据合并:
3. 合并文件夹中的部分工作簿
放入文件夹中的工作簿,可以按需要合并,而并不是必须全部合并。
在导入后的第一步【源】中,可以筛选需要合并的工作簿,假如只需要合并北京和杭州,直接勾选就可以了
之后的步骤不变。
4. 合并文件夹中的部分工作表。
工作簿中的sheet,同样可以按需合并。
比如只合并每个工作簿中名称为“3月”的sheet,在添加并展开自定义列以后,在Name列筛选3月,
之后的步骤不变。
5. 合并文件夹中的某一类型数据。
如果文件夹中的文件类型,不止一种,还可以选择按文件类型合并。
假如文件夹中既有Excel格式,还有csv、txt格式的数据文件,如果直接全部合并会报错,那么可以按类型分别单独合并。
依然在【源】这个步骤中,可以按数据格式来筛选。
6. csv、txt格式的数据合并技巧。
上面添加自定义列时用的是Excel.Workbook,是专门用来解析Excel格式的,当数据格式为csv或txt时,需要换个解析函数。
csv、txt格式本质上属于同一种类型,都可以使用这个函数Csv.Document,为了避免中文出错,一般情况下自定义列可以直接这样写:
=Csv.Document([Content],[Delimiter=",", Encoding=936])
其中Delimiter=","是对逗号分割的数据,如果你的源数据是其他符号分割,这里就改为相应的符号;中文编码一般为936,所以上面代码中用了Encoding=936来避免中文乱码的问题。
以上就是关于PowerQuery文件夹批量汇总数据时,经常会用到的各种技巧,以及遇到问题时的解决办法,熟练掌握并灵活运用,PQ批量汇总的相关问题,基本都可以处理。
-精彩推荐-
采悟 from PowerBI星球
网友评论