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
2021-04-28_14-28-58.png
02_Report.xlsx
2021-04-28_14-29-19.png
03_Report.xlsx
2021-04-28_14-29-34.png
04_Report.xlsx
2021-04-28_14-29-46.png
Main.xlsx
2021-04-28_15-05-19.png
Bookmarks