Combine multiple tables on same sheet from Excel efficiently

[lbudack]

My current model works, but I'm sure there's a better, more efficient way to do this that I'm missing. Every time I have to add a staff member, I have to adjust some of the steps in my model to get it to see all the rows and then republish. Ideally, I'd like to avoid reformatting the spreadsheet as our receptionist tracks attendance this way so I don't want her to have to scroll down one long list of names when the current layout is much easier to deal with. The Excel file lives in SharePoint. 

Layout of spreadsheet: 

Snapshot of query (created mostly by an append query that I did): 

Suggestions welcome! Thanks! 

[v-easonf-msft]

Hi , @lbudack

Could you please tell me whether your problem has been solved?
If it is,  please mark the helpful replies or add your reply as Answered to close this thread.
More people will learn new things here. If you haven't, please feel free to ask.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-easonf-msft]

Hi ,  @lbudack 

I ‘m still confused about your issue .Can you explain it  in more detail?

When you say "adjust some of the steps in my mode", what do these steps refer to ?

And  which one you choose to combine files , table1 or sheet1?

Best Regards,
Community Support Team _ Eason

[harshnathani]

HI @lbudack ,

https://radacad.com/get-data-from-multiple-excel-files-with-different-sheet-names-into-power-bi

https://radacad.com/combine-multiple-or-all-sheets-from-an-excel-file-into-a-power-bi-solution-using...

Please see if these help.

Regards,

Harsh Nathani

[lbudack]

Thanks, but that's not really my issue. My issue is combining tables that are on one sheet instead of multiple. 

[mahoneypat]

When you say "tables", do you mean they are formatted as tables?  If so, those will show up in query as distinct entities (Kind = Table instead of Kind = Sheet).  If not, you should do that and use the Tables to extract the data instead of sheets.  If you are already doing that, please clarify your issue (pics aren't obvious to me at least).

Regards,

Pat