Combine Different Excel Worksheets Tabs similar Names

Question

How to point power query to the worksheet names with CPE in it from the examples below?&nbsp; &nbsp;Some excels will have one worksheet and others will have multiple sheets.&nbsp; &nbsp; I need to ensure i am exclsively importing the ones with CPE in it.&nbsp; &nbsp;I noticed the worksheet range with CPE can sometimes vary if other data sources are added.&nbsp;1st Excel&nbsp;FY24 ActualFY24 BudgetHotdog Revenues&nbsp;&nbsp;Mustard&nbsp;$ 22,185,572&nbsp;$ 23,287,692Ketchup58,189,099&nbsp;&nbsp;&nbsp; 58,727,097Cheese&nbsp; (34,815,115)&nbsp; (23,588,891)Total French Fry Revenues&nbsp;$ 45,559,556&nbsp;$ 58,425,898Non-Cardbord Adjustments&nbsp;&nbsp;Non-Rents&nbsp;$&nbsp;&nbsp;&nbsp; (356,779)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (534,387)Non-Grease&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -Pan Fees&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (884,122)&nbsp;&nbsp;&nbsp; (1,191,249)Sandwitch Bag fees&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (39,581)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -Boxes&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (851,142)&nbsp;&nbsp;&nbsp; (1,279,014)Total Workboots&nbsp;&nbsp;&nbsp; (2,131,624)&nbsp;&nbsp;&nbsp; (3,004,650)Total Nikes&nbsp;&nbsp;$ 43,427,932&nbsp;$ 55,421,248Total Trashbags&nbsp;&nbsp;&nbsp;&nbsp; 13,831,31914,168,400Corp&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.14&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.91&nbsp;2nd Excel&nbsp;FY24 ActualFY24 BudgetHotdog Revenues&nbsp;&nbsp;Mustard&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 44,444&nbsp;$ 23,287,692Ketchup58,189,099&nbsp;&nbsp;&nbsp; 58,727,097Cheese&nbsp; (34,815,115)&nbsp; (23,588,891)Total French Fry Revenues&nbsp;$ 23,418,428&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 444Non-Cardbord Adjustments&nbsp;&nbsp;Non-Rents&nbsp;$&nbsp;&nbsp;&nbsp; (356,779)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (534,387)Non-Grease&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -Pan Fees&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (884,122)&nbsp;&nbsp;&nbsp; (1,191,249)Sandwitch Bag fees&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (39,581)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -Boxes&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (851,142)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,444Total Workboots&nbsp;&nbsp;&nbsp; (2,131,624)&nbsp;&nbsp;&nbsp; (1,721,192)Total Nikes&nbsp;&nbsp;$ 21,286,804&nbsp;$ (1,720,748)Total Trashbags&nbsp;&nbsp;&nbsp;&nbsp; 13,831,31914,168,400Corp&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.54&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (0.12)3rd Excel&nbsp;FY25 ActualFY25 BudgetHotdog Revenues&nbsp;&nbsp;Mustard&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 567,898&nbsp;$ 23,287,692Ketchup323,443&nbsp;&nbsp;&nbsp; 58,727,097Cheese&nbsp;&nbsp;&nbsp; (34,815,115)&nbsp; (23,588,891)Total French Fry Revenues&nbsp;$ (33,923,774)&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 444Non-Cardbord Adjustments&nbsp;&nbsp;Non-Rents&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (356,779)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (534,387)Non-Grease&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -Pan Fees&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (884,122)&nbsp;&nbsp;&nbsp; (1,191,249)Sandwitch Bag fees&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (39,581)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -Boxes&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (851,142)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,444Total Workboots&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (2,131,624)&nbsp;&nbsp;&nbsp; (1,721,192)Total Nikes&nbsp;&nbsp;$ (36,055,398)&nbsp;$ (1,720,748)Total Trashbags&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13,831,31914,168,400Corp&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (2.61)&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (0.12)&nbsp;

tutu_in_yyc · Accepted Answer

Use the Get data &gt; 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.&nbsp;Now go to Transform Sample File query,&nbsp;and delete Navigation step.&nbsp;&nbsp;&nbsp;then go&nbsp;Source and click the drop down option on the column&nbsp;Name.&nbsp;&nbsp;Then choose Text Filter and Contains...&nbsp;Configure : contains CPE, and hit OK&nbsp;Then go to the column Data and expand.&nbsp;Uncheck Use original.... and hit ok&nbsp;&nbsp;Now go back to the main query.. you should see&nbsp;Name will contain different flavors of CPE.&nbsp;&nbsp;

Forum Discussion

Combine Different Excel Worksheets Tabs similar Names

1 Reply

Related content

Need help combining two worksheets

Need help combining 4 worksheets into one

Tables combine

Combining Data From Multiple Worksheets Into one table

Lock worksheet

	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

	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)