Import Excels from folder: Filter first, combine after - or combine first, filter after

Anonymous  a fatser and better way (I think) when you are connecting to a folder and performing same transformation (filtering in this case) on each folder contents (excel), I guess a better way to run transformation is through ODBC connection cause you can write SQL query for transformation within that connector before combining.

Let's suppose your excel (m1.xlsx) looks like this which resides in C:\Users\user1\Documents\New folder

and you want to get only Answers from ther, you can do it in following way

Odbc.Query("dbq=C:\Users\user1\Documents\New folder\m1.xlsx;defaultdir=C:\USERS\user1\DOCUMENTS;driverid=1046;maxbuffersize=2048;pagetimeout=5;dsn=Excel Files", "SELECT `Answers$`.*#(lf)FROM `C:\Users\user1\Documents\New folder\m1.xlsx`.`Answers$` `Answers$`#(lf)")

The benefit of this approach is you can write SQL queries here for transformation so that PQ gives you a cleaned and transformed table from step1 itself.

Now, if you want to iterate this for each folder content, do the following  to parametrize the file name in the query string like this

Odbc.Query("dbq=C:\Users\user1\Documents\New folder\"&[Name]&";

let
    Source = Folder.Files("C:\Users\user1\Documents\New folder"),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Odbc.Query("dbq=C:\Users\user1\Documents\New folder\"&[Name]&";defaultdir=C:\USERS\user1\DOCUMENTS;driverid=1046;maxbuffersize=2048;pagetimeout=5;dsn=Excel Files", "SELECT `Answers$`.*#(lf)FROM `C:\Users\user1\Documents\New folder\m1.xlsx`.`Answers$` `Answers$`#(lf)")),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Month", "Transaction"}, {"Month", "Transaction"})
in
    #"Expanded Custom"

This would  require you to fill in credential which can be set to Windows