Combine Different Excel Worksheets Tabs similar Names

[sa34techer]

How to point power query to the worksheet names with CPE in it from the examples below?   Some excels will have one worksheet and others will have multiple sheets.    I need to ensure i am exclsively importing the ones with CPE in it.   I noticed the worksheet range with CPE can sometimes vary if other data sources are added. 

1st Excel

	FY24 Actual	FY24 Budget
Hotdog Revenues
Mustard	$ 22,185,572	$ 23,287,692
Ketchup	58,189,099	58,727,097
Cheese	(34,815,115)	(23,588,891)
Total French Fry Revenues	$ 45,559,556	$ 58,425,898
Non-Cardbord Adjustments
Non-Rents	$    (356,779)	(534,387)
Non-Grease	-	-
Pan Fees	(884,122)	(1,191,249)
Sandwitch Bag fees	(39,581)	-
Boxes	(851,142)	(1,279,014)
Total Workboots	(2,131,624)	(3,004,650)
Total Nikes	$ 43,427,932	$ 55,421,248
Total Trashbags	13,831,319	14,168,400
Corp	$            3.14	$            3.91

2nd Excel

	FY24 Actual	FY24 Budget
Hotdog Revenues
Mustard	$        44,444	$ 23,287,692
Ketchup	58,189,099	58,727,097
Cheese	(34,815,115)	(23,588,891)
Total French Fry Revenues	$ 23,418,428	$             444
Non-Cardbord Adjustments
Non-Rents	$    (356,779)	(534,387)
Non-Grease	-	-
Pan Fees	(884,122)	(1,191,249)
Sandwitch Bag fees	(39,581)	-
Boxes	(851,142)	4,444
Total Workboots	(2,131,624)	(1,721,192)
Total Nikes	$ 21,286,804	$ (1,720,748)
Total Trashbags	13,831,319	14,168,400
Corp	$            1.54	$          (0.12)

3rd Excel

	FY25 Actual	FY25 Budget
Hotdog Revenues
Mustard	$       567,898	$ 23,287,692
Ketchup	323,443	58,727,097
Cheese	(34,815,115)	(23,588,891)
Total French Fry Revenues	$ (33,923,774)	$             444
Non-Cardbord Adjustments
Non-Rents	$      (356,779)	(534,387)
Non-Grease	-	-
Pan Fees	(884,122)	(1,191,249)
Sandwitch Bag fees	(39,581)	-
Boxes	(851,142)	4,444
Total Workboots	(2,131,624)	(1,721,192)
Total Nikes	$ (36,055,398)	$ (1,720,748)
Total Trashbags	13,831,319	14,168,400
Corp	$            (2.61)	$          (0.12)

[Tutu_in_YYC · Accepted solution]

Use the Get data > Folder, and then just connect to one of sheet with CPE .You will get a mixed of data and errors in the end query, but we will fix it.

Now go to Transform Sample File query, and delete Navigation step.

then go Source and click the drop down option on the column Name.

Then choose Text Filter and Contains...

Configure : contains CPE, and hit OK

Then go to the column Data and expand.

Uncheck Use original.... and hit ok

Now go back to the main query.. you should see Name will contain different flavors of CPE.