文章背景:在使用文件夹
的方式批量导入多份Excel文件时,由于Excel文件中存在隐藏的筛选行,所以导致数据重复录入,后来看了赵文超老师的一篇文章(见文末的参考资料1),对这一问题有了新的认识。
1 FilterDatabase
的产生2 通过Excel工作簿
导入3 通过文件夹
导入4 通过Excel源文件中调整的方法
FilterDatabase
的产生有如下的一张销售表格,
有时候会对该数据进行筛选
,做一些数据分析的工作。
筛选后的数据如下:
在使用筛选
后,Excel会自动产生一个名称_FilterDatabase
,它代表了自动筛选的区域。由于这个区域是隐藏的,所以经常会被忽略。
对于隐藏的命名区域FilterDatabase
,由于我们看不见,所以需要先通过一段VBA代码调出。
VBA代码如下:
Option Explicit
Sub test()
'显示隐藏的命名区域
Dim Name As Object
For Each Name In ThisWorkbook.Names
Name.Visible = True
Next
End Sub
运行上述代码之后,可以在名称管理器
中看到该隐藏区域了。
Excel工作簿
导入 在Power BI中,通过Excel工作簿
导入上述的销售数据表,一切正常。
在应用的步骤“
中选择源
,
我们发现源数据中包含了一行FilterDatabase
,这说明我们的Excel中存在命名区域。之所以我们导入的结果正确是因为在下一步导航
中,Power Query 默认只导入Kind="Sheet"
的数据,也就是我们的工作表数据,没有包括命名区域的数据。
结论一:当通过Excel工作簿
“导入Excel文件时,即便Excel文件存在命名区域,Power Query也会自动排除,帮大家躲过这个坑。
文件夹
导入 有时候需要批量导入多份Excel文件,这个时候就会使用文件夹
导入的方式。当我们使用从文件夹
导入的时候,往往是有很多的表格需要合并,这时最容易忽略是否某个表格包含此类隐藏的命名区域。
我们使用文件夹
的方式,目前只汇总一张表。
添加自定义列数据
:Excel.Workbook([Content],true)
添加参数ture
代表识别第一行作为标题。
得到的结果如下:
对数据
展开:
得到的结果如下:
我们会看到,这里有三项数据,第一项是销售数据表。另外两项是命名区域,如下图所示。
这时,如果直接将Data
列展开,则会把工作表中的数据和命名区域中的数据都展开,也就造成了数据的重复。而当我们有多份Excel文件合并时很难发现这个错误。
避免的方法就是在展开Data
列的时候只筛选Kind
等于Sheet
,从而排除其它不需要的数据。
得到的结果如下:
展开Data
后的结果如下:
结论二:当通过文件夹
汇总多份Excel文件时,一定要注意检查Excel文件中是否存在命名区域,存在的话要通过筛选排除出去。
除了上面介绍的在Power Query中规避的方法,大家也可以对Excel源文件进行调整,但是由于源文件经常需要更新,所以并不推荐这种方法。
对于命名区域(DefinedName
),大家可以在公式
– 名称管理器
中手动删除。
参考资料:
[1] Power Query 数据处理躲坑系列一:关于FilterDatabase和命名区域(DefinedName)(https://zhuanlan.zhihu.com/p/28197784)