From Folder method reading all files even after filtering?

[Nick Burns]

Attached is my powerquery file and sample data.

The PQ file is part of a larger macro, but essentially on a coworker's PC, as the query is being run, you can watch in the status bar that the "sample function" is being invoked for every file in the folder, even though I'm filtering the files prior to the function. I can't seem to replicate this issue on my PC.

My initial thought is through the macro, to just move those files selected to their own temporary folder and then run the query against that folder, but if there's something I'm missing in my power query I'd rather use that option.

Has anyone else experienced this?

 

[BernardBrussels]

Hi Nick,

Actualy I registered to expose a problem VERY close to yours...

But I can be a little further that you are.

I use the same parameters function as you, but it will get all files from the directory & all subs.

My problem: It seems that some users of the file can't refresh the queries...

Bernard

 

[BernardBrussels]

Hi Nick,

I solved the problem by adding a macro that will refresh the table "parameters" at the opening of the document.
I love the "One Rules them All".

Bernard.

 

[cyborgski]

Hi Nick,

I am purely speculating here.

Your filter is a NestedJoin. Perhaps PQ is "optimizing" the query similar to how it handles Table.Sort. Try replacing the NestedJoin, Remove steps with the SelectRows steps below.

let
Source = Folder.Files(pSourceDir),
FilteredHiddenFiles = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
LowercaseFilename = Table.TransformColumns(FilteredHiddenFiles,{{"Name", Text.Lower, type text}}),
#"Filtered Rows" = Table.SelectRows(LowercaseFilename, each List.Contains(SelectedFiles[Files], [Name])),
InvokeTransformOAData = Table.AddColumn(#"Filtered Rows", "TransformFile", each TransformOAData([Content])),
RenameColumns = Table.RenameColumns(InvokeTransformOAData, {"Name", "Source.Name"}),
KeepTables = Table.SelectColumns(RenameColumns, {"Source.Name", "TransformFile"}),
ExpandTables = Table.ExpandTableColumn(KeepTables, "TransformFile", Table.ColumnNames(TransformOAData(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(ExpandTables,{{"Source.Name", type text}, {"COINS ID", type text}, {"Job Name", type text}, {"Phase", type text}, {"CC", type text}, {"Description", type text}, {"Vendor", type text}, {"Vendor Name", type text}, {"Int Reference", type text}, {"Invoice No", type text}, {"PO Number", type text}, {"Check No", type text}, {"Job", type text}, {"Eff Date", type date}, {"Run Date", type date}, {"Invoice date", type date}, {"Check Date", type date}, {"Amount", type number}, {"Discount Taken", type number}, {"Check Amount", type number}, {"Gross", type number}, {"Error", type logical}})
in
#"Changed Type"

FYI, I was not able to replicate the issue. Your coworker might be on a different version of PQ.

Hope this helps,
Mike