Combining excel files

For Excel, it's best to do a few things to make your life easier. One, use Format as Table so that you only get the table and not the whole sheet. https://support.office.com/en-us/article/create-and-format-tables-e81aa349-b006-4f8a-9806-5af9df0ac664 for the instructions. Secondly, tables should not have merged cells or intermediate headers. Thirdly, a column should have a consistent definition and contain one type of data appropriate for that definition. For example, if it says Ship Date, then hopefully it has actual dates, not "Next Week". 

Using Power Query in the Query Editor, you would create a connection to each table in the Excel workbook. If the tables are the same format and don't need to be pre-cleaned, you can append them to each other. After that, apply whatever transforms you need to the data, like adding custom columns to represent business rule conditions, etc.

Hope this helps!

--Treb

Check out my Power BI blog posts at https://marqueeinsights.com/category/power-bi/