Hi Jacek,

You can append the tables. See file attached.

Mynda

o wow very nice approach,

thank you! 

Hmm ok but when i have to select multiple files like Test1 - like for example 50 from folder i would have to do one big transformation i think.
Using Merging columns and unpivoting them, can you help also with that? 

And furthermore, how can i add workbook source name to colum? (i would to take without extension = "xlsx"). 

I attached second worbkook - i need one more column here - workbook name. 

Best Wishes,
Jacek

Ok i am addiny my trials with this as attachment.

I combined and transformed 2 tables and merged columns for Values but i do not know what should be next. 

Please help,
Jacek

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

Hi Mynda,

thanks for questions!

1. Yes, exact structure

2. There is no tables and ranges. PQ will use Columns without structure

3. Yes, the structure is the same and everywhere i have to take only specific columns

4. yes, only one sheet. 

External files in attachment. 

In PQTEST.xlsx i am just trying to merge Test1 and Test2 from the same folder. 

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

o wow thank you very much!

Few questions:

1. = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true) and action "Filtered hidden files".

How did you do this? 

2. When you were creating 4 queries : "NameAdded", "NameDeleted" you wre copying queries manually and deleted other files created with them together? 

(not necessery one)

Best,

Jacek

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.

thank you Mynda. 

Hi Mynda,

one more thing. 
I want to input folder path into cell and pass it to power query. 

Can you help with this? 

Best,
Jacek

You can create a table with a single row that contains your folder path.

In power query, add a new line using Advanced Editor with:

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

Rplace the hardt yped path from the query with this parameter name:

Source=Folder.Files(FolderName)

Hi Catalin,

thank you. I tried to use what you provided but i have issues with that. 

Where exactly can i put this path? 

Can you please help and use my attached workbooks?

Please help,
Jacek

Please help,

Jacek

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)