Combine horizontally multiple ranges from different files with different columns. Power Query.

Question

There is a folder in which files with reports are constantly saved.
In the n_report file.xlsx:

• fields: code, name, description - will be required;
• fields: property_1, property_2, property_3, and so on-the fields will be in any composition, in any sequence, with any names.

Folder structure:
.\Folder\Main.xlsx
..\Folder\Data\01_Report.xlsx
..\Folder\Data\02_Report.xlsx
..\Folder\Data\03_Report.xlsx
..\Folder\Data\04_Report.xlsx

Question.
1. How to use Power Query to collect from the files "n_Report.xlsx " ranges per table (range) per file Main.xlsx?
2. As in the file Main.xlsx in the table (range), always have the current composition of data from all files in the folder .\Folder\Data** ?
In other words, if in a folder **..\Folder\Data** added a new file **n_Report.xlsx, then the user in the file Main.xlsx clicks the Update button and results in data from all files n_Report.xlsx are displayed (updated) in the table (range).
3. If Power Query can't implement this with its own tools, what additional work needs to be done?
For example, make ranges in the file n_Report.xlsx make tables...

Note.
Formatting the name_report field in the file "Main.xlsx the ExpectedResult tab is shown conditionally.

01_Report.xlsx

02_Report.xlsx

03_Report.xlsx

04_Report.xlsx

Result. .\Folder\Main.xlsx

Files
https://yadi.sk/d/rMxcIeQSbHEDqA

Answer 1

Hi @Иван Иванов

(Thanks for providing the files & a precise expected result). Your workbook with the query that does what you expect is available here. Don't forget to change the folder parameter (named DataFolder)
If you want to columns to be ordered differently let me know

Answer 2

@Иван Иванов

1. If I understand correctly, the main step of your solution is the "ColumnsToExpand" step
Correct, that's the key step

(If I understood question-1 correctly) Is it possible to make a solution in which the user does not analyze the list of fields, and the program collects all the unique fields from all the files, the program determines the sequence of these fields and outputs the result to the resulting table?
That's exactly what step "ColumnsToExpand" does => the user has nothing to do
To understand how this works you can decompose it as follow and look at the result of each step (ListOfTablesToCombineAtTheEnd, TranformAboveListAsAListOfColumnNames, CombineAboveListOfListsAndRemoveDuplicates) with the APPLIED STEP window:

let  
    Source = BinariesInFolder,  
    AddedTableFromWBook = Table.AddColumn(Source, "TableFromSheetOne", each  
        GetExcelSheetOne([Content]), type table  
    ),  
    RemovedBinary = Table.SelectColumns(AddedTableFromWBook,  
        {"name_report", "TableFromSheetOne"}  
    ),  
      
    ListOfTablesToCombineAtTheEnd = RemovedBinary[TableFromSheetOne],  
    TranformAboveListAsAListOfColumnNames = List.Transform(ListOfTablesToCombineAtTheEnd,  
        Table.ColumnNames  
    ),  
    CombineAboveListOfListsAndRemoveDuplicates = List.Union(TranformAboveListAsAListOfColumnNames),  
  
    ExpandedColumns = Table.ExpandTableColumn(RemovedBinary, "TableFromSheetOne",  
        CombineAboveListOfListsAndRemoveDuplicates  
    )  
in  
    ExpandedColumns