I want to use Power Query to keep track of Inventory

I assume you have all files in a single folder?

1. Make sure your file that you are using PQ reside outside of the folder.

2. Then use PQ From File->From Folder. This should give you table with list of files in given folder.

3. Now go to Home tab in Edit mode. Find "Combine" and click on it.

4. This will give you list of sheets/tables in given file. Click on INV sheet and hit OK.

PQ will auto create custom functions and parameters needed to combine files and will generate combined table.

If you are not happy with the result, go to query named "Transform xxxx from..." and apply any necessary transformation.

You can then load the result to data model and report using PivotTable.

Edit: See link. I found more detailed tutorial.
https://www.myonlinetraininghub.com/...-from-a-folder

Sure, there are number of ways to do it.

If file keeps same structure across...
1. ADODB connection using VBA. Though this will be bit difficult to manage if you are not familiar with it.

2. Ron de Bruin's RDBMerge add-in. I have not used it myself, but I've heard good things from those that have.
https://www.rondebruin.nl/win/addins/rdbmerge.htm

3. MS Query - Similar to PowerQuery, but has outdated GUI and is bit of pain to edit.

If it does not have same structure... there's no easy way of merging workbooks. Good data structure is essential for data manipulation and analysis.

There are other options, but I primarily stick to ADO & PowerQuery myself.