Thank you in advance for both taking the time to look and, if you respond, having patience while I try to understand.
An important part of my role is controlling inventory so that we have enough to support service, but at the lowest investment possible. I need to transform how I'm tracking and analyzing. Currently, I take a weekly snapshot of the inventory that includes fields like planner code, MTO/MTS, inventory type, etc. It's all in one huge spreadsheet and I work off of pivots of that dataset. It works.
I'm being required to change my data source. The new source will have the inventory qty and cost, but not the additional fields I use to analyze. I think my original scheme, though fast and easy, wasn't the best as far as data efficiency - the planner for item X is stored 52 times per year, even though it never changes - so time for a change before it explodes anyway.
I'll have 3 different files as data sources - weekly inventory data (which is a ton of rows) and item attributes and inventory classifications, about 10k rows each.. They tie together easily by part number. Can you recommend a set up? Do I just put 3 pages in a workbook and use power pivot, or am I better to tie 3 workbooks together somehow so it's not all in one giant file? Is that power query?
I get a new inventory file each week. I just consolidated YTD out of a folder using power query (thank you youtube). I don't know if I should plan to cut and paste or use power query to append somehow when the new file is out each week? Or just connect to the folder?
I can probably figure out how to do things once I have a good direction.
An important part of my role is controlling inventory so that we have enough to support service, but at the lowest investment possible. I need to transform how I'm tracking and analyzing. Currently, I take a weekly snapshot of the inventory that includes fields like planner code, MTO/MTS, inventory type, etc. It's all in one huge spreadsheet and I work off of pivots of that dataset. It works.
I'm being required to change my data source. The new source will have the inventory qty and cost, but not the additional fields I use to analyze. I think my original scheme, though fast and easy, wasn't the best as far as data efficiency - the planner for item X is stored 52 times per year, even though it never changes - so time for a change before it explodes anyway.
I'll have 3 different files as data sources - weekly inventory data (which is a ton of rows) and item attributes and inventory classifications, about 10k rows each.. They tie together easily by part number. Can you recommend a set up? Do I just put 3 pages in a workbook and use power pivot, or am I better to tie 3 workbooks together somehow so it's not all in one giant file? Is that power query?
I get a new inventory file each week. I just consolidated YTD out of a folder using power query (thank you youtube). I don't know if I should plan to cut and paste or use power query to append somehow when the new file is out each week? Or just connect to the folder?
I can probably figure out how to do things once I have a good direction.
Last edited:
