Power Query - repeat queries for multiple excel files

[aikibart]

Hi all

I have about 15 excel files, all containing 3 tabs. All 15 files have same layout, but the layouts of the 3 tabs is different.

I created a set of queries; the first 3 parse through each of the 3 tabs, and the 4th appends the 3 result sets together.

What I'm now looking to do, is find a way to repeat the above 4 steps on all 15 files that I have, and consolidate the result into one file.

Would anyone know if this is possible at all?

As always, many thanks in advance for your help
Bart

 

[FranzV]

If you have the 15 workbooks in the same folder it will be quite easy. In the Power Query group of the Data tab in the menu ribbon choose:

New Query -> From File -> From Folder

You will see a table with all the filenames and you should click on the double arrow icon ( ⇊ ) on the 'Content' column. You will be prompted to select the sheet you want to query and after hitting 'Ok', PQ will append each selected sheet from all the files in the folder. It is not perfect, because it does some automatic transformations (type changing, header promoting, etc.) that might be undisired, but it is a great start point for further code tweaking until you get the expected result.

I hope it helps.

 

[aikibart]

Thank you FranzV

I started to play around with it. One issue I'm having is the following: i would like to keep the file name and worksheet name together with the expanded 'data' table column. After expanding, when I promote first row to headers, the header of the file name column becomes the filename itself.

Do you know if there is a way to work around that? Right now, I renamed that column to 'filename', but that won't work if there is a file with a different name in the folder.

Thanks!
Bart

 

[FranzV]

I am not sure I'm understanding correctly. A screenshot of your table might help.

If the issue is that you don't want to hardcode the column name there is a workaround as long as that column is always in the same position. You can add a step that dynamically finds the name of the Nth column using

NthColumnName = Table.ColumnNames( YourTable ){ N-1 }

Where YourTable refers to the table you want to modify (usually the name of the previous step) and N-1 is needed because list positions start at 0.

You can then rename your column manually and then change the old name in commas for NthColumnName (or whatever name you gave to the step where you got the old column name).

// from
= Table.RenameColumns( YourTable, {{ "OldName", "NewName" }} )

// to
= Table.RenameColumns( YourTable, {{ NthColumnName, "NewName" }} )

// or in a single step
= Table.RenameColumns( YourTable, {{ Table.ColumnNames( YourTable ){ N-1 }, "NewName" }} )

I hope it helps.

 

[aikibart]

FranzV, you are brilliant, this was exactly what I was looking for.

Thanks again, Bart