I need to put the following example into a table that allows me to filter via term and metric. There are 12 terms each containing 1-5 weeks. This data, however, is on seperate sheets in seperate workbooks.
@cheeseontoast101 You're not very specific, but it seems that PowerQuery, indeed, is the tool you need. Connect to all files in a folder and get everything in one table. Then flatten it (i.e. unpivot) and do further analysis from there.
Exactly how is difficult to tell without more information.