Transform Specific Worksheet into Table|Power Query|Excel Forum|My Online Training Hub

Ah, not all of the information was provided with your first question. I suspect there is more you haven't explained, like;

1. will each file in the folder have the exact same structure (columns, rows, sheets)?

2. will the data be formatted in Excel tables or named ranges, or just random unspecified ranges as per your first example file, in which case, how will Power Query know which cells you want to consolidate and which you want to exclude?

3. will each file also have data that is redundant as there was in your example file in the first few columns?

4. will each file have only one sheet containing data you want to get?

The second sample file, PQTEST.xlsx connects to external files that I don't have, therefore I cannot see the query or data. Please provide the source files as well.

Mynda

Thanks for providing the sample files. You'll see in the attached I used Get Files from Folder and that you need to duplicate the final query two more times so you have 3 queries, one for each table in the files; UpdatedName, NameAdded and NameDeleted.

The file name is automatically retained when you get files from a folder, so nothing extra to do there.

Mynda

1. This is done by Power Query automatically when you get files from a folder.

2. The first query under the 'Other Queries' folder is created automatically when you get the files from a folder. I renamed it, then duplicated it two more times for the NameDeleted and UpdatedName queries. Then deleted the columns from each query that weren't relevant.

Hi Jacek,

As mentioned, the cell that holds the path should be in a Table, not a regular cell.

Select L1:L2 and press Ctrl+T to create a table.

In the attached file, the table name is Table2, use it in query:

FolderName = Excel.CurrentWorkbook(){[Name="Table2"]}[Content]{0}[FolderPath],

Source=Folder.Files(FolderName)