Power Query not refreshing the source files in folder

[Mr_Phil]

Hi. I am really confused by this behavior. I have a query that updates via a folder. I decided today that instead of monthly updates I was going to try weekly. So I removed the files and figured I would run the query again but have a smaller output due to smaller source files. Nope. I got all the files that used to be in the folder. Scratching my head.

let
    Source = Folder.Files("D:\FILL RATES\CURRENT WEEK"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (3)", each #"Transform File (3)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (3)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (3)", Table.ColumnNames(#"Transform File (3)"(#"Sample File (3)"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"DATE", type date}, {"NAME", type text}, {"InventoryItemName", type text}, {"TO Issue", Int64.Type}, {"WH OH", Int64.Type}, {"LOCATION", type text}, {"ROUTE", type text}, {"PIXSYS #(lf)ISSUED", Int64.Type}, {"DIFF", Int64.Type}, {"MATCH?", type logical}, {"FILL RATE", Int64.Type}, {"LINE", type number}, {"ACTION TAKEN/NOTES", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"})
in
    #"Removed Columns"

I'm sure it is something I did wrong because that is how we learn. But this is annoying since it appears to me to be right. Thanks for looking.

 

[smozgur]

You are in the Power Query interface. Have you already clicked on "Refresh Preview"?

 

[Mr_Phil]

Yessir. I Refreshed Data in the spreadsheet view by right clicking on the table. Then I did “Refresh All” in that view. From there I went to the editor and refreshed the preview and another refresh all. I am still deep in the learning curve but I’ve been reasonably successful at learning and making it do the things I’ve learned about. Thanks for looking.

 

[smozgur]

Hmm, I am not sure why I didn't notice that initially, but you are using the Folder.Files() function, so it will also retrieve the files in the sub folders. You should filter by the Folder Path and select only the root folder. Or, keep the weekly folders somewhere else and only store the workbook files in the target folder.

Or, you can also try using the Folder.Contents() function instead. Folder.Contents() folder will only look at the root folder by ignoring the sub folders.

 

[Mr_Phil]

That did the trick. I moved the folders and it ran without a hitch. The Folder.Files() is the default when you specify a folder to get your import from I guess? Regardless I know about both options and can work them in as appropriate. Many thanks for the help Sir.

 

[smozgur]

That did the trick. I moved the folders and it ran without a hitch. The Folder.Files() is the default when you specify a folder to get your import from I guess? Regardless I know about both options and can work them in as appropriate. Many thanks for the help Sir.

You're welcome. Glad to hear it is solved.

Yes, the interface only uses the Folder.Files() as default. As I mentioned, you can always filter by the folder path, so it is just the generic version of Contents. But Contents saves that extra filtering step.