Brainstorming a new table layout

Hey everyone.

I have a predicament at work and I can't figure it out. Reaching out to the internet for assistance.

I have a giant table for pricing that at a high level is rows of products (numbering around 500) and columns (around 300) representing cost, product sizes, ID's, and discounts. Currently these are broken out by a national section and 6 division (state groupings) sections that have their own discount columns. It's cumbersome due to its size but the person charged with entering the data likes it, so it has stayed this way.

However, the business has outgrown it and now wants to get more granular with the data. They need discounts at the state and city level. Which I could just expand our current table but I estimate that would create a file with around 2,000+ columns, and around 95% of those would be blank.

The reason it gets so long is that each discount has to have its own column and that discount could have all/none of the products. So every level of granularity just compounds this issue.

I'm here looking for better ways to handle this data.

Right now the best I've come up with is that I create a 2nd workbook that just handles the state/city level, it would still be awkward and add a lot of duplicate work though (maintaining product names/ids/etc)

Currently I run the workbook through a power query which condenses it all and spits out individual sales books based on region data. I would plan on combining these two books into one dataset in the future. And that's an issue for another time.

I'm decent with excel/power query. I'm the company "excel guy" but I know enough to know that I don't know much.