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

[Anonymous]

Hi,

I'm importing xlsx from a folder. Each xlsx has 3 sheets.

My question is: What is "better"?

A) Open binaries, open tables, filter for correct sheet (Companies, Answers, Filters) - and combine each sheet afterwards

-> Only one Query + Helper query, but multiple additional steps to take inside each sheet

B) Combine tables, subquery for correct sheets and combine them

-> Multiple queries, but less many steps inside each sheet (Also because you already can format 3 different example files BEFORE final combining)

I don't have to refresh that often. Amount of data is & will be quite small.

[Anonymous]

Thank you both!

I was incommunicado, just returned to work.

[smpa01]

@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

[edhans · Accepted solution]

Never click on that "binary" link when you have selected a folder unless you will never need to change it. The path gets hardcoded in the M code. You can change it, but it is annoying.

Combine, then do the changes. For 3 files it really doesn't matter, but if you need a 4th file, the combine first will scale automatically. Or a 40th file.