question

61317893 avatar image
0 Votes"
61317893 asked Lz-3068 commented

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

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
92121-2021-04-28-14-28-58.png

02_Report.xlsx
92059-2021-04-28-14-29-19.png

03_Report.xlsx
92115-2021-04-28-14-29-34.png

04_Report.xlsx
92094-2021-04-28-14-29-46.png

Result. .\Folder\Main.xlsx
92076-2021-04-28-15-05-19.png

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


power-query-not-supportedoffice-excel-itpro
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
1 Vote"
Lz-3068 answered Lz-3068 commented

Hi @61317893

(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


· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@Lz-3068
Could you please clarify a few questions:
1. If I understand correctly, the main step of your solution is the "ColumnsToExpand" step, where the user must make a list and sequence of fields.
Do I understand you correctly?"

I think I'm on youtube.com I saw such a solution. It seemed complicated to me... But if there is no other way out, I will use this solution..

  1. (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?

  2. Briefly describe the steps to implement this solution?
    or

  3. suggest links to articles where your method of solving the problem is described?
    or

  4. suggest links to the book, the section of the book where your method of solving the problem is described?
    or

  5. search keywords...

Thanks.

0 Votes 0 ·

@lz-3068

Could you briefly describe the steps you need to perform to create the PQ_Main_61317893 file?

I understand that it is necessary:
Data -> Get Data -> From File -> From Folder
Insert Folder path -> Ok

And then, which button to press?

If it is not difficult for you, describe a few steps, and then I will try to understand...

Thanks.

0 Votes 0 ·

@61317893

For your scenario there is a wizard (Data > Get Data > From File > From Folder) that does exactly what you expect BUT this only works when all files in the folder have the same structure (same number of columns and names). Adapting the code generated by the wizard is more complex than writting your own code (what I did)

I added a few comments in the code, see updated version here
Hope this helps


0 Votes 0 ·

@Lz-3068
Part-I
All the sources of information on working with Power Query that I find describe the scenario of working with the wizard - (Data > Get Data > From File > From Folder, and so on).
If I understand you correctly , then
-you create your own script.
- when creating your script, you create:
- a sequence of queries (displayed on the left side of the Power Query interface);
- for each query, you create a sequence of steps (displayed on the right side of the Power Query interface).
- in your solution, you created:
- the DataFolder parameter (if I understood correctly, it is created: Power Query -> Home page - > Group "Parameters" -> Manage parameters -> Create parameter);
- GetExcelSheetOne function (I don't understand how to create it);
- BinariesInFolder request (I don't understand how to create it);
- QueryResult request (I don't understand how to create it).


0 Votes 0 ·

@Lz-3068
Part-II
Question.
1. How to create a GetExcelSheetOne function, a BinariesInFolder query, a QueryResult query?
Or where it is described, or what keywords to use for the search?

0 Votes 0 ·
Show more comments
Lz-3068 avatar image
1 Vote"
Lz-3068 answered Lz-3068 commented

@61317893

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


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@61317893. Beside PQ documentation that's available here you can refer to the following articles:

0 Votes 0 ·