Multiple Excel files, with multiple tabs

[G_Whit-UK]

Hi

I have a daily system generated Excel file which is saved in a single folder.  Each file has multiple tabs (Data, Data1, Data2, etc) as the system caps the data reflected on each tab to 65,535 rows.  Each tab has the same colum headers.  A sample of the data is as follows:

Aggregate Ind	Cpty LE Name	X ID	Internal Ref ID	Security ID	Security ID Type	My Qty	Cpty Qty	Number of Breaks	Break Type	Age
No	A	721149852	123456789	B1G6TY5	S	2,000	2,000	2	Market Value	5
No	A	721149852	123456789	B1G6TY5	S	2,000	2,000	2	Trade Execution Timestamp	16
No	B	721306673	321654987	B06H8J9	S	26,500	26,500	1	Settlement Date	30

Each file also contrains an additional "Summary" tab - this tab needs to be excluded from the reported data.

I know how to link Power BI to a folder so that it picks up multiple Excel files, but only using a single tab on each file.  I also know how to link a single Excel file source and use multiple tabs, but how do I go about setting up using multiple Excel files with multiple tabs so that the data is reflected in a single Power BI data table?

Thanks

[v-eachen-msft]

Hi @G_Whit-UK ,

You could refer to the resolved case which is similar to yours:

https://community.powerbi.com/t5/Desktop/Power-Query-Combine-Excel-data-files-with-different-tab-nam...

[mahoneypat]

Hard to provide M code with an example to work on.  You've combined one tab from multiple workbooks, which auto generates a function.  You can modify that function to start from the worksheet level (instead of the workbook level).  You then modify your query to expand all the excel files, filter for all the Kind = Sheet and filter out any that contain "Summary", then invoke your worksheet function.

If that isn't enough help, you can provide two mock Excel sheets with multiple tabs, and I will send back some M code to demonstrate.

This video also demonstrates how to do this - https://www.youtube.com/watch?v=tYqFIVH-SmY

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[OKgo]

Many ways to do this. I would aggregate the tables within Excel so each file has one powerquery output answer. 

Data > Get Data > From other sources > Table / Range
(For sure make sure things are real tables)

You simply don't do that step for the summary ones.

You can then within PowerQuery append all theses tables easily with the user interface

Then in your PBIX file you only need to make one connection per file - the answer table you created from appending. Hopefully that is a managble!. There are many other options.