Excel、Power Query查找目录下的所有文件解决Power BI Query的Formula.Firewall: references other queries or steps报错问题-优快云博客

Excel利用Power Query查找目录下的所有文件
Excel之Power Query查找指定目录下的所有文件
解决Power BI和Power Query的Formula.Firewall: references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.的报错

详细操作，手把手教学。

Power Query查找指定目录下的所有文件

概述

通过Excel自带的Power Query来获取指定目录下的文件，支持文件后缀筛选，文件名的关键字查找。
在完成的新文件创建的过程中，会出现Formula.Firewall: Query ‘最终查询表’ (step ‘Reordered Columns’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.的报错，顺带解决该类型的Power BI和Power Query的报错问题

具体操作参考：二、Power Query最终查询表、解决报错章节的3、报错和解决
最终效果图：
Power Query查找目录文件-页面效果图

图0：Power Query查找目录文件-页面效果图

一、插入Power Query表

1、新建Excel，插入新表

新建一个Excel表，通过输入一下内容。具体文件路径参照自己的文件夹路径输入。

文件路径	文件后缀	文件名筛选
C:\Users\*********\Documents\SQL Server Management Studio	.sql	SKU

2、插入PowerQuery表格

在菜单栏的插入中，插入一个表格，在表格设计中，修改表格名称为FilePath。如图1所示。随后选中表格区域内任一单元格，点击数据，点击从数据和区域。如图2所示。
Power Query查找目录文件-插入表格

图1：Power Query查找目录文件-插入表格

Power Query查找目录文件-插入Power Query

图2：Excel查找目录文件-插入PowerQuery表格

3、Power Query界面图

Power Query查找目录文件-Power Query界面

图3：Power Query查找目录文件-Power Query界面

二、Power Query最终查询表、解决报错

1、新建空白查询

在左侧空白查询处，新建空白查询。在查询空白区域→点击右键→新查询→其他来源→空白查询。如图4所示。
PowerQuery查找目录文件-Power Query新建空白查询

图4：Power Query新建空白查询

2、编辑中间表

在选中上述创建的空白查询，选择Home菜单，选择高级编辑器，将以下代码粘贴进去。如图5所示。

附录1：中间表的DAX代码

let
    suffix = FilePath{0}[文件后缀],
    name = FilePath{0}[文件名筛选],
    path = FilePath{0}[文件路径],
    // Source = 中间过滤表,
    Source = Folder.Files(path),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Attributes", "Content"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Name", "Extension", "Date accessed", "Date modified", "Date created", "Folder Path"}),
    // 根据suffix是否为空来决定是否应用基于后缀的过滤
    #"Filtered Rows" = if suffix = null then #"Reordered Columns"
                    else Table.SelectRows(#"Reordered Columns", each ([Extension] = suffix)),
    // 根据name是否为空来决定是否应用基于文件名的进一步过滤
    #"Filtered Rows1" = if name = null then #"Filtered Rows"
                    else Table.SelectRows(#"Filtered Rows", each Text.Contains([Name], name)),
    #"Inserted Merged Column" = Table.AddColumn(#"Filtered Rows1", "Merged", each Text.Combine({[Folder Path], [Name]}, ""), type text),
    #"Removed Columns2" = Table.RemoveColumns(#"Inserted Merged Column",{"Date accessed", "Date modified", "Date created"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"Merged", "全路径"}})
in
    #"Renamed Columns"

PowerQuery查找目录文件-编辑空白表

图5：PowerQuery查找目录文件-编辑空白表

3、报错和解决（Formula.Firewall references other queries or steps）

Formula.Firewall: Query ‘中间过滤表’ (step ‘Reordered Columns’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
完成上述操作后，可能会出现Formula.Firewall: Query ‘中间过滤表’ (step ‘Reordered Columns’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.的报错。
解决：点击最左上方的文件，点击选项和设置，选择查询和选项，在跳出来的新界面中，选择隐私，勾选忽略隐私等级，点击确定即可。
注：操作完后，页面需要重新刷新才会生效。可通过切换右方的步骤，或者切换左侧的查询来刷新数据。亦可直接操作下一步来刷新数据。
可以忽略以下截图中出现的中间过滤表。

Power Query查找目录文件-Formula.Firewall报错的解决

图6：PowerQuery查找目录文件-Formula.Firewall报错的解决

4、修改查询名称

在右侧的查询设置的属性中，选中名称框，修改为最终过滤表。
Power Query查找目录文件-查询改名

图7：PowerQuery查找目录文件-查询改名

改名后的界面如图8所示。可以看到，报错已经解决，并出现了数据。
Power Query查找目录文件-查询改名后界面

图8：PowerQuery查找目录文件-查询改名后界面

5、关闭并加载

完成改名后，在Home菜单栏中，选择关闭和加载，点击关闭并加载到，在随后出现的导入数据向导中，选择只创建连接，最后点击确定。如图9和图10所示。

Power Query查找目录文件-关闭并加载

图9：PowerQuery查找目录文件-关闭并加载

PowerQuery查找目录文件-只创建连接

图10：PowerQuery查找目录文件-只创建连接

三、重新加载到当前工作表

1、重新加载

在Excel的数据菜单栏中，选中查询和连接，会看到Excel表右侧弹出来一个新页面。在新页面的查询栏中，把鼠标选停在最终过滤表上方，在随后出现的数据预览界面点击更多(…)，选择加载到，在新出现的向导界面中，勾选表格，在已存在的工作表中选择区域，点击F1，最后点击确定。
注：在选择F1
单元格时，它可能会在默认的内容上叠加F1单元格从而导致报错。若出现报错或发现内容叠加，可先删除原内容后再选择F1单元格。
Excel查找目录文件-重新加载查询