Power BI：关于FilterDatabase和命名区域(DefinedName)

文章背景：在使用文件夹的方式批量导入多份Excel文件时，由于Excel文件中存在隐藏的筛选行，所以导致数据重复录入，后来看了赵文超老师的一篇文章（见文末的参考资料1），对这一问题有了新的认识。

1 FilterDatabase的产生2 通过Excel工作簿导入3 通过文件夹导入4 通过Excel源文件中调整的方法

1 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

运行上述代码之后，可以在名称管理器中看到该隐藏区域了。

2 通过Excel工作簿导入

在Power BI中，通过Excel工作簿导入上述的销售数据表，一切正常。

在应用的步骤“中选择源，

我们发现源数据中包含了一行FilterDatabase，这说明我们的Excel中存在命名区域。之所以我们导入的结果正确是因为在下一步导航中，Power Query 默认只导入Kind="Sheet"的数据，也就是我们的工作表数据，没有包括命名区域的数据。

结论一：当通过Excel工作簿“导入Excel文件时，即便Excel文件存在命名区域，Power Query也会自动排除，帮大家躲过这个坑。

3 通过文件夹导入

有时候需要批量导入多份Excel文件，这个时候就会使用文件夹导入的方式。当我们使用从文件夹导入的时候，往往是有很多的表格需要合并，这时最容易忽略是否某个表格包含此类隐藏的命名区域。

我们使用文件夹的方式，目前只汇总一张表。

添加自定义列数据：Excel.Workbook([Content],true)

添加参数ture代表识别第一行作为标题。

得到的结果如下：

对数据展开：

得到的结果如下：

我们会看到，这里有三项数据，第一项是销售数据表。另外两项是命名区域，如下图所示。

这时，如果直接将Data列展开，则会把工作表中的数据和命名区域中的数据都展开，也就造成了数据的重复。而当我们有多份Excel文件合并时很难发现这个错误。

避免的方法就是在展开Data列的时候只筛选Kind等于Sheet，从而排除其它不需要的数据。

得到的结果如下：

展开Data后的结果如下：

结论二：当通过文件夹汇总多份Excel文件时，一定要注意检查Excel文件中是否存在命名区域，存在的话要通过筛选排除出去。

4 通过Excel源文件中调整的方法

除了上面介绍的在Power Query中规避的方法，大家也可以对Excel源文件进行调整，但是由于源文件经常需要更新，所以并不推荐这种方法。

对于命名区域（DefinedName），大家可以在公式– 名称管理器中手动删除。

参考资料：

[1] Power Query 数据处理躲坑系列一：关于FilterDatabase和命名区域(DefinedName)（https://zhuanlan.zhihu.com/p/28197784）