Challenging Append Scenario - Power Query

[SeveralTradesLater]

Hi everyone. Happy July 4th to those who celebrate. Hope you smart folks can assist. Let's see who can beat a large language learning model at giving step by step instructions.

Here is the scenario,

I have a folder, let's call this folder "ALL". The folder "ALL" contains more folders North, West, East and South. Folders North, West, East and South each contain various Company Workbooks. Each workbook has a company name, but for our purposes let's assume they are named Company 001 thru Company 020 all divided randomly within Folders North, West, East and South.

However, within each North, West, East and South folder I am only focused on the workbooks that contain "(Reviewed)" in the file name. Example: "Company 003 (Reviewed)" , "Company 007 "Reviewed".

Each workbook contains multiple sheets of irrelevant data. There are only two sheets with each workbook I am concerned about, let's assume they are named sheets "A" and "B". In most cases the workbooks contain both sheets "A" and "B", in very few cases they contain either sheet "A" or sheet "B".

Both sheets "A" and "B" contain the same headers. However, those headers consist of five rows of useless labels. All data I am trying to append exists below those five rows.

Normally, I would have to copy and paste columns A, B and C of sheets "A" and "B" into a new worksheet and combine all of them thru Company 020.

Example Company 001 - Reviewed

Company 001 - Reviewed	Sheet A	Apple	$1.00	$3.00
Company 001 - Reviewed	Sheet A	Orange	$5.00	$8.00
Company 001 - Reviewed	Sheet A	Kiwi	$3.50	$2.00
Company 001 - Reviewed	Sheet B	Banana	$1.20	$4.40
Company 001 - Reviewed	Sheet B	Coffee	$5.00	$7.00

Example of final result I want to produce and load to another sheet in Power Query.

Company 001 - Reviewed	Sheet A	Apple	$1.00	$3.00
Company 001 - Reviewed	Sheet A	Orange	$5.00	$8.00
Company 001 - Reviewed	Sheet A	Kiwi	$3.50	$2.00
Company 001 - Reviewed	Sheet B	Banana	$1.20	$4.40
Company 001 - Reviewed	Sheet B	Coffee	$5.00	$7.00

Company 005 - Reviewed	Sheet A	Candy	$1.00	$3.00
Company 005 - Reviewed	Sheet A	Orange	$5.00	$8.00
Company 005 - Reviewed	Sheet A	Pens	$3.50	$2.00
Company 005 - Reviewed	Sheet B	Pencils	$1.20	$4.40
Company 005 - Reviewed	Sheet B	Paper	$5.00	$7.00

Company 007 - Reviewed	Sheet B	Apple	$1.00	$3.00
Company 007 - Reviewed	Sheet B	Orange	$5.00	$8.00
Company 009 - Reviewed	Sheet A	Comic Books	$3.50	$2.00
Company 009 - Reviewed	Sheet A	Banana	$1.20	$4.40
Company 009 - Reviewed	Sheet A	Coffee	$5.00	$7.00

Company 015 - Reviewed	Sheet B	Soda	$1.00	$3.00
Company 015- Reviewed	Sheet B	Clothing	$5.00	$8.00
Company 017 - Reviewed	Sheet A	Plants	$3.50	$2.00
Company 019 - Reviewed	Sheet A	Banana	$1.20	$4.40
Company 020 - Reviewed	Sheet B	Vitamins	$5.00	$7.00

 

[jdellasala]

This is really simple, and although the main M Code is here, you'll want to do it yourself rather than copy and paste. There's also one line that you have to manually change.
So, you start with Data -> Get Data -> From File -> From Folder, and point to your ALL folder. You'll be presented with a table of every file. Using the UI, make changes as needed. Here's the main query that delivers the final table:

