12 year analyst feeling like a dinosaur. Need advice on moving away from massive flat files without forcing Power BI on my team.

I’ve been a Reddit user for a long time, but I’ve recently hunted and scoured the internet for communities to help me with this problem and I heard that this was possibly the place to be.

I work as an Analyst in the CPG space (NWA area) and have been doing this for over a decade now. I know my way around a spreadsheet pretty well, but I feel like my technical skills kinda froze in time around 2020. Before COVID hit, I was finally learning proper Data Modeling and Power Pivot from a mentor, but then we got sent home, things changed and I’ve basically been surviving on VLOOKUPs and brute force ever since.

I actually inherited some pretty advanced VBA tools from that mentor back in the day, but they were built for the old legacy system (DSS/Retail Link). When the retailer migrated everything over to the new platforms (Luminate/Scintilla and Madrid), all that old automation effectively died. The new export formats and cell limits broke the old code, so I never really rebuilt them and I'm back to manual stitching.

I realized about 6 months ago that I am falling behind. I’m still building massive flat-file reports the hard way and it’s killing me.

The situation is basically this: I pull data from a vendor portal (think Unify style system) that has a hard export limit of like 3M cells. If I want to pull 52 weeks of history for 500+ items across hundreds of locations, I get the "cell count exceeds limit" error. So I have to pull it in chunks—Dollars, Units, PODs separately—and stitch them together manually. It is a massive pain.

To make it worse, my stakeholders are super old school. They want Excel files they can touch, pivot, and scribble on. If I send them a Power BI link, they won't even open it. So I need the flexibility of Excel, but the data volume is getting too big for the standard sheets I'm building.

My goal is to build a "set it and forget it" system. I want to be able to just drop those raw data exports into a folder and have Excel just "eat" them. I know Power Query is probably the answer for the stitching, but is Power Pivot still the best way to handle the data model part? I need it to handle the heavy lifting (52 weeks, store level data) but output into standard Pivot Tables that my buyers can still play with.

Also, side note on AI—everyone tells me to just use ChatGPT to write python scripts, but that’s not really what I need. I’m trying to get my data structure clean enough that I can feed the final Pivot Tables into an LLM to help me write the recaps/insights. Has anyone had luck with that workflow?

Any advice on where to start or a specific course to bridge the gap between "VLOOKUP guy" and "Data Model guy" would be awesome. Thanks.