Looking for best practices or advice setting up product stocking workbook

Hi everyone,

I'm looking for advice best practices to build pivot tables and charts for reports on an inventory tracking Workbook.  I built worksheets to track food, beverage, condiments, etc for 7 floors (red, green) and 6 (blue) departments we support.

The red and green worksheets have 40 columns of inventory items and the blue floors each have 20 columns of inventory items.  There is a separate worksheet that I built that updates the price per unit when I update the prices monthly. 

I built a master table using append but it requires each spreadsheet have 65 matching columns multiplied by 365 days each.  I'll clean up the empty rows but my master appended table created a clunky 9500 row table.  I can't link the price sheet automatically and can't create reports that include cost info. I can get it to update my master append table using refresh.  I've have built pivot tables and charts that also function.  I can't use the queries to build financial reports.

I also tried separating the worksheets into separate workbooks and used the data model but didn't succeed. I tried importing a file containing the worksheet without success.

Any advice from the community as to which features in Excel might help me create a more efficient way than having 65 column x 9500 row appended table please?  It shouldn't need to be 65 columns each worksheet.  Next year the table will double to a 17000 row table. Next, how does the data model or power queries ink the food items worksheet to my tables?  I'm so close but can't get it.

Thank you

TI