let
    Source = Folder.Files("C:\Users\USERNAME\Desktop\ALL"),

    // The following 2 lines make sure you're getting only Excel files
    LowercasedExtension = Table.TransformColumns(Source,{{"Extension", Text.Lower, type text}}),
    FilteredOnlyXlsxRows = Table.SelectRows(LowercasedExtension, each [Extension] = ".xlsx"),

    // Next, only files with "Reviewed" in the file Name
    FilteredOnlyFilesWithReviewed = Table.SelectRows(FilteredOnlyXlsxRows, each Text.Contains([Name], "Reviewed")),

    // Next line removes the .xlsx extension to clean up the name
    RemoveXlsxFromFilename = Table.ReplaceValue(FilteredOnlyFilesWithReviewed,".xlsx","",Replacer.ReplaceText,{"Name"}),

    // This allows you to retain the Region folder name
    RemovePathToRegion = Table.ReplaceValue(RemoveXlsxFromFilename,"C:\Users\jdell\Desktop\ALL\","",Replacer.ReplaceText,{"Folder Path"}),
    RemoveTrailingBackslash = Table.ReplaceValue(RemovePathToRegion,"\","",Replacer.ReplaceText,{"Folder Path"}),

    // The next 3 lines of code are generated AUTOMATICALLY when you go to expand the Content column
    FilteredHiddenFiles1 = Table.SelectRows(RemoveTrailingBackslash, each [Attributes]?[Hidden]? <> true),
    InvokeCustomFunction1 = Table.AddColumn(FilteredHiddenFiles1, "Transform File", each #"Transform File"([Content])),
// NOTE: This code generated will only keep the "Transform File" column. ADD "Folder Path", "Name", MANUALLY
    RemovedOtherColumns1 = Table.SelectColumns(InvokeCustomFunction1, {"Folder Path", "Name", "Transform File"}),

    RenamedFolderPathToRegion = Table.RenameColumns(RemovedOtherColumns1,{{"Folder Path", "Region"}}),
    ExpandedTableColumn1 = Table.ExpandTableColumn(RenamedFolderPathToRegion, "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),

    // For whatever reason, this has to be added manually here, not in the Transform Sample File query
    ChangedType = Table.TransformColumnTypes(ExpandedTableColumn1,{{"Region", type text}, {"Name", type text}, {"Column1", type text}, {"Column2", type number}, {"Column3", Int64.Type}})
in
    ChangedType

Note the comments in the code above. Also, I renamed all the steps removing spaces to make the code clearer and avoid that awful #"" nonsense!

This is the resulting table:

Book1
	A	B	C	D	E
1	Region	Name	Column1	Column2	Column3
2	East	Company 001 Reviewed	Apple	1	3
3	East	Company 001 Reviewed	Orange	5	8
4	East	Company 001 Reviewed	Kiwi	3.5	2
5	East	Company 005 Reviewed	Candy	1	3
6	East	Company 005 Reviewed	Orange	5	8
7	East	Company 005 Reviewed	Pens	3.5	2
8	North	Company 001 Reviewed	Apple	1	3
9	North	Company 001 Reviewed	Orange	5	8
10	North	Company 001 Reviewed	Kiwi	3.5	2
11	North	Company 005 Reviewed	Candy	1	3
12	North	Company 005 Reviewed	Orange	5	8
13	North	Company 005 Reviewed	Pens	3.5	2
14	South	Company 001 Reviewed	Apple	1	3
15	South	Company 001 Reviewed	Orange	5	8
16	South	Company 001 Reviewed	Kiwi	3.5	2
17	South	Company 005 Reviewed	Candy	1	3
18	South	Company 005 Reviewed	Orange	5	8
19	South	Company 005 Reviewed	Pens	3.5	2
20	West	Company 001 Reviewed	Apple	1	3
21	West	Company 001 Reviewed	Orange	5	8
22	West	Company 001 Reviewed	Kiwi	3.5	2
23	West	Company 005 Reviewed	Candy	1	3
24	West	Company 005 Reviewed	Orange	5	8
25	West	Company 005 Reviewed	Pens	3.5	2
Sheet1

When you expand the Content column, Power Query will create a folder with sub folder and 4 queries. That will look like this:

That is why you need to do it manually. All of that is not really needed, but that is how the UI handles it.

If you need to do manipulations to all the files, do it in the Transform Sample File query. You can rename the columns in either place.

I only used 2 files because it was such a pain to pull in the data. I suggest you use XL2BB when posting data in the future.