Nest 加载文件夹路径 Power Query [英] Nest Load a folder path Power Query

问题描述

我正在尝试使用 Excel Power Query(在此使用 Excel 2010)来检查目录列表中名为SWPPP"的文件夹中是否有文件.

文件夹结构如下.

|O:\Planning Projects|---2012\|--------00-000 自定义文件夹名称\|------------------------------------\SWPPP|---2013\|--------00-000 自定义文件夹名称\|------------------------------------\SWPPP|---2014\|--------00-000 自定义文件夹名称\|------------------------------------\SWPPP|---2015\|--------PB-391-000 自定义文件夹名称\|------------------------------------\SWPPP|--------10-000 另一个自定义文件夹名称\|------------------------------------\SWPPP|---2016\|--------00-000 自定义文件夹名称\|------------------------------------\SWPPP|--------10-300 另一个自定义文件夹名称\|------------------------------------\SWPPP|---2017\|--------00-000 自定义文件夹名称\|------------------------------------\SWPPP|--------10-000 另一个自定义文件夹名称\|------------------------------------\SWPPP|---填写许可证\|--------这些文件夹我不关心\|--------另一个我不关心的文件夹\|---帕特的检查\|--------这些文件夹我不关心\|--------另一个我不关心的文件夹\

从根目录 (O:\Planning Projects) 开始，我只对年份"文件夹内的文件夹感兴趣(例如:2012、2013、2014...).其中有 600 多个子目录(接近 700 个)，并且会随着时间的推移而不断增长.我正在尝试尽可能地自动化和面向未来.

我需要的是进入每个年份"文件夹，转到子文件夹，检查是否有SWPPP"文件夹，然后检查其中是否有任何文件.

我快要完成了，但有一部分我被卡住了.我得到了臭名昭著的:

<块引用>

Formula.Firewall:查询 'Query1'(步骤 'loadedFiles')引用其他查询或步骤，因此它可能不会直接访问数据源.请重建这个数据组合.

在继续之前，我已经完成了研究并访问了几个网站:

如果这些 SWPP 目录仅存在于您的年份文件夹中，您真正需要做的就是将文件夹路径列过滤为以SWPP\"结尾的任何内容

