Power Query: Combine two tables from 1 file and repeat procedure for all files within one folder?

Question

I have several excel sheets that have all the same strucure. From each excel sheet I need two tables.

I know how to use Power Query to combine two tables of the same file and I know how to combine several files within one folder.

But I do not know how to set up the query such that Power Query first combines the two tables of one file and then repeats this step for all files of the folder such that I get as a result both tables of all files combined in one table.

Any suggestions or hints?

Thank you!

Answer 1

If you had posted some code, it would be easier to answer. I assume you mean:

I have several excel sheets workbooks that have all the same strucure. From each excel sheet workbook I need two tables.

because you go on to say:

...combines the two tables of one file and then repeats this step for all files of the folder such that I get as a result both tables of all files ...

Below is what I think you're trying to achieve.

---

Say I have two tables (see blue table and yellow table below) in some Excel workbook, which I want to combine.

I can combine my two tables using some code like below (provided I load the query in an Excel workbook different to the Excel workbook containing the tables):

let
    someExcelFile = Excel.Workbook(File.Contents("C:\someFolder\Book1.xlsx")),
    firstTable = someExcelFile{[Name="Table1"]}[Data], // Or however you're getting your first table.
    secondTable = someExcelFile{[Name="Table2"]}[Data], // Or however you're getting your second table.
    combineTwoTables = Table.Combine({firstTable, secondTable}) // Or however you're combining the two tables.
in
    combineTwoTables

(Presume you have something like the above. In the code above I'm identifying the tables by their name, but you might be identifying them in some other way.)

---

Then say I also have multiple Excel workbooks in some folder, each containing two tables (just like the ones shown above) which also need extracting/combining.

In order to use the above on every Excel workbook in my folder, one approach might be to change it into a function which accepts any file as an argument. Something like:

let
    CombineTwoTablesInSomeExcelFile = (someFile as binary) as table =>
        let
            someExcelFile = Excel.Workbook(someFile),
            firstTable = someExcelFile{[Name="Table1"]}[Data], // Or however you're getting your first table.
            secondTable = someExcelFile{[Name="Table2"]}[Data], // Or however you're getting your second table.
            combineTwoTables = Table.Combine({firstTable, secondTable}) // Or however you're combining the two tables.
        in combineTwoTables
in
    CombineTwoTablesInSomeExcelFile

The reason the function accepts a binary type argument is that Folder.Files (used below to access files in a folder) returns a column containing each file (in that folder) as a binary. In other words, this is convenient and we can pass the values in that column directly into our function (hope that makes sense).

To call the function against all files in a folder and combining the results into one table, we can use something like:

let
    CombineTwoTablesInSomeExcelFile = (someFile as binary) as table =>
        let
            someExcelFile = Excel.Workbook(someFile),
            firstTable = someExcelFile{[Name="Table1"]}[Data], // Or however you're getting your first table.
            secondTable = someExcelFile{[Name="Table2"]}[Data], // Or however you're getting your second table.
            combineTwoTables = Table.Combine({firstTable, secondTable}) // Or however you're combining the two tables.
        in combineTwoTables,
    filesInFolder = Folder.Files("C:\someFolder\"), // Change to whatever the folder is on your computer.
    relevantFiles = Table.SelectRows(filesInFolder, each List.Contains({".xlsx"}, [Extension])),
    invokedFunction = Table.AddColumn(relevantFiles, "toCombine", each CombineTwoTablesInSomeExcelFile([Content]), type table),
    combinedAllTables = Table.Combine(invokedFunction[toCombine])
in
    combinedAllTables

Some points in closing:

• I tried to filter the files in the folder to only include certain file extensions. In your case, you may need to add file extensions to the list.
• There is no error handling implemented within the function. So if you pass the function a file which is not an Excel file containing two tables (or if the steps required to extract the two tables differ from the steps/logic in the function), then you will likely get an error. (You should also not include the workbook containing this query in the folder.)

Answer 2

Here's another way.

Open Excel. Click Data > New Query > From File > From Folder. Browse to and select the folder that has the Excel files you want to work with. Once the folder is listed in the Folder Path text input box, click OK.

Click Transform Data.

Here, you can filter the information in the columns to restrict the files to only the ones you want to work with.

Click the Combine Files button at the top right of the Content column.

Select the first table listed and click OK.

Click on the query "Transform Sample File from Folder," to open it for editing. This is where all the transformations that would be done to every file are worked out.

Select the Navigation applied step then Transform > Detect Data Type. This detection of data types step is needed between the Navigation step, which brought in the first table you'll use, and the next step, which will bring in the second table. Without a step between these two steps, working with the graphical user interface, the second table would just replace the first in the Navigation Step. I'm not sure why, but it does.

To make sure you understand what you see below, my tables were named Table1 and Table2 in all of the spreadsheets. In the first spreadsheet, each table had entries using the following convention:

T(for table) # C(for column) # R(for row) #

So for Spreadsheet1, Table1, Column1:

T1C1R1
T1C1R2
...

Select the Navigation applied step again, and then copy what is in the formula bar.

Click the fx to the left of the formula bar and paste what you just copied over what appears in the formula bar. In other words, replace what appears with what you just copied. Then replace the table name listed after Item= with the second table's name and press enter.

Now you can perform merges with the two tables: the one initially brought in, at the Navigation step, and the one brought in later (in my example the Applied Step where the second table was brought in is called Custom1). I would use the instances of each where I changed type...so Change Type for the first table and Change Type1 for the second.

So now, to append Table1 and Table2: Click on the Changed Type1 Applied Step, then Home > Append Queries, and select Transform Sample File from Folder (Current) from the dropdown, then click OK.

Then, in the formula bar, change the first #"Changed Type1" to #"Changed Type" and press enter.

Now go back to the original query. Mine was called folder. You'll see all of your spreadsheet's with their appended tables have been appended to each other.

Just so you understand what you're seeing in the completely appended listing above, for the other spreadsheets, I added a file number. I used the convention:

F(for file) # T(for table) # C(for column) # R(for row) #

So for Spreadsheet2, Table1, Column1:

F2T1C1R1
F2T1C1R2
...