= Table.SelectRows(#"Filtered Rows", each Text.EndsWith([Folder Path], "SWPP\"))

如果您只想过滤年份目录，这取决于您要查找的目录的复杂性和多样性.如果它们都只是您描述的单个目录中的年份文件夹，您可以执行一些简单的操作，例如检查文件路径该位置中的文本以查看它是否为20"(假设您的年份无论如何只能回到 2000...)，这不是最模块化的检查，但可能足以满足您的需求.

= Table.SelectRows(Source, each (Text.Range([Folder Path], 11, 2) = "20"))

从那里您可以过滤到您需要的任何内容(删除除文件夹路径之外的所有列并删除其中的重复项将为您提供每个包含文件的 SWPP 目录的列表)

I'm trying to use Excel Power Query (Stuck with Excel 2010 in this one) to check if a folder called "SWPPP", which is inside a list of directories, has files in it.

The folder structure is as follows.

|O:\Planning Projects
|---2012\
|--------00-000 A Custom Folder Name\
|------------------------------------\SWPPP
|---2013\
|--------00-000 A Custom Folder Name\
|------------------------------------\SWPPP
|---2014\
|--------00-000 A Custom Folder Name\
|------------------------------------\SWPPP
|---2015\
|--------PB-391-000 A Custom Folder Name\
|------------------------------------\SWPPP
|--------10-000 Another Custom Folder Name\
|------------------------------------\SWPPP
|---2016\
|--------00-000 A Custom Folder Name\
|------------------------------------\SWPPP
|--------10-300 Another Custom Folder Name\
|------------------------------------\SWPPP
|---2017\
|--------00-000 A Custom Folder Name\
|------------------------------------\SWPPP
|--------10-000 Another Custom Folder Name\
|------------------------------------\SWPPP
|---Fill Permits\
|--------These folders I don't care about\
|--------Another Folder I don't care about\
|---Pat's Inspections\
|--------These folders I don't care about\
|--------Another Folder I don't care about\

From root (O:\Planning Projects), I'm only interested in the folders that are inside the "year" folders (e.g.: 2012, 2013, 2014...). There are more than 600 sub-directories (close to 700) inside them, and they will keep growing as time goes. I'm trying to automate and future-proof this as much as possible.

What I need is to go in each of those "year" folders, go to the sub-folders, check if there is a "SWPPP" folder, and then check if there are any files in it.

I'm close to get it done, but there's a part which I'm stuck. I'm getting the infamous:

Formula.Firewall: Query 'Query1' (step 'loadedFiles') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Before going any further, I've done my research and accessed a couple of sites:

https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

https://community.powerbi.com/t5/Desktop/Formula-Firewall-Query-references-other-queries-so-it-may-not/td-p/18619

Power Query - Please rebuild this data combination

This is the procedure I've followed:

1. 1.I load the root directory. (O:\Planning Projects)
2. Do some logic to get the "years" folder into a list: {2012, 2013, 2014,etc}\
3. From the list, I iterate and get the directories that are in each of those lists. (So far, so good)
   4. Now, I try to append "SWPPP" to the folder structure, and try to get the Contents from the folder. Then that's where I get struck with the error.

I tried breaking the algorithm in different parts, as suggested by excelguru.ca, but that didn't do anything.

My second approach:

1. Repeat steps 1 -3 from previous procedure.
2. Load the directories into the Excel (See Step 3), and then, concatenated "SWPPP" in a custom table I created.
3. Load the custom table into a new query using Power Query's import from Excel file (By importing the same file).
4. I tried reading the files again, but it gave me the same error.

I'm using m programming language to put everything together (Need some help, only know it for a day), and I'm stuck.

Here's the second approach:

This first query loads all the sub-directories from the "year" folders.

Query Name: "LoadInspections"
let
    Url = "O:\Planning Projects",
    Source = Folder.Contents(Url),
    Cols = Table.SelectColumns(Source, "Name"),
    ColsList = Table.ToList(Cols),
    Cond = (val) => try not Number.IsNaN(Number.FromText(val)) otherwise false,
    Years = List.Select(ColsList, (val) => val = "pre 2012" or Cond(val)),
    load = List.Transform(Years, (val) => Folder.Contents(Url & "\" & val)),
    combinedTables = Table.Combine(load)

in
  combinedTables

This is the query that loads the custom table I created in Excel, which concatenates the directories with "SWPPP":

Query Name: ApplicationNumber_Files_Folders

let
    Source = Excel.Workbook(File.Contents("O:\MS4 Program\MCM4 Construction Site Stormwater Runoff Control\SWPPP Tracker\SWPPP Inspection Processor.xlsm"), null, true),
    ApplicationNumber_Files_Folders_Table = Source{[Item="ApplicationNumber_Files_Folders",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(ApplicationNumber_Files_Folders_Table,{{"Application Number", type text}, {"Planning Project", type text}, {"Hidden Folder Path", type text}, {"Folder Path", type text}})
in
    #"Changed Type"

Then, as suggested by excelguru.ca, I tried breaking it up in a separate query:

Query Name: Query1

let
    Source = ApplicationNumber_Files_Folders,
    Cols = Table.SelectColumns(Source, "Hidden Folder Path"),
    ColsList = Table.ToList(Cols),
    SWPPP = List.Transform(ColsList, (url) => url&"\SWPPP"),
    // This line below is the problematic one
    loadedFiles = List.Transform(SWPPP , (url) => Folder.Contents(url))


in
    loadedFiles

I've visited these sites:

解决方案

If you are looking for your SWPP directories that have files in them, I would probably just change the base level query to be "Folder.Files" instead of "Folder.Contents", as this will return every file in every subdirectory of the specified base directory.

= Folder.Files("C:\folders")

If these SWPP directories only exist in your year folders, all you really need to do is filter the Folder Path column to anything that ends in "SWPP\"

= Table.SelectRows(#"Filtered Rows", each Text.EndsWith([Folder Path], "SWPP\"))

If you wanted to filter only for the year directories, it depends how complex and varied the directories you are looking for are. If they are all just year folders in a single directory as you described, you can just do something simple like check the text in that location of the filepath to see if it is "20" (assuming your years are only going back to 2000 anyways...), which isn't the most modular check, but may be adequate for your needs.

= Table.SelectRows(Source, each (Text.Range([Folder Path], 11, 2) = "20"))

From there you can just filter down to whatever you need (removing all the columns except the folder path and remove duplicates on it will give you a list of every SWPP directory that has files in it)

这篇关于Nest 加载文件夹路径 Power Query的